I make a habit of using the latest version of SSMS even if I’m not working with the latest version of SQL Server. For example right now at home I’m using SSMS 2016 even though I frequently work with SQL Server 2014 or 2012. Well this can cause a bit of a problem when I’m using the script button to generate scripts of changes I make in the GUI. With each version some of the commands change, some of the settings change, etc. This means that when I script out something with my latest version of SSMS I’m getting the latest version of the code. Not good if I’m trying to run that script on the older version of SQL.

For example when I scripted out a database it started with this:

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY 
 ....

Run that on a 2005 instance, I dare you :). Contained databases weren’t introduced until SQL 2012, so this script won’t run on 2008 or 2008 R2 databases either. So what’s the solution? Change one of the scripting settings!

Now when I run the same script I get this:

CREATE DATABASE [Test] ON  PRIMARY 
....

And there we go. A 2005 version of the same script.

Note: this scripting option is also available in the Generate scripts advanced scripting options.