Generating scripts for previous versions of SQL


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:


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:


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.



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: Logo

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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

Join 3,755 other subscribers

Follow me on Twitter

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