Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does SQL Server have any kind of magic undo feature?

Tags:

sql-server

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?

like image 731
Andrew G. Johnson Avatar asked Mar 12 '10 18:03

Andrew G. Johnson


People also ask

What is Undo in SQL Server?

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.

What is magic table in SQL Server?

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.

Is it possible to undo previous values in audit table?

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?

What is the difference between inserted and deleted magic 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.


2 Answers

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
like image 179
Scott Ivey Avatar answered Oct 13 '22 05:10

Scott Ivey


How to: Restore to a Point in Time (Transact-SQL)

The "point in time" is the "ohnosecond" before you bollixed things...

like image 22
gbn Avatar answered Oct 13 '22 05:10

gbn