Master in lower compatibility mode - issues when fixing?


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?

more ▼

asked Nov 07, 2011 at 03:20 AM in Default

avatar image

Wilfred van Dijk
3.6k 29 39 49

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Nov 07, 2011 at 03:57 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 07, 2011 at 03:20 AM

Seen: 1426 times

Last Updated: Nov 07, 2011 at 03:20 AM

Copyright 2018 Redgate Software. Privacy Policy