Undo an Update Query 1

[TheStriker]

I fat-fingered an update query to remove null values and forgot to enter the WHERE clause so it updated all of the records. Is there a way to undo this?

 

[NoCoolHandle]

Run for you last backups

seriously.. If you have backups and need your transactions and have the logs you could use lumigent log explorer to see exactly when you made you mistake and then do a restore of the log to a point in time.

THen you would just need to recreate every transaction after that point (and the records you got from the lumigent log explorer should show you exactly what you need to do - just make a note of it beofre you do the restore.

Lumigent will let you download a "FREE" copy that blows up after X days. It can dig you out of deeeep do doos if you have the logs.


Good luck


Rob

 

[NoCoolHandle]

Oh yeah..

When I am using QA to run updates or inserts or deletes from REAL tables with REAL data, I tend to issue a

Begin Tran
update whatever
insert whatever

then you can do a select to test your data and if you like it

issue a Commit Tran..

In QA a I tend to have a commented out version of

COmmit Tran
and Begin Tran

If the select looks good, then a quick highlight of the commit or if bad a quick highlight of the Rollback and execute to either save your changes or put things back.


VERY good practice when playing with real data and updates..


Rob

 

[TheStriker]

Thanks guys,

I realized a little while after that I exported the original data to spreadsheets for some of my co-workers. Then all I had to do was import them back into the Server and perform the update the restore the column. Whew! that was a close one. Visions of the unemployment line just briefly overwhelmed me.

Anyway, that was some good pieces of information about lugiment and Commit and Begin Tran. Now I know a little more about recovering data.

Thanks again,
Striker

 

[NoCoolHandle]

Striker,

last year before doing some driver mirroring we lost 20,000,000 records to a corrupt database
No recient enough TL or ablitity to get it

I saw the same unemployment line you saw

What I did to fix the corrupt status and get back is wonderfully undocumented, but...

Fixing corrupt of suspect database

It came via a ms mvp I used to be in contact with.

Worth keeping around.


Rob

 

[jbenson001]

Rob.. good bit of help there.. Keeping on hand just in case

 

[TheStriker]

As a DBA, you can never have enough information and helpful tips regarding data recovery. Thanks NoCoolHandle, I will add this to my growing bank of knowledge.

Striker