Codito Ergo Sum

So you did it. You ran an UPDATE or DELETE SQL statement without a WHERE clause. Don't panic (well, panic, but don't despair). You can fix it with a few steps I will outline here, provided you are doing a few 'best practice' SQL maintenance items.

First, some pre-requisites to disaster recovery:

• You need to be doing backups! I perform nightly full backups to a disk location, and we write to tape on a weekly basis.
• If you are going to restore to a 'point in time' right before your disastrous SQL executed, your database needs to be in 'Full Recovery' mode. This means that all statements processed against it get written to a transaction log. That log will save your bacon here!
• I am working in a SQL 2005 environment, but I believe these statements and methods apply to SQL 2000 as well.

So, the first thing I did was to note the exact time my 'bad sql' executed. You will need this later, write it down now as closely as you can, but be SURE that the time you write is BEFORE your statement (give yourself a little pad if you can, maybe 5 minutes prior).

Next, you need to stop any database activity. I went to the application level and gave my users a quick 'maintenance' message. I then took the database down (to kill any connections), and brought it back up. I performed a transaction log backup of the bad database (only transaction log needed) to a file (note where you place this file, you will need it later). After you perform your 'to a file' transaction log backup, I then re-named my 'bad' database to something completely different, so that you have it (just in case!)

Next, you need to restore your most recent full database backup. You can do this with a command something like this, or use the graphical tools, the important part to note here is: WITH NORECOVERY. This tells the SQL server not to bring the database up and live yet, as you are not done working with it! You will use the first command to retrieve the file names used in the backup, because you do not want to overwrite those files. Then use the filenames for the Data and Log files in the next SQL statment to perform the restore of your database from (hopefully) the prior evening.

If all went well, now you have your database restored to its proper name, and containing the data from your most recent backup. Now we are going to restore your transaction logs. Note! If you only perform a full backup weekly, you would need to restore every transaction log that was backed up after your last full weekly save, up to the prior evenings log! If you have to do this, simply change the 'WITH RECOVERY' to 'WITH NORECOVERY' for each full transaction log you are restoring up to the prior evening, then run the following SQL statement 'WITH RECOVERY' to bring your database back online and ready for action!

If everything worked out, now you have your database (with corrupted or damaged data) re-named so you can still pull information from it if necessary, and you have your ready-to-run production database restored to a point in time. If you are not running with a full recovery model, the best you are going to restore too is your last full backup, as the transaction logs are not available to 'play forward' like we did here!

Good luck, its a harrowing experience to destroy data, but if you are prepared you can save the day.