How to undo an UPDATE or DELETE in SQL Server

• Posted by:
• Olly Lennox
• No Comments

When ‘that’ Update statement wipes your database

 

No matter how careful or experienced you are at SQL Server development, there will inevitably be times when you run a query that you really wish you had not run. I was working on a project recently for a client and hacking around with my local development environment to try and fast track a piece of functionality. The hack involved updating a series of rows in a 1,500,000 row table and I was executing a series of UPDATEs in SQL Management studio because I didn’t want to waste time writing an admin script to do it. All was going well until I accidentally highlighted half of the UPDATE (without the WHERE clause of course) and  one brief F5 press later I was very surprised  to read that I had “Successfully updated 1.5 million rows”. It was only suppose to be 2.

Uh oh.

It might have been a development environment but I had a couple of months of work invested into this database and copying back from the live environment would not have helped. Like a genius I had also neglected to take any backups of the development environment and the only restore version was from over 2 years ago.

Panic.

 

Always Use Transactions

 

At this point in the story let me give you one piece of advice that you already know but frequently fail to act upon:

 

ALWAYS WRAP YOUR UPDATE AND DELETE STATEMENTS IN A TRANSACTION

 

Transactional syntax is not difficult and it only takes 5 seconds to type “BEGIN TRAN” … “COMMIT” but in doing so you are saving yourself hours of headache when things like this happen. Even if you’re only working in a dev environment like I was, just do it and make it a permanent habit.

Anyway, the reason you’re reading this is because you were not using transactions (if you were then obviously the simple solution is just to execute the ROLLBACK statement to reverse the last transaction). In this case no amount of rollbacks are going to help you and the simplest solution is to restore from a recent backup. If like me you have no recent backup then thankfully, all is not lost.

 

Apex SQL Log

 

If you run a quick search on reversing a sql update or delete then most guides will reference the excellent tool ApexSQL Log. This application can be used to scan your database’s transaction log over a specified period and then “undo” any queries which you find. It’s very powerful and would be a “must have” in any sql developer’s toolbox except…

The price tag. If you are really, really up “shit creek” then it is a thousand bucks well spent but for this project the price tag was just to steep. There is a free/trial version available (and you should download this, we’ll need it in a minute) but it will only show you 1 in 10 of every result found (i.e. if 30 queries were executed then it will show you 3 at random with the other 27 greyed out) which makes it completely useless in practise. So what else can we do?

 

Determining when your DELETE/UPDATE Query was run

 

Whilst Apex SQL Log (free) is useless for undo-ing our query, it is useful for determining exactly when our query was run. You can use the filter options to limit the results to just your query type (Delete/Update) and then also filter for just your user session/client. With that done you should then be able to use a couple of “trial and error” searches to narrow down the date/time range to the nearest second (or even split second) when your fail query was executed which can be very useful if multiple people were working on the database at the same time.

If you already know exactly when your fail query was run (or you don’t mind losing a bit of work) then you can skip this step but for me I had already worked most of the morning on my database and so I wanted to restore back to a specific point in time and pick up my work where I left off. This step is also important to avoid corrupting the state of your database, especially if it is part of a large business system.

 

Restoring from the Transaction Log

 

I should point out at this stage that if you are using the “simple recovery model” for your database then you really are completely out of luck.

I’m really sorry but this model does not use a transaction log and so all updates are written blindly over database with no history of the data or the transactions. I would always recommend that you stick to the full recovery model for any database – mainly because of situations like this or for when you need to recover/view specific historical data. There are performance benefits to using a simple model but unless you are working on some kind of complicated data-warehousing or caching solution (where data is derived and/or temporary) then I cannot think of  many occasions when the pros outweigh the cons.

If you are using a full recovery model then good news, the process is as follows:

1.  Make a Backup

The first step is to make a full backup of your database including the precious logs which we will be using to restore. Open SQL Server Management Studio (or whichever SQL Client you are using) and right click the database and choose “Backup”. Ensure that “FULL” backup is selected and in the advanced options tick to create a “Tail-Log” backup which will ensure that the whole log is included. Note that it is okay to “Truncate the transaction log” and you won’t lose anything important if you select this option.

2.  Create a new recovery Database

The next step is to create a new database that will be used to restore the data. name it something like “MyDatabase_Recovery” or “MyDatabaseNew”. Once you have restored the database and it’s tested working you can replace the old database with this new copy.

3.  Restore from the Transaction Log (before the bad thing happened)

Finally you need to perform a RESTORE LOG operation specifying a time/date prior to the bad query which got you into this mess. There are plenty of decent guides on-line for this so I won’t copy it out again here:

http://www.techrepublic.com/blog/the-enterprise-cloud/restore-your-sql-server-database-using-transaction-logs/ (using sql commands)

or

https://technet.microsoft.com/en-us/library/ms190982(v=sql.105).aspx (using the GUI)

An example recovery query looks something like this:

 

RESTORE LOG MyDatabase_Recovery

FROM DISK = 'D: \BackupFiles\MyDatabase.bak'

WITH STOPAT = N'6/28/2007 4:01:45 PM', RECOVERY

 

where the “STOPAT” argument is used to specify the point in time to restore up to and the “RECOVERY” argument leaves the database in a workable state.

 

And that’s it! Run a query against your new database to check that the data has been restored correctly and then replace the old database with your new one.  Some caveats though:

• As with any SQL Server restore, you will have to ensure that your “ldf” and “mdf” files are restored to the correct DATA folder with the correct name, overwriting the files in your new recovery database. Check out the “MOVE” arguments for the restore command in the examples here (https://msdn.microsoft.com/en-us/library/ms186858.aspx#restoring_db_n_move_files)
• When swapping back your databases ensure that the security model and permissions are recreated in the new database so all of your database user accounts can still access the new database.

 

 

---

I hope this post helps you. If you would like more information or need help recovering your SQL Server database then contact us or leave a comment below