December 24, 2009 at 9:54 am
Hello,
I have (stupidly) run an update statement on a table and I need to go back to the original data.
I just now executed:
update Images set ImageName = '123.jpg'
instead of:
update Images set ImageName = '123.jpg' where ImageID = '3'
I have now messed up all my data. The backup is a couple of days old and a lot has changed since. Is there a command to undo the last update to the Images table?
Thanks,
Dave
December 24, 2009 at 11:05 am
Unfortunately, your only option is to go back to the old backup and restore. You can restore to a separate database and then perform a comparison on just that table. That might get you back to where you were, but it all depends on how many changes have been made to that table.
Also, check whether or not your database is in full recovery model. If so, make sure you take a new transaction log backup (tail log backup - look this up in books online). Then, you can restore from the old backup and apply the transaction log backups to get you restored to a point in time just before you issued your update.
If you are not running in full recovery model - and you don't have transaction log backups then you either lose all of the changes, or you try the first item above and see if you can get back to somewhere close. You are going to suffer data loss of some sort here - most likely.
In the future, you can prevent this kind of issue by using the following in your scripts:
BEGIN TRANSACTION;
[issue your insert/update/delete statement]
ROLLBACK TRANSACTION;
In other words, always wrap your ad-hoc changes in a transaction. Run in the above format you can verify the results are what you expected by issuing select statements before and after the change. Once you are satisfied with the results, change the rollback to a commit and execute the script.
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
December 24, 2009 at 11:32 am
Thanks Jeffrey,
I didn't have time to do all the comparisons as the client was waiting on the system to work properly again. So, I found some software called ApexSQL Log, which is quite expensive, but worth it in this particular case.
It was able to inspect my transaction log and create a sequence of update statements that basically changed all my records back to their original data.
A lesson learned. Just type a few extra lines to save a thousand dollars! Whew.
Thanks again for the quick response!
Dave
December 24, 2009 at 11:53 am
Yes, as long as you have the transaction log entries available you can use a tool like that to recover. Glad you were able to get it back where you needed it.
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
December 29, 2009 at 12:21 am
Good that you got your data back.
Another way to avoid such accidental updates is to set "implicit transaction" ON in a session or globally in SSMS.
To enable implicit transactions in a session use "SET IMPLICIT_TRANSACTIONS ON" command.
When this setting is on, each time you make a change, you'll have to issue a "Commit" to make the change permanent.
To globally use this settings for each new session in SSMS goto [font="Tahoma"]Tools->Options->Query Execution->SQL Server->ANSI[/font] and click the checkbox "SET IMPLICIT_TRANSACTIONS". Since this setting applies to all connections in SSMS, I would not recommend using it as you may unnecessarily or unknowingly lock tables.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply