Menu
Long story short is I tried to quickly update a single row in SQL Server using the Management studio and just typed UPDATE table SET column='value' and forgot the WHERE other_column='other_value' portion. Went for lunch, came back and theres 15 unread emails waiting for me. Happened about an hour ago, waiting for the database guy to come back to see when the last backup was. There's no magic UNDO feature though is there?
731
Undo happens when database goes through crash recovery or recovery. There are 3 phases of recovery The undo phase is where transactions that are not committed are rolled back so that they don't end up being in database when it comes online. Yes this information about transaction which is not committed is present in transaction log.
Magic tables are the temporary logical tables that are created by the SQL server whenever there are insertion or deletion or update (D. M. L) operations. The recently performed operation on the rows gets stored in magic tables automatically. These are not physical table but they are just temporary internal tables.
You won't be able to undo as it is.. Have you got auditing feature enabled you ll have all the previous values there.. You need to update your table from the audit table. i dont have any idea about auditing feature will you please tell me how to do it from audit table?
The recently inserted row gets added to the INSERTED magic table. The recently deleted row gets added to the DELETED magic table. The updated row gets stored in INSERTED magic table and the old row or previous row gets stored in the DELETED magic table.
Yes, there is - it's the transaction log. To recover from something like this, as long as you have your database in FULL recovery model, you'd just do another transaction log backup, and then do a restore with the STOPAT option set to tell the restore to stop restoring at the point in time right before you started your transaction. This will revert the database back to the point of your mistake.
See here for more info on using the STOPAT option - http://msdn.microsoft.com/en-us/library/ms186858(SQL.90).aspx.
Your script would look something like this...
-- backup the existing log
BACKUP LOG [MyDatabase]
TO DISK = N'\\MyServer\Share\MyDatabase.trn'
-- options left out for brevity
GO
-- restore the database first
RESTORE DATABASE [MyDatabase]
FROM DISK = N'\\MyServer\Share\MyDatabase.bak'
WITH FILE = 1,
NORECOVERY,
NOUNLOAD,
STATS = 10
GO
/* Previous transaction logs in the chain go here... */
/* restore your log that you backed up after the mistake
took place, stopping at your point where the problem happened */
RESTORE LOG [MyDatabase]
FROM DISK = N'\\MyServer\Share\MyDatabase.trn'
WITH FILE = 1,
NORECOVERY,
NOUNLOAD,
STATS = 10,
STOPAT = '2010-03-12 13:00'
GO
How to: Restore to a Point in Time (Transact-SQL)
The "point in time" is the "ohnosecond" before you bollixed things...
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
