x

Master in lower compatibility mode - issues when fixing?

Hi,

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

Wilfred van Dijk gravatar image

Wilfred van Dijk
1k 18 22 28

(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][1]

[1]: http://msdn.microsoft.com/en-us/library/bb510680.aspx
more ▼

answered Nov 07, 2011 at 03:57 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x12
x2

asked: Nov 07, 2011 at 03:20 AM

Seen: 1083 times

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