Compatibility level of Database Properties of sqlserver 2008

When I restore the databases from sqlserver 2005 to 2008, I see the database properties (options) are defaulting to SQL Server 2000(80). Do I need to change the Compatibility level to SQL Server 2008 (100) for all databases.
more ▼

asked Jun 01 '12 at 02:11 PM in Default

Chitrarekha gravatar image

140 30 39 43

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

3 answers: sort voted first

Yes, you will have to make this change yourself. If this is a thirdparty vendor database you might want to double check with them as well to ensure they support the upgraded compatibility level. I have only had two vendors out of several hundred where this was an issue.

more ▼

answered Jun 01 '12 at 02:15 PM

Tim gravatar image

35.5k 32 40 138

+1 for the 3rd party consideration
Jun 01 '12 at 02:19 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

If the database was already at level 80 when in 2005, then yes you will need to change the level to 100.

There are T-SQL differences between 2000 and 2008, but I'm sure you've already contemplated those if you are upgrading your server?

I'd also check the page verification option as this was defaulted to TORN_PAGE_DETECTION in 2000, whereas 2005 and above it is CHECKSUM - it is worth changing this too!
more ▼

answered Jun 01 '12 at 02:18 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

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

You might also want to run [DBCC CHECKDB WITH DATA_PURITY][1] if it was at 80. It's best to do a test migration and work out the kinks/see what pitfalls may be in store.

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

answered Jun 01 '12 at 02:48 PM

Blackhawk-17 gravatar image

11.8k 28 30 35

(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



Answers and Comments

SQL Server Central

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



asked: Jun 01 '12 at 02:11 PM

Seen: 818 times

Last Updated: Jun 01 '12 at 02:50 PM