Today I’m going to talk about something that more people should be aware of: potential loss of data when performing delete or update and the dangers of performing such actions without performing DB backup.

And I’m not talking about regular daily or weekly backup (it is supposed to be done without saying, an old mantra that nobody doubts, something as normal as eating and sleeping). No, what I want to talk is making backups before making any actions against database that can result in data loss.

I noticed that my fellow co-workers often delete or change the data or run the change scripts without backing up the database first. Yes, this is perfectly acceptable when it is done in the test environment, but when it comes to live databases, I strongly advise against doing so. Seriously, unless your DB is really big and your server very slow, does it really hurt so much spending a few minutes of your time to do it? “Why?”, you ask. Well, just imagine that something goes wrong. And I mean REALLY wrong - you deleted the right record(s), but you weren’t aware of a cascading delete that pulled a lot of “healthy” data with it. Or your script deleted the wrong data. The list can go on, but you get the point.

So, something went wrong, you realize it now. Any minute now the phone will ring, or Skype chat window will pop-up. You know that it is your client. Your super angry client that is now set back to 2011 because of your poorly written delete script. What are you going to tell them, how long it will take you to fix it? If you backed up the DB before you run the script, not long. But, if you didn’t.. Well, let’s hope that maintenance plan that your co-worker set-up still works, so you have last night’s backup. But, what now? If you restore it over the live database, your client’s today’s work will vanish. “OK, I’ll restore the backup, name it differently and then copy the missing data back to the live database!”, you say. Do you have DB compare SW installed on your live server? Yes? -you’re half-lucky because, in addition to missing data, you will also restore the data that was intentionally deleted today. If not, then be prepared to write a script on the fly. Be prepared that it WILL take long. Maybe not for you, but definitely long for your client that can’t work until they get their data back. Did somebody say “Stress”? Yes, I think you now get the idea how it must look like. And all that because you didn’t have a 5 minutes to do the backup? Trust me, this 5 minutes of your time can save you HOURS!!!

What if you have a large DB that takes long to back-up and you really don’t have that time? What if you absolutely must do the edit/update without backup. There is a way to test of the script is running fine: run it inside of a BEGIN TRANSACTION / ROLLBACK TRANSACTION block and add script that checks the results after the delete/update. I do this always, regardless if I have a backup or not:

BEGIN TRANSACTION
--do the scary stuff
--check what the scary stuff did
ROLLBACK TRANSACTION

Once you are 100% sure it runs fine, replace the ROLLBACK with COMMIT and run the query:

BEGIN TRANSACTION
--do the scary stuff
--check what the scary stuff did
COMMIT TRANSACTION

There it is, just my view on things that kept me from doing something really stupid. I hope you never learn it the hard way.