Elizabeth Noble (@sqlzelda) asks us to write about automation this month. I’m a big fan of automation – I know that if I just rely on muscle memory to do things the same way, I’ll make mistakes. We all make mistakes from time to time, and I’m certainly not immune.
So automation is a really big deal, but even more so, using scripts to do things instead of using a user interface.
User interfaces are great, but I simply don’t want to have to remember to do everything the same way each time.
To that end, I want to wax lyrical for a moment about the Script button on most dialog boxes in SQL Server Management Studio (SSMS), and make a quick mention of what things (currently) look like in Azure Data Studio (ADS). (I say “currently” because ADS is still under very active development and could have changed even by the time I publish this.)
First SSMS…
By default, if you want to drop a database in SSMS, you go to the Object Explorer, find the database in question, right-click on it, choose Delete (seriously – that should say “Drop”, as Delete is a data manipulation term, not a data definition term), pick the odd option in the dialog, and hit OK. You could try it now, but preferably not in a production environment.
And then if you’re teaching someone how to do this, you explain to them what to choose for the various options, and hope they do it correctly. Restoring a database (which you’ll want to do if you just dropped a database you shouldn’t have – sorry about that) is similar. You get a dialog box, you pick the options you want, and you hit OK.
The hassle is that if you use a different set of options you get a very different behaviour. If you’re dropping a database and you don’t use the “Close existing connections” option, you might have to wait for a while for the drop to happen. Or if you use it when you didn’t mean to, some users might not appreciate having their transactions roll back.
Conversely, if you’re doing a restore database and you forget to choose between RECOVERY / NORECOVERY / STANDBY, then you might inadvertently interrupt a recovery process and lose precious time as you start it all over again.
Instead, you should have used the Script button. I’m not a fan of the OK button on these dialogs at all. People should use Script and then Cancel, and use the Script you’ve run.
Now, I get a useful script which I can then run, and I can keep it for next time too.
But ADS does it differently again. If I right-click on a database there, I don’t get a Delete option at all (although there are extensions available to provide that). But if I go to an individual table, there’s an option to immediately produce a Script for Drop.
I’m sure in time ADS will provide all the functionality of SSMS, including something to generate a Drop Database script complete with all the options that SSMS provides. In the meantime, I will continue to use dialogs to create scripts (enhancing them as much as I feel I need), and then holding onto them for ‘next time’, or for when I get someone else to run it.
…or for when I automate it.
This Post Has 3 Comments
That scripting button is a lifesaver in SSMS. It helps keep people from doing bad things in the GUI, and gives you a starting T-SQL script to edit and save.
Definitely. I mean, you can break stuff with scripts too, but it’s way less error prone.
Pingback: T-SQL Tuesday #130 – Recap: Automate Your Stress Away – Elizabeth Noble