Upgrading Database Compatibility Level When Database is already on 2008 SQL Server

Question

Post reply

Upgrading Database Compatibility Level When Database is already on 2008 SQL Server

d33m

SSChasing Mays

Points: 620
More actions
June 22, 2010 at 8:31 am

#224122

Is there a tool that you can use to tell what items may need to be fixed when upgrading to 2008? I can't use SQL Server Upgrade Adviser because the Database is already on SQL Server 2008, but the compatibility level is still at 80. We would like to change it to 100. I currently do not have a way of installing SQL 2000 or 2005 and backup/restore to another server. Any suggestions?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Why do you want to change the compatibility level for all dbs at once?

Here's a quick script to identify them :

SELECT name, database_id, compatibility_level FROM sys.databases where compatibility_level <> 100

d33m SSChasing Mays Points: 620 More actions · Answer 2

There is only one database on the server, but I would like to upgrade the compatibility level of the database to 100 because I would like to take full advantage of SQL Server 2008.

WayneS SSC Guru Points: 95460 More actions · Answer 3

I'd be interested in this also. I have a database that was kept in sql 2000 compatability mode, but I'd like to find out what needs to be modified so that it can move up to 2008 compatibility mode. As you mentioned, the upgrade advisor doesn't work on these since the server is at 2008.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Only 1 solution here ... test it out.

Depending on the importance of the applications that are using the databases :

1 - If not critical, take backup in 80 or 90 comp mode then do the upgrade in place. Then retest all applications... then you also have users who can report bugs.

2 - If critical or not sure, make a backup, restore on different name, change comp level and run full batery of tests for all applications involved.

One thing to keep in mind is that many new features are sql engine dependant so if the db is installed on the engine then you have access to it (like online index jobs, or merge statement...). So you might check this path out first. Then if you hit the wall, consider the upgrade.

Remeber what Joel on Software said... code doesn't rust, it doesn't decay. Don't fix something that's working. It'll take years to get back where you are now just to say you have new code (because that code has been in testing and live environement for years). It's not worth it 99.99% of the time (God knows I've tried).