How to adjust Compatibility Level after database restore?

Question

We have a few SQL servers. with different version i.e. 2005, 2008, 2012, 2014. (we plan to move away from 2005 soon). Many times our DBA will simply backup and restore a "template" client database to create a "new" database from the restored template.

Problem is the compatibility level sometimes is 80 (or 90) after the restore. our new SQL scripts require at least level of 90 for new SQL features.

So, I wrote a script that will check the master database compatibility level and adjust the target client database on that server.

DECLARE @sys_compatibility_level tinyint, @db_compatibility_level tinyint;

SELECT @sys_compatibility_level = compatibility_level
FROM sys.databases
WHERE name = 'master';

SELECT @db_compatibility_level = compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

DECLARE @db_name nvarchar(128) = DB_NAME();

IF @db_compatibility_level < @sys_compatibility_level
BEGIN
    -- EXEC dbo.sp_dbcmptlevel @dbname=@db_name, @new_cmptlevel=@sys_compatibility_level -- deprecated
    EXEC('ALTER DATABASE ' + @db_name + ' SET COMPATIBILITY_LEVEL = ' + @sys_compatibility_level)
END

My question, is this approach correct? should I check the master db or maybe the modeldb? are there any drawbacks from blindly setting the client db to that of the master/model?

Answer 1

since you are restoring databases,they retain the old compatabilty level..Newly created databases would inherit model database compatabilty level..

So change restored databases compatabilty level to the one you wish,not based on model/master..

change model database compatabilty level to one you choose,so new ones would inherit