How can I minimize the risk of accidentally modifying the wrong database?

Question

I just learned the hard way that disconnecting from a server in the Object Explorer doesn't stop you afterwards from executing query windows that were already open on that server.

My situation is like this: I have one instance of SSMS that I use to connect to our dev/staging server and to our production server. I had to delete a bunch of data on dev so I figured I should close my connection to production, but I didn't pay attention to the query window I was using. (Luckily we had a backup of only a few hours old.)

I'm not the first person to destroy production data and I won't be the last I'm sure. So I'm looking for checklists, best practices, etc. that help you to minimise the risk of executing queries on the wrong database. Have you had this happen to you before, and how have you adapted your workflow to try to avoid this?

Answer 1

One thing I like to do in SSMS is to use Custom Colours when connecting to database. So you choose a nice bright Red for Live databases, and a gentle blue or green for dev or test systems. I used to use the inbuilt SSMS, but these days I prefer the SSMS Tools Addon Colour coding.

Or like this for SSMS Tools (A really nice addon, and I find the colour better when it's on top, rather than on the bottom like the built in on)

Answer 2

Depending on who you ask, it will require a little more work, but I got in the habit of always using the below statement for all production or pre-production query windows, and for all UPDATE, DELETE and INSERT statements in all environments.

BEGIN TRAN
-- END OF QUERY WINDOWS
ROLLBACK TRAN
PRINT 'Transaction rolled back.'

If I see this, I will immediately know, "Oops, that query window was still connected" or "Oh crap, I auto did something I shouldn't have" - and yes, you can close the database in object explorer, but a query window can still be connected. In my mind, all production queries should be highlighted and run with BEGIN TRAN; an accidental F5 on everything, should roll everything back, not COMMIT. What this does is force to user to be conscious of his or her actions; similar to taking a picture of every meal you eat will help you lose weight because you must stop and think about what you're doing.

Does it take longer to do? Yes. Does it stop 100% of errors. Yes, because nothing ever commits, unless I manually force the COMMIT, post typing it, which the very nature of will have forced me to consider the COMMIT.

Answer 3

Create a second user account for production changes and revoke the access your account currently has. When you want to do stuff in production you can run ssms as the second user.

EDIT: This would only be beneficial in the case of domain logins. If you had two separate domain accounts you would be forced to have separate instances of SSMS for DEV and PROD. If you are not using domain accounts, this suggestion wouldn't really help you out much.

Also, if you are using separate domain accounts you can adjust your SSMS color settings per user, maybe having a bright red background for the account that connects to PROD.

Here is a good white paper that came to mind as well: http://download.microsoft.com/download/D/2/D/D2D931E9-B6B5-4E3B-B0AF-22C749F9BB7E/SQL_Server_Separation_of_Duties_White_Paper_Jul2011.docx

It discusses things like not giving your daily login account full SA access.

Answer 4

Have a look at my add-in: SSMSBoost. It has exactly what you need. I have improved SSMS Status-bar coloring feature so that it tracks your current database and changes it's color. Additionally you can add "important DB alert" floating tooltip:

Read more about this feature here: http://www.ssmsboost.com/Features/ssms-add-in-preferred-connections

Answer 5

In one of my jobs we developed a tool for this purpose.

If you wanted to run a statement on PROD, it forced you to write:

run_sql servername PROD <file_with_sqlstatements>.sql

It would write the results to a logfile and append the execution into a log in our management database. It came very handy for instance when we wanted to figure out who was the last person to change a certain table.

In SSMS, when you have registered servers, you can apply a certain color to a connection, so that for instance all PROD-connections have a red color at the bottom. But it's best to avoid using GUI-tools on a production server if possible.

Answer 6

Just two more tips, since I don't see anything similar here already:

1. In my workflow I often work with multiple statements in a single window, and I am quite used to select-text-then-run flow. But I am always scared to accidentally press F5 when no text is selected and execute all statements in a window as a result. So every time I open a new window I start with typing whatever garbage SQL will refuse to compile. This effectively makes whole batch non-executable. (Warning! If you use multiple batches separated with GO then garbage is required per batch.)

2. When doing data changes on production server (or whenever I need extreme care) - implicit transactions are very useful (you either SET IMPLICIT_TRANSACTIONS ON or change an option in SSMS so that the option is effective for every new window). This way every statement which is not in transaction - starts a new transaction. I only commit if I make sure twice that I did what I intended to.

Answer 7

Try using a seperate windows user, who is the only one that has the production database configured. Set the whole color theme of this user to red. With fast user switching this should be no problem.

Never use the production credentials in an account that is on a development machine. A short phonecall or coworker question and afterwards you are happily deleting everything for your new testrun...

Another option (same idea) is using a remote desktop or virtul machine with a different theme.

Answer 8

Another rather straightforward way of preventing execution of everything in the query window when F5 is pressed would be to surround all the content with /* and */ thus making the whole thing a comment.

You can still execute the statements you want by highlighting them and pressing F5 in the usual way, even though they are enclosed in a comment.

Note: if you choose this method, you will not be able to benefit from syntax highlighting or auto-complete, but if you don't use those features that much then it could be worthwhile sacrificing them to ensure it is 100% impossible to damage the database with an accidental F5.

Edit: you also won't be able to use /* */ anywhere within the query window, otherwise you will inadvertently uncomment subsequent code. Need to stick with the -- notation instead.

Answer 9

In agreement with swasheck's comment on the original question, how about executing...

select @@servername + '\' + @@servicename

...before running any DML, or even looking at the status bar to see what instance you're connected to, or even running all DML in a transaction so that you can rollback if you realize you've made a mistake? A lot of great suggestions here, but basically, when it comes to potentially destructive DML, gimmicks will only get you so far. I always check, double-check, and check again. And if I'm dealing with a small amount of data, I might even SELECT INTO a new table prior to the DML, run my DML, do some comparisons to make sure things worked correctly, then drop the "backup" table. Work smarter, not harder.