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:
- 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.
- 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.
- 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.
- 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.