Change compatibility level on multiple databases and multiple instances

Following my previous post on mass change of PAGE_VERIFY option, I want to share one other script that you can use if you are using a Centralized Management Server and multi-server queries in order to change the compatibility level of multiple databases and instances.

With the below script you can change the compatibility level of all databases and in my example I am setting it to compatibility_level = 100

--mass change the compatibility level option for all instances and 
--databases that are not read only and have current comp. set to 80 or 90
EXEC
sp_MSforeachdb '
use [?]
if (select count(*) 
	from sys.databases 
	where compatibility_level <100 and 
	name=''?'' and database_id>4 and
	is_read_only = 0
)=1
begin
alter database [?] set compatibility_level = 100
PRINT ''compatibility changed to 100 for database = '' + DB_NAME()
end'

 

6 Replies to “Change compatibility level on multiple databases and multiple instances”

  1. Will fail with offline DBs.

    You don’t need to USE [?] to the database to change its compatibility level. Also notice that if DBs are coming from sql2000 you could SET PAGE_VERIFY CHECKSUM too as otherwise these will be using torn page detection by default.

    1. Hi wqweto,
      Well, yes, it will fail, but then – why do you have offline databases on the server? :-)
      I use the “USE [?]” because I need to get in the context of the database and use DB_NAME() later on, otherwise it will just throw me “master”. I know that there is a better solution, but hey, I just wanted to share what I used :) You are free to rewrite, my purpose was just to use the script to get you started. And the PAGE_VERIFY – yes, this is something that you should do, as well as DBCC UPDATEUSAGE, sp_updatestats, db owner, etc. But this is not part of this post ;-)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.