December 17, 2013 at 3:23 am
I've inherited a database that according to the 3rd party supplier has been restored to the incorrect compatibility after a server migration level a few months ago. This was only noticed after throwing up ODBC errors for certain end users. The 3rd party has asked us to change the database from 2008 to 2005
The instance: SQL Server 2012
Current database compatibility: 2008
Destined compatibility: 2005
Are there any hidden gotchas from moving from 2008 back to 2005 that I need to be aware of.
Is it simply a matter of using Management Studio to change the compatibility option. (Obviously ensuring backups are made prior to the change?)
December 17, 2013 at 4:34 am
You can change compatibility level up and down as much as you like. It's just a switch controlling how the query execution engine interprets some T-SQL constructs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2013 at 5:09 am
I'm guessing a fairly dangerous switch depending upon how the application is written.
http://technet.microsoft.com/en-us/library/bb510680.aspx
The BOL article above is a quite informative on differences between the levels and also best practices on how to carry out the changes. Including the following;
"Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. For example, if the compatibility level changes while a query plan is being compiled, the compiled plan might be based on both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results."
Apologies, being lazy (saving myself time :Whistling: ) here but how does the restore work when restoring between different versions of SQL Server.
If I create a new database with the same db name on say a 2012 instance will a restore from a 2005 db backup automatically change the compatibility level to 2012 or do I have to explicitly change the level to 2005 after the restore?
Does this depend upon how I do the restore. i.e. Restore and create the new database during restoration / Create the database and then do the restore separately
December 17, 2013 at 5:19 am
The restore won't change the compatibility level, you have to do that as a second step.
Even if you create the database in 2012, when you restore the 2005 backup, it's a 2005 database, so the compatibility level will be changed. You can't get around that one.
But, the restore process does upgrade what is being restored, so you have a 2012 database, it's just running under compatibility mode with all the restrictions that Gail outlined.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 17, 2013 at 7:50 am
thunderousity (12/17/2013)
Does this depend upon how I do the restore. i.e. Restore and create the new database during restoration / Create the database and then do the restore separately
The latter is a waste of time. If you create a database then restore over it, the restore overwrites the existing database. Just restore the backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply