SQL Server : database compatibility_level after upgrade

Question

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,

Answer 1

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.