CODEWITHSUNDEEP
sqlsql-servert-sqlcompatibility-level
Yossi M
Yossi M

Reputation: 71

SQL Server : database compatibility_level after upgrade

We are currently upgrading few SQL Server instances from SQL Server 2008 R2 to SQL Server 2012.

This is a temporary solution (because Microsoft EOL support for 2008 R2 is coming soon).

I noticed that after the upgrade all system database EXCEPT master are set for the new compatibility_level (110).

Does somebody know why [master] is not getting the new compatibility_level as part of the upgrade?

I know I can set it manually, but wondering why it is not set be default as the other system database.

Thanks,

enter image description here

Upvotes: 2

Views: 1884

Answers (1)

Alexander Volok
Alexander Volok

Reputation: 5940

From the documentation of SQL Server 2017, however the same behavior can be observed also in SQL Server 2012: ALTER DATABASE (Transact-SQL) Compatibility Level

The below behaviors are expected for SQL Server 2017 (14.x) when a database is attached or restored, and after an in-place upgrade:

  • If the compatibility level of a user database was 100 or higher before the upgrade, it remains the same after upgrade.
  • If the compatibility level of a user database was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2017 (14.x).
  • The compatibility levels of the tempdb, model, msdb and Resource databases are set to the current compatibility level after upgrade.
  • The master system database retains the compatibility level it had before upgrade.

So, this is behavior by design.

Upvotes: 1

Related Questions