Rollback or reverse actions caused by query

Question

Rollback or reverse actions caused by query

FroggEye

SSC Veteran

Points: 295
More actions
November 26, 2008 at 11:20 am

#127914

I ran a query which I want to roll back the actions of it. I did an update statement and failed to put in my where clause and updated about 300 records incorrectly. Is it possible to do this?
This is the statement I ran: update iworker_mstr set iworker_site_id = 2486195.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

If you didn't nest your update in a transaction, and you don't have implicit transactions turned on for your connection... then no.

Restore your database with a different name, and update your values to what they were from the backup.

This is why, whenever you're updating a production database, use transactions, and type everything out to do the update and verify the update ahead of time, that way you can very quickly make the change, check it, and commit it or roll it back with the least amount of effect on everyone.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Actually, my first post isn't necessarily true. You may be able to restore the db and the transaction logs up to the point of your bad update query if you have the right logging method set on your db, but I don't know enough about that to explain it.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Yeah, point in time recovery depends on the database using full or bulk-logged recovery model. So more information is needed from the OP.

Greg

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Garadin (11/26/2008)
You may be able to restore the db and the transaction logs up to the point of your bad update query if you have the right logging method set on your db, but I don't know enough about that to explain it.

DB must be in full or bulk-logged recovery.

There must be a full backup.

There must be a complete, unbroken chain of transaction log backups from the full backup to after the bad update.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

FroggEye SSC Veteran Points: 295 More actions · Answer 5

Thanks guys for your quick responses! This has helped out alot