CODEWITHSUNDEEP
sql-server
Andrew G. Johnson
Andrew G. Johnson

Reputation: 26993

Does SQL Server have any kind of magic undo feature?

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?

Upvotes: 13

Views: 573

Answers (5)

Scott Ivey
Scott Ivey

Reputation: 41568

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

Upvotes: 19

gbn
gbn

Reputation: 432421

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

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

Upvotes: 4

Raja
Raja

Reputation: 3618

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.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135111

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

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416039

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.

Upvotes: 2

Related Questions