1

I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012. So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3).

Executed query on New database:

SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyNewDatabase';

Result:

compatibility_level
-------------------
90

But System db's are showing Compatibility_Level as '110' of MSSQL-Server-2012.

Let me conclude my problems with below questions:

  • If Compatibility_level = 90 is for SQL Server 2005, then has the database really been upgraded to SQL Server 2012 or not?

  • Why does the restored database show Compatibility_Level as '90' and not '110'?

  • Also, if it is only contains features of SQL Server 2005, then what is the better way to upgrade it to SQL Server 2012?

Is the upgrade as simple as "ALTER DB"? Like this:

ALTER DATABASE MyNewDatabase
SET COMPATIBILITY_LEVEL = '110';
  • 1
    Also check my answer here for some distinctions between features and areas that may or may not be affected by the compatibility level vs instance version. – LowlyDBA Dec 11 '18 at 20:48
2

Maybe Compatibility_level = 90 is of MSSQL-2005, then is it really been upgraded to MSSQL-Server-2012 or not? how may I know?

If it's running on the 2012 instance, then that database's metadata has been upgraded. So you're good.

Could anyone please explain me why restored database also showing Compatibility_Level as '90' and not '110'?

When you restore a database onto a newer version (called an upgrade), it's compatibility level is set to the lowest supported version for that instance. In this case, 90 is still functional on the 2012 instance, so it was left as-is.

Also, If it is still contains featured of MSSQL-Server-2005 only, then what is the better way to upgrade it to MSSQL-Server-2012?

Upgrade is as much simple as "ALTER DB"?? like

Yes, it really is just as easy as ALTER DATABASE ... SET COMPATIBILITY_LEVEL.

You can remain at the lower compatibility level as long as you like. However, to use newer features introduced since version 90, you'd need to update to the later compatibility levels. You can see a list of some of the changes here:

Differences Between Lower Compatibility Levels and Levels 110 and 120

Also note that, starting with SQL Server 2014 (level 120), the 90 compatibility level is not available. So the next time you upgrade, your database will automatically be updated to compatibility level 100. You can see a table of supported levels on each engine version here:

ALTER DATABASE (Transact-SQL) Compatibility Level - Arguments

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.