Does SQL Server have any kind of magic undo feature?

Question

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?

Answer 1

It's called the transaction log. You can roll it back. You have roll the whole log back to a specific point in time, not just your one bad transaction.

Answer 2

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

Answer 3

if you take database backups and transaction log backups then you can do a point in time restore to the moment before you executed the update statement otherwise no, not really

Answer 4

I am afraid there is none. This is why I always write the select see the results and then convert it to update. Also learned the lesson the hard way lol. Ofcourse you can restore from the transaction log.

Answer 5

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

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