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';
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 =
90is 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 '
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';