Automating repetitive tasks in SQL Server Management Studio

by Adam Grande

This post is going to be somewhat shorter than my usual post. My job tends to have me focusing on the Microsoft stack, and as a result I will spend large amounts of time using SQL Server, and working in SQL Server Management Studio.

In my opinion, SSMS is pretty solid. Having used Toad/SQL Developer (of Oracle fame), I can confirm that SSMS is miles ahead of everything else I've seen elsewhere.

This post focuses on the idea of scripting out repetitive actions in SSMS. If you've ever done work with complex operations where a single action has multiple effects on the database, you've probably been in the situation where you want to test something repeatedly.

I will actually make a backup of the database when it's in a state that is ready for the operation I'm about to complete. I will execute the action, and observe the outcome, and then when I'm ready to re-test it again, restore that backup. This way I can get a clean, repeatable experience each time I test something.

The SSMS UI for restoring a database is fairly straight forward:

Right click on the 'Databases' node -> Restore Database -> Select your source and backup file, adjust the relevant options, and click OK. See, that's not too bad. However... there's a better way!

Before you click OK, have a look at the top of that window:

See that 'Script' menu at the top of the DB restore screen? If you click that, and click 'New Query Editor Window', it will generate the SQL script that is used to execute the equivalent commands of everything you've just selected in that restore screen, and open it in a new window!

Now, each time you want the database back to that state, you simply switch back to this window, hit F5, and BAM. Restored Database, without all those annoying extra clicks.

Be sure, in the Options screen before you script this out, that you check 'Close existing connections to the database' and/or 'Overwrite the existing database'. This makes the restore process much cleaner, and less prone to failing.

Sure, it's a small thing in the scheme of things, but things like this make our lives so much easier in the long run :) You can do this sort of thing with pretty much every action in SSMS, so hopefully this post inspires you to automate All of The Things™.

Good luck!