Nobody is Perfect
OK, a show of hands everyone. . . tell me how many of you have run a query against the wrong instance. . . oh, and when I say wrong I mean W-R-O-N-G. We're talking take-down-production-and lose-data-wrong. Come on, don't be shy. Don't lie to me. I can tell when you're lying. You're about as good at it as my 13-year-old son.
There we go. One hundred percent of you except that fellow from Cambridge and the dear lady from Nashville. I'm raising my hand as well. Heck, I'm raising both hands!
One simple solution you can employ to avoid running a query against the wrong instance, is altering the code that populates each and every New Query window that is populated in SQL Server Management Studio (SSMS). . . the default query.
The Default Query
This is something I picked up from a dear friend of mine, Mladen Prajdić, in his SSMS Tools Pack. Each time you launch a new query window in Management Studio, what is actually happening is that a version of a .sql file on your workstation/laptop. By default, the file is empty, blank, void. However, what if you were to alter it. Perhaps something like this:
BEGIN TRANSACTION --ROLLBACK TRANSACTION
would allow you to protect yourself when crafting new queries. Pretty simple huh? You could even simply add the comment block syntax to the default query and then anything you begin to type after ends up being completely harmless unless you go out of your way to make it executable:
/*
No chance of harm there, now is there? So how can you do this? Well, you can download this SSMS add-in because this is just one of the many features it provides, but until then, you can easily go in and edit your default .sql file to give you just one more safety switch on doing something wrong against one of your valuable databases (or perhaps I should say doing something right at the wrong time to one of your databases).
Where is the Default Query File?
File location for the default query file for SSMS on your client workstation depends upon the version of SSMS you're running:
- SQL 2005 = C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\sqlfile.sql
- SQL 2008 = C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\sqlfile.sql
- SQL 2008R2 = C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\sqlfile.sql
- SQL 2012 = C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\sqlfile.sql
Doing so means that each time New Query is selected from the menu bar in SSMS the query stored in that location is opened. Granted, it will not completely allevieate the chance that you'll run a query against the wrong (read production) instance, but it will reduce that risk.
Caveat
Of course, there are rules. You may not have rights to this folder or file and might need to explicity grant yourself rights; even if you're an Administrator on your workstation. If you get a save error then this is indicative of needing to grant read/write permissions on the file. This is also dependant upon operating system versions and patching as well.
Closing
We will be looking at other options to help prevent you from doing unintended harm in your environments. Stay tuned for more in my column on the subject.
