A couple of weeks ago I said I'd bought some PASS Pro memberships to give away. If you haven't read about, take a moment to follow that link, and then email me the details of someone who could use it. If you're worried I haven't received it, you could send me a DM on Twitter if you like, or via LinkedIn. Just let me know.
I don't care where in the world you are – tell me who you think deserves to get one of these regardless.
Now, after some negotiating with PASS HQ, I've arranged the year for these memberships to begin counting from the end of September (rather than when I paid for them in August). But don't think about the timeframe, just let me know who.
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.)
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.