Generating scripts for previous versions of SQL

1

October 21, 2015 by Kenneth Fisher

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!

OldScripting1

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.

OldScripting2

OldScripting3

One thought on “Generating scripts for previous versions of SQL

  1. […] Set the scripting option to the version of SQL I most use. I like to use the latest version of SSMS. Unfortunately these days that means I’m working with a version of SSMS that is sometimes 3 or even 4 versions ahead of the version of SQL Server I’m connecting to. If I want to use the scripts SSMS is going to generate for me I need to tell it what version of SQL I’m scripting for. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,151 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: