Do you have an environment where databases get restored onto your
SQL Instances from different sources? If yes, you must have come across the
fact that all the databases do not originate from the same version of SQL
Server. If you have a SQL 2016 Instance, it is possible that databases get
restored from different Instances on different versions such as SQL 2014, SQL
2012 etc. When such a move happens, the compatibility mode of the db needs to
be changed to match the level of the Instance where it is being restored to
(SQL 2016 in this case). If that not done, you are not letting the db make use
of all the features of SQL 2016.
What if that db creation process is constant? How do you keep up with automating the process of checking for any dbs with lower compatibility level and then change that to match the version of the SQL Instance? Below script does exactly that for you. You an automate the process by scheduling the below script as a SQL job that runs every day.
DECLARE @BUILD VARCHAR (MAX)=substring(CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')),1,2)+'0'
DECLARE @CMD VARCHAR(MAX)
--SET @CMD= (
SELECT @CMD = 'USE master;' + (
SELECT CHAR(10)+'ALTER DATABASE '+NAME+' set compatibility_level = '+@BUILD
from sys.databases
where database_id>4
AND compatibility_level <> @BUILD
FOR XML PATH('')
) + CHAR(10)
--)
PRINT @CMD
EXEC (@CMD)
