Avoiding and Recovering From Accidental Data Deletion

People make mistakes, since they are only human. Sooner or later, everyone will do something like forget or mess up a WHERE clause, when running an ad-hoc  DELETE or UPDATE query in a Production environment. Rather than seeing "1 row affected", you see "500,345 rows affected" and a cold chill runs down your spine…  You think, "What have I done!"

Here are a few ideas on how to avoid that fate:

  1. Always, always try an identical SELECT statement to the UPDATE/DELETE statement before you run the UPDATE/DELETE. This will confirm that your query does what you expect in a safe manner.
  2. Wrap your UPDATE/DELETE statement in a T-SQL transaction. Simply issue BEGIN TRAN, run your UPDATE/DELETE query and then COMMIT or ROLLBACK TRAN. That way, you can rollback if things go wrong.
  3. If you are using SQL Server 2005, take a database snapsnot before running the UPDATE/DELETE query. That way, you can very easily recover the data from the snapsnot if you need to.
  4. If you are using a modern SAN, you can also take a SAN snapshot before your UPDATE/DELETE query. Then, you can, with a little more work, recover data from the snapshot.
Technorati Tags:
This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s