Database Compatibility Levels : How to change all at Once

On February 14, 2008, in SQL, by Anuj Gakhar

Before I begin, this post is specific to Microsoft Sql Server, for those who care.

A little background :-

If you migrate your databases from Sql Server 7.0 to Sql Server 2005, you are going to have to deal with this at some point, I think. So, once you you have your databases migrated into Sql 2005, you will have to start worrying about backups as well and having nightly backups is a sensible thing to do. Unless you write a custom T-SQL script to do this, chances are that you will end up using the inbuilt Maintenance Plans that come with Sql Server. For people like me, who are not DBA’s but sometimes have to wear different hats to do different kinds of stuff, this is all a little known domain.

However, a maintenance plan is a pretty straightforward wizard, you can choose the databases you want to add to the plan and define certain tasks (like backup, cleanup, integrity check etc) and save and you can have it email a report to yourself every time it runs. Thats all cool, but what is less obvious (or atleast to me it was) is the fact that it wont let you include any databases that have a compatibility level of 70. Now, when you migrate your database from 7.0 to 2005, the default compatibility will be 70 and therefore, none of those databases will show up in the maintenance plan for backup. For those who are not aware, SQL Server has the following compatibility levels.

Sql Server 6.0 (60)
Sql Server 6.5 (65)
Sql Server 7.0 (70)
Sql Server 2000 (80)
Sql Server 2005 (90)

So, you need to change the compatibility level to atleast 80 for Sql 2005 to pickup your databases to include for backup plans. This can be done by right clicking on the database in Management Studio, go to Options and change the compatibility level there. But what if you have like 80 different databases, how would you do it all at once? Well, thats where this script comes handy.

[sql]

DECLARE GET_DATABASES CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != ’90’

DECLARE @DATABASENAME NVARCHAR(255)
DECLARE @COUNTER INT

SET @COUNTER = 1

OPEN GET_DATABASES
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
— CHANGE DATABASE COMPATIBILITY
EXECUTE sp_dbcmptlevel @DATABASENAME , ’90’

PRINT @DATABASENAME + ‘ changed’

SET @COUNTER = @COUNTER + 1
END

FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
END

CLOSE GET_DATABASES
DEALLOCATE GET_DATABASES

GO
[/sql]

So that script simply gets all databases that dont have a compatibility level of 90 and then changes the level to 90. I am sure there could be some error handling code written in that, but it kinda does the job as it is.

Tagged with:  

7 Responses to Database Compatibility Levels : How to change all at Once

  1. RandyB says:

    Thank you! I needed a quick command to change compatability level and you had it right there for me.
    Appreciate the help.
    -Randy

  2. Anuj Gakhar says:

    @Randy, Glad I could help. Cheers 🙂

  3. Kranp says:

    Thank You! It works Great!

  4. Robert says:

    Worked like a charm. Thanks a bunch!

  5. Jaime says:

    The script was fine when you have the same compatibility for that instance, how can it be impliment to change it to the default compatibility. For example I have several different servers that have different SQL version (2000,2005, and 2008) I would like to run a the script that will update the compatibility on each server based on the default compatibility level.

  6. don says:

    I have changed the compatability level of sql 2005 to sql 2000

    ALTER DATABASE TestDB SET SINGLE_USER;
    EXEC sp_dbcmptlevel TestDB, 80;
    ALTER DATABASE TestDB SET MULTI_USER;

    Now when Iam trying to add “xml ” datatype it is getting added for a column which should not right?

    please specify what is the problem

  7. Michael says:

    Not using a CURSOR and easy update to change ‘compatability level’.

    SET NOCOUNT ON
    USE master
    GO

    –Clean up
    IF object_id(‘tempdb..#DatabaseNames’) IS NOT NULL
    DROP TABLE #DatabaseNames

    — Variables to be used in the script
    DECLARE @dbName varchar(100), — the name of the database
    @NextRowID int

    — Get all user databases name
    SELECT RowID=IDENTITY(int, 1, 1),name
    INTO #DatabaseNames
    FROM master.dbo.sysdatabases
    WHERE name NOT IN ( ‘master’,’model’,’msdb’,’tempdb’)
    ORDER BY name DESC

    — MAX RowID
    SELECT @NextRowID = (SELECT MAX(RowID) FROM #DatabaseNames)
    WHILE ISNULL(@NextRowID,0) 0
    BEGIN
    — First Row
    SELECT @dbName = name FROM #DatabaseNames WHERE RowID = @NextRowID
    — Execute SQL statement
    EXECUTE (‘ALTER DATABASE ‘ + @dbName + ‘ SET RECOVERY SIMPLE WITH NO_WAIT ‘)
    PRINT ‘RowID:’ + CAST(@NextRowID AS varchar(4)) + CHAR(9) + ‘ALTER DATABASE ‘ + @dbName + CHAR(9)+ ‘ SET RECOVERY SIMPLE WITH NO_WAIT ‘
    –Next Row
    SELECT @NextRowID = @NextRowID – 1
    END

    DROP TABLE #DatabaseNames

Leave a Reply to Anuj Gakhar Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar
%d bloggers like this: