Alter database compatibility on all databases in a SQL Server Instance

09 April,2018 by Jack Vamvas

Question: I have a SQL Server Instance with 102 databases at a database compatibility level of SQL Server 2012 (110). I've recently migrated all the databases onto a SQL Server 2016 instance - and the database compatibility level is still at 110.

Is there a scripted way I can change the database compatibility level of all the user databases to SQL Server 2016 (130), rather than having to hand crank for every database?

Answer: Hi - this is a common problem particularly during SQL Server migration work , which you could solve by generating a script with all the ALTER statements . You could then review the list - and execute the script.

Here is a sample script, that will list out an ALTER statement to change the database compatibility of every user database. In the SELECT statement , I 'm only listing out database with an id greater than 4 - therefore excluding the system database - master, model,tempdb and tempdb .

USE [master]
GO
select 'ALTER DATABASE ['+name+'] SET COMPATIBILITY_LEVEL = 130' from sys.databases where database_id > 4

Executing the script will generate some output , such as below. Once you've reviewed you can either execute the script on the target server .

ALTER DATABASE [db1] SET COMPATIBILITY_LEVEL = 130;
ALTER DATABASE [db2] SET COMPATIBILITY_LEVEL = 130;
ALTER DATABASE [db3] SET COMPATIBILITY_LEVEL = 130;

 

 

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Alter database compatibility on all databases in a SQL Server Instance


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer