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.
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
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
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?
@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.