March 25, 2019 by Kenneth Fisher
Recently (honestly it’s been a few years now) Microsoft split the lifecycle for SSMS out from the general SQL Server lifecycle. That has the huge benefit of not having to wait on a new version of SQL to get new features in SSMS. They come out so often, in fact, that it’s easy to miss some of them.
For a long time now SSMS has had the option of scripting for a specific version of SQL Server. This lets you script specifically for 2008 R2, 2012, 2016 etc. Just as an example the script for an extended event changed quite a bit over the first version or two. It’s never huge things, but it does help to script to your target server.
Well in SSMS 16 this was what the option looked like:
Notice how you can select any version of SQL from 2005 to 2016.
But as of v17.9 the option has changed (I’m not sure if it changed somewhere between 17 and 17.9, I haven’t checked).
The important thing to notice here is Match script settings to source. Now rather than having to select the scripting version it automatically selects the version of the server you are on. And of course, if you need to generate a script for a different version you have that option too. Working on an upgrade for example, where you need a script for a higher level of SQL, or perhaps you’re halfway through the upgrade process, and your dev environment is a later version than your production environment so you need to script to a lower version.