0

I've an SQL Server 2005 SP2 instance that contain old databases that are not compatible with other SP of SQL Server 2005. Those databases are on physical server that don't have any space to backup them so i planned to put them on a new virtual machine on a SQL Server 2012 instance with compatibility level at 90.

But i want to know if the compatibility level of the 2012 instance is equivalent of an SQL Server 2005 SP4 because if is this i can't put the database on it.

|improve this question
  • Define "equivalent". The 2012 engine still behaves differently than that of 2005, compatibility level or not. Compat level more or less only means that you can be (reasonably) sure that all statements that worked on the database before will continue to work (as in, old syntax remains supported, new syntax/statements won't work to ensure they don't clash with existing identifiers, etc.) You are not guaranteed 100% bug-for-bug compatibility, let alone equal performance, so testing remains necessary. – Jeroen Mostert Sep 28 '18 at 12:02
  • For equivalent i mean, if my old database is not compatible with the SP4 of SQL 2005, will it be compatible with the compatibility level 90 on a SQL Server 2012 instance. – Guidjome Oct 1 '18 at 12:26
0

The Compatibility Level never changes due to a Service Pack upgrade. So SP4 has the same Compatibility Level as SP2, same features and limitations etc, it's just presumably better/faster/more stable/more secure.

SQL2005 databases have Level 90 (unless they were restored from even older versions without updating the Level) and can be restored on SQL 2005 through 2012.

For the complete table of SQL Server versions vs supported Compatibility Levels, see: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level

|improve this answer
  • thanks for the link but i already know it. i'll test the restore of the database on sql 2012 instance because i don't find any answer for my question. – Guidjome Oct 1 '18 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.