skreebydba

My company is starting to look at upgrading to SQL Server 2012, so I've installed it on a server here and am working to get an application set up. I ran the Upgrade Advisor against the existing server, which is running SQL Server 2005. It errored on a number of databases because they were set to compatibility_level 80 and it can only processes 90 or higher. I put together a quick dynamic script to update the compatibility mode to 90 for anything set below that. In the spirit of Ted Krueger's (b|t) post on throw-away SQL from last year, here's the code:

USE master
GO

-- Shut off 'row(s) affected' messages in your output making it easier to copy and paste dynamic SQL
SET NOCOUNT ON

-- Create a temp table to hold the list of database names
CREATE TABLE #dbcomp
(dbname SYSNAME)

-- Declare your local variables
DECLARE @dbcount TINYINT
DECLARE @loopcount TINYINT
DECLARE @sqlstr VARCHAR(2000)
DECLARE @dbname SYSNAME 

-- Get a list of database names that are online and in a compatibility mode less than 2005.  This can be adjusted to look for other compatibility_level values.
INSERT INTO #dbcomp
SELECT name FROM sys.databases
WHERE compatibility_level < 90
AND state_desc = 'ONLINE'

-- Get a count of those database names for a loop
SET @dbcount = @@ROWCOUNT
-- Initialize your loop counter
SET @loopcount = 1

-- While the loop counter is less than the count of databases, execute your loop
WHILE @loopcount <= @dbcount
BEGIN
	
	-- Get the top database name from the temp table
	SET @dbname = (SELECT TOP (1) dbname FROM #dbcomp)
	-- Build your ALTER DATABASE statement to update the compatibility_level
	SET @sqlstr = 'ALTER DATABASE ' + @dbname + ' SET COMPATIBILITY_LEVEL = 90;'
	/*PRINT @sqlstr - Uncomment if you want to print the SQL statement for future use*/
	-- Execute the ALTER DATABASE statement for each database
	EXEC(@sqlstr)
	-- Delete the TOP row from the temp table
	DELETE TOP (1) FROM #dbcomp
	-- Increment the loop counter
	SET @loopcount = @loopcount + 1

END

-- Clean up the temp table when done
DROP TABLE #dbcomp