Last month in January, I participated in the Idera #sqlchat.  This is a monthly chat meeting that is organized by Idera Software and hosted by a SQL Server community member. This one was hosted by Monica Rathbun (B|T) and her topic for the month was “SQL Server Bad Habits in Configurations, Security, and Code”.  It was a fun and entertaining way to spend an hour over lunch.  Plus it helps to get you engaged with the SQL Server community, which is never a bad thing.

During the course of the chat, one member mentioned a mistake they made in which a table in Production was accidentally truncated.  I had mentioned that when I run queries in production, I try to follow the “4 Rules of Right”

I use the 4 rules of right when running a query: Right server, Right Database, Right query, Right recoverability. #truestory #sqlchat

— DBA With A Bat (@SqlrUs) January 3, 2018

Quick back story.  In a previous life, I was a volunteer EMT for the community that I lived in.  One of the things you are taught is the “5 Rights of an Medication”.  Anytime that you administer a treatment to a patient, we should check the following 5 rules:

• Right Patient – do I have the correct patient that I’m treating? Pretty self-explanatory.
• Right Medication – do I have the right medication to give to this patient?
• Right Dose – Is the dosage amount correct? Medication can severely harm (or even kill) a patient so this is an important one.
• Right Route – Am I going to administer this medication in the right manner? Orally? Intravenously?
• Right Time – Is it the right time to administer the medication? Administered too soon the medication could cause problems.  Too late, same story.

The 5 Rules of Medication are there to help ensure that I, as an EMT, do no further harm to the patient.  Not only do they protect the patient (if I’m doing it right) but they also protect myself as an EMT as all of this should be documented.

As a DBA, I’ve morphed that the 5 Rules of Medication into the 4 Rules of Right whenever I execute a query.  This is especially true if I know that the query is going to touch the production environment.

Right Server

Am I connected to the appropriate server when I hit F5 or click Execute?  It is a very easy check to ensure that you are on the right server.  If you are connected via SSMS, just glance at the Connection bar and double check.  I’ve caught myself more than once about to execute a potentially damaging script in the wrong environment.

Right Database

Will the right database be affected when I execute this query?  Am I connecting to the database through Management Studio or does the query have a “USE” statement within it? Does the query have multiple USE statements (it does happen)?

What about three-part naming?  If you are running the query as a system administrator (SA) and the query does have three-part naming contained within it, are those the right databases?

While this all sounds like a lot, unless the script is large, it should be relatively easy to scan through the it and make sure.  Better to take a few seconds to confirm rather than destroying the wrong data.

Right Query

Do I have the right query in front of me?  If you are like me, you potentially might have multiple query windows open.  This can cause confusion and it is easy to touch the wrong database.  How many times have you created a new table and then realized that you were still connected to Master?

I am quite sure that I’m not the only one who has executed a query and then suddenly realized that it was the wrong window.  It happens I assure you.

Take a quick second and double check.  The life you save just might be your SQL Servers.

Right Recoverability

This is a big one for me. Do I have the right recoverability process in place for this query?  If I know that I’m going to be modifying data, I make sure that I have some method to capture the original state of the data if at all possible.  This might be a good full copy-only backup.  Or maybe a copy of the data that is being modified placed into a physical (not temp) table that I can retrieve from?

If I do have a copy of the data, do I have a way to cleanly update the new data with the old if I have to roll back?  Is there a way to easily join the two tables together?  If not, I find a way to make that happen.

I will often also have a script ready to go in case I do have to roll back.  This might seem like overkill however, well, time is money.  The quicker I can get things back to a previous good state, usually that’s a good thing.

Summary

Right Server, Right Database, Right Query and Right Recoverability.  Four simple little things that you can check just before executing a query.  While the rules can seem cumbersome, like the rules an EMT follows they are there to help protect you and your patients.  They are easy to follow and with a little practice, it’s easy to implement them every time you go to execute the query.

Enjoy!

© 2018, John Morehouse. All rights reserved.