1

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?

zig
  • 4,524
  • 1
  • 24
  • 68

1 Answers1

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

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • stupid question: is it guaranteed for every sql server to have a `model` db? – zig Jan 09 '17 at 08:26
  • yes,master,model,tempdb all are system databases and they are present on every instance of sql server – TheGameiswar Jan 09 '17 at 08:29
  • strange, the `model` is set to 100, and the restored db is set to level 80. how could that be explained? – zig Jan 10 '17 at 09:47
  • @zig: restored database will retain its old compatabilty level,unless changed – TheGameiswar Jan 10 '17 at 09:56
  • OK. missed "**Newly** created databases would inherit model database compatabilty level". Is there actually any harm in setting compatibility level to say 100 or 120 (based on the current model) if they work fine in level 90? – zig Jan 10 '17 at 10:10
  • 1
    @zig:some new features will work only in new compatabilty mode,some may not work ..check out this link for more details.https://msdn.microsoft.com/en-us/library/bb510680.aspx – TheGameiswar Jan 10 '17 at 10:33