Change Database Compatibility Level

Recently I came across a database environment that had SQL Server 2005 (don't ask me why), but the databases (there were about 50 databases) were running at SQL Server 2000 compatibility level. After ensuring that nothing will break if I changed a database's compatibility level to 2005, I decided to go ahead and run a script to change all databases' compatibility level to 2005.

Below is a script that you can run in your environment.

DECLARE  @tmpTable TABLE (Name varchar(50),compLevel INT,StateDesc varchar(50))

INSERT INTO @tmpTable(Name,CompLevel,StateDesc)
SELECT Name,Compatibility_level,State_Desc FROM sys.databases 

DECLARE @DBName varchar(50)
DECLARE curDBName CURSOR FOR
    SELECT Name FROM @tmpTable WHERE StateDesc='ONLINE'
OPEN curDBName
FETCH NEXT FROM curDBName INTO @DBName
WHILE @@FETCH_STATUS=0
    BEGIN
        DECLARE @compLevel INT
        SELECT @compLevel=compLevel FROM @tmpTable WHERE Name=@DBName
        If @compLevel=80
            BEGIN
            Exec sp_dbcmptlevel @DBName,90
            END
        FETCH NEXT FROM curDBName INTO @DBName
    END
CLOSE curDBName
DEALLOCATE curDBName

You can change the version to move from 2000 to 2008 or 2005 to 2008, but before you do that, make sure that your database will not break. One way to check would be to create database scripts by logging into Management Studio > Right Click on Database > Tasks > Generate Scripts. Make sure you select all objects i.e. tables, views, stored procedures, functions etc. and then save scripts. Run these scripts to create an empty database in the SQL environment you are upgrading your database to. If there are no errors, there is a good chance that your production database will work fine.

Thank you.