Switching Compatibility Levels on Multiple Databases

Recently as part of an upgrade to SQL Server 2019, I needed to set the compatibility levels for a database to 150 (for 2019). You could certainly do this by clicking through the configuration dialogs for each database as shown here, but for a server with many databases that could take some time.

Here are some of the compatibility levels for SQL Server:

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0
• 80 = SQL Server 2000
• 90 = SQL Server 2005
• 100 = SQL Server 2008 and SQL Server 2008 R2
• 110 = SQL Server 2012
• 120 = SQL Server 2014
• 130 = SQL Server 2016
• 140 = SQL Server 2017
• 150 = SQL Server 2019

Here is the syntax to make the change.

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

Instead of clicking through all the dialogs, I decided to create a script to find all the databases that are at a lower level than SQL Server 2019 (Level 150) and to set the compatibility level to 150. The SQL Scripts to do that looks like this.

SELECT [name],
'ALTER DATABASE [' + [name] + '] SET COMPATIBILITY_LEVEL = 150;' as setCompatibilityLevel
FROM sys.databases
-- only those that are less than a 150 compat level
WHERE compatibility_level < 150 
-- don't do the master, model, tempdb, msdb 
AND database_id > 4;

When you run that query it generates the code to copy and paste to run to set the compatibility levels.

Just copy the second column and paste it into SSMS and run it to update the compatibility level. It will look something like this.

ALTER DATABASE [DBHealthHistoryOriginal] SET COMPATIBILITY_LEVEL = 150;
ALTER DATABASE [DBHealthHistory] SET COMPATIBILITY_LEVEL = 150;
ALTER DATABASE [pcfImport] SET COMPATIBILITY_LEVEL = 150;
ALTER DATABASE [TextSizeTest] SET COMPATIBILITY_LEVEL = 150;

I hope this saves you some time. It certainly save me a bit of time.

Related Links:

• SQL Server 2019 Compatibility Levels at SteveStedman.com
• SQL Server Compatibility check at Database Health
• Database Health Monitor

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!