If you haven.t already upgraded to SSMS 2016 you should do immediately. The guys at Microsoft have decoupled SSMS from the SQL Server release cycle and one of the best features by far is the auto-save\recover functionality.
Remember all those times you were putting the finishing touches on the best code you’ve ever written and SSMS crashed? Well worry no more. I did post previously about how SSMSBoost can help you to recover recent sessions, but now SSMS does it out of the box.
When SSMS crashed you’ll be asked to recover recent work and will see something like the below:
All you need to do is connect them back up and viola you’re away!
Of course you could always (and you should always) save your work as soon as you create a new window, but who am I to tell you how to live your life….
According to MSDN, SQLCMD can be used to process Windows System commands and Transact-SQL statements in the same script.
One of the most useful things you can do is pass parameters into your script from the command line by using the :setvar command and use them to replace sections of your script, such as user names. Variables set when using :setvar cannot be updated as they are passed into the script when it is called.
You can turn on SQLCMD in SQL Server Management Studio by selecting SQLCMD Mode form the Query menu:
:setvar ENV INT
IF DATABASE_PRINCIPAL_ID('BestDomain\BD_Svc$(ENV)') IS NULL
CREATE USER [BestDomain\BD_Svc$(ENV)] FROM LOGIN [BestDomain\BD_Svc$(ENV)];
The above script accepts the parameter ENV and uses it to replace $(ENV) with the value of the parameter, in this case INT. As you can see the above script, when executed, will add the SQL Server login BestDomain\BD_SvcINT, to the BestDatabase database.
I’m sure you can imagine the rest of the possiblities!
I’m sure, many times you’ve all faced the frustration of losing your work due to an SSMS crash. The good news is that if you’re using SSMSBoost your recent query windows will be saved.
If you go to the settings of SSMSBoost and check the Script Execution History tab, you’ll see there is a checkbox that will Enable Execution History, you can also choose where to store your files.
Extremely useful and this doesn’t require you to save every query window that you open!