November 7, 2008 at 1:49 pm
update document set sysid = 235
I mean to update only one record, unfortuntely the where clause is not there, I committed it too fast.
How do I do to roll back?
Thanks.
November 7, 2008 at 2:37 pm
You don't roll back. Do you have a current backup, prior to your erronous update? If so, you will need to restore it under a different name, and use the table in that database to correct the records in the table in your current database.
November 7, 2008 at 4:58 pm
Another option (which will require a downtime) is to:
1) Perform a transaction log backup (tail log)
2) Restore your latest backup
3) Restore transaction logs to point in time right before the mistake
If you have the disk space, Lynn's option is the better option since it will not incur any downtime.
For future reference, when I need to update data I create the script in my development environment first, test it to make sure it works correctly and only when I am sure it is doing what I want do I run it in live. And, just to make sure when in live - I do the following:
Begin Transaction;
Update/Insert/Delete statement
... Validate the results (important step here...make sure everything is the way it is supposed to be).
If not the results I wanted, issue the following:
Rollback Transaction;
If the results are correct:
Commit Transaction;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 8, 2008 at 4:32 am
Jeffrey Williams (11/7/2008)
Another option (which will require a downtime) is to:1) Perform a transaction log backup (tail log)
Assuming the DB is in full/bulk logged and there is a database backup.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply