Six months ago I wrote article about SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility. Yesterday I received an email asking that one of my blog reader is not able to use the sp_dbcmptlevel command with error that database is in use. He has asked me to write about proper procedure of changing database compatibility which will always work. The safe way to change compatibility level takes three steps: single user mode, the change itself, then multi user mode again.
First read my previous article SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility as it has explained many details about compatibility.
The best practice to change the compatibility level of database is in following three steps.
- Set the database to single user access mode by using ALTER DATABASE SET SINGLE_USER.
- Change the compatibility level of the database.
- Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.
Above three steps are translated in T-SQL in following three line script. Run this procedure in Query Editor.
ALTER DATABASE AdventureWorks
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER
GO
What to Know After You Change Compatibility Level
The compatibility level controls how some T-SQL behaves in that database. It does not turn the server into an older version. The database still runs on the engine you installed, so a SQL Server 2005 instance with a database at level 80 still uses the 2005 engine.
A few tips:
- Check the current value first with
SELECT name, compatibility_level FROM sys.databases. - Add
WITH ROLLBACK IMMEDIATEto the SINGLE_USER step so open sessions do not block it. Warn the users first, because their open transactions roll back. - From SQL Server 2008 onward, use
ALTER DATABASE ... SET COMPATIBILITY_LEVEL = 100instead. sp_dbcmptlevel is deprecated there. - Test the application after the switch. A query that worked at one level can behave differently at another.
Keep a note of the old level before you start. If something breaks, going back is one command away, as long as you know where you came from. Write it in your change log too, so the next person knows what was done and why.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
15 Comments. Leave new
thanks
Hello.
On a 2005 sql restore basic sql 2000 that were compatible with 80, my question is that considerations or soft I can run to verify incompatibilities, and then switch compatibility 90 smoothly.
Thank you
bravo Dave!!
Hi.
I want to wnow that now can i set default user when i create a new database.
Ajay
Hi.
I want to know that now can i set default user when i create a new database.
Ajay
Will there be any problems changing the compatibility level from 2000 to 2005?
Search for Behaviour Changes from One version to Another version in Google
@Paul.
There is free in build tool Upgrade Advisor that comes with SQL Server, it could be downloaded as well from web.
I suggest you run Upgrade Advisor on your database, it will list all issues that you could face after upgrade (changing compatibility from 2000 to 2005).
I suggest not to do this on production databases, run this test, in your Test/Dev Env.
~ IM.
Dear sir
i have problem with this crosstab query .
SELECT studentid,tablenumer,roomid,[SUB001] AS [ភាសារបរទេស(អគ្លេស)],[SUB002] AS វប្បធម៌ទូទៅ,[SUB003] AS ភាសាបាលី
FROM(SELECT studentid,tablenumer,roomid,subjectid,score FROM tbl_studentexamein ) ps
PIVOT(SUM(score)FOR subjectid IN([ភាសារបរទេស(អគ្លេស)],[វប្បធម៌ទូទៅ],[ភាសាបាលី])) as pvt
_but it does not acept the selection of subjectid= SUB001,SUB002,SUB003 ON First select statment
please help me to check this code how to run it with sqlserver 2005?
What was the error you got?
Refer this post and apply the logic
In SQL 2008 we can do it using Alter command
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
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
Hi,
i have a production server with sql server express 2005 and need to move database on sql server 2008 workgroup edition.
1.) just want to check can we move sql server express 2005 database to sql server 2008 workgroup edition server?
2.) will there be any impact on the database due to same?
Thanks & Rgds
Deepak Bansal
[email address removed]
If I have no. of stored procedures in my database which are joined with an old style (*=, =*) for right or left joins, how can i convert them automatically instead of converting the each sp code manually.
Is there anyway to do that ?
Some databases on SQL 2008 R2 are in 90 compatibility mode, before I change it to 100, I would like to know what will break, so I ran Upgrade advisor for SQL 2008 R2 but it is giving bunch of errors like below:
Method not found: ”System.Collections.Generic.IEnumerable`1 Microsoft.SqlServer.Management.SqlParser.SqlCodeDom.SqlCodeObject.get_Errors()”.