I discovered a SQL server (2005) which has MASTER, MODEL and MSDB in SQL2000 compatibility mode. I don´t know why (every user database is in SQL 2005 mode) it's probably done accidentially, so I want to revert this issue.
Question: Is it just switching the compatibility mode on database level? Do I have to restart SQL? Does somebody have experience with this issue?
asked Nov 07, 2011 at 03:20 AM in Default
Wilfred van Dijk
If you make an inplace upgrade of SQL Server, at least your master database will remain on the former compatibility level (80 for SQL Server 2000, 90 for SQL Server 2005 etc).
You should set the database to single user mode before switching to another compatibility level, and then set it back to multi user mode.
I would strongly recommend performing this change in a test environment before changing in production. If it will have an impact on your apps or not will depend on if you have created your own objects in the system databases, and if they use deprecated functionality.
Have a look at Books Online about ALTER DATABASE Compatibility Level: http://msdn.microsoft.com/en-us/library/bb510680.aspx
answered Nov 07, 2011 at 03:57 AM