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.
Thank you! I needed a quick command to change compatability level and you had it right there for me.
Appreciate the help.
-Randy
@Randy, Glad I could help. Cheers 🙂
Thank You! It works Great!
Worked like a charm. Thanks a bunch!
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.
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
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