Monday, August 27, 2007

How To Recover From Bad Update or Delete Querries in SQL Server 2005

It happened to me... the good old update query without the where statement :( The end result was zeroing out hand-coded data that took me about 4 days to enter.

After searching the web for a while I came up empty. Finally I thought of a different way to run the query and found another blog that mentioned 3 products. The only one that worked with SQL 2005 was "SQL Log Explorer". I tried the eval version and after a couple tries got it to load the 54GB log from my DB that was 104 gigabytes. Needless to say this took a while.

Once I narrowed log entries to the appropriate table and date range I found one of the entries from the offending query. All I needed to do at that point was right-click the entry and tell it to "undo". What resulted was the generation of about 50 pages of SQL statements that I fed into SQL Manager and voila!!!! All my data was back.

SQL Log Explorer is an impressive product! I highly recommend it.

Also of note: SLE does not allow you to work on any db in the trial version... it only lets' you work on their DB and on the "pubs" db. As for me, I wasn't tied to my db name so I renamed my DB to "pubs" and ran the program on it.

We have now placed an order for SLE just for those special times when one of us shows our human side.