I recently migrated several databases running on SQL Server 2005 Standard Edition to SQL Server 2012 Enterprise edition. Due to the fact that we had numerous databases running on a single instance, each with its own compatibility concerns I needed an easier way to keep track of the current compatibility level of each database as we migrated, as opposed to using SQL Management Studio’s GUI to dig into each individual database and manually record the settings.
Fortunately SQL Server provides a simple way to get to this information using the
sys.databases Dynamic Management View (DMV).
SELECT name AS 'Name',
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
WHEN 130 THEN 'SQL Server 2016'
END AS 'Compatibility Level',
recovery_model_desc AS 'Recovery Model',
I’ve included a CASE statement here to return the actual version of SQL Server in the Compatibility Level column. An example of the output you should expect to see from running this script can be shown below. As you can see, each database is currently in SQL Server 2012 compatibility mode.
Though not necessary, its good to be familiar with the compatibility values that Microsoft has set for each version, as often this is what you will see when dealing with this in a real world setting.
80 – SQL Server 2000
90 – SQL Server 2005
100 – SQL Server 2008
110 – SQL Server 2012
120 – SQL Server 2014
130 – SQL Server 2016
Additionally, you can change the compatibility level of a database using the following script:
ALTER DATABASE DYNAMICS
SET COMPATIBILITY_LEVEL = 110;
In this example, as I work with Dynamics GP quite a bit, I am altering the compatibility level of the DYNAMICS database and setting it to 110 (SQL Server 2012).