How to roll back UPDATE statement?

Question

Is this possible without restoring whole database?

I have made changes which I would like to undo, but without putting DB offline, and doing full restore.

Answer 1

No, SQL Server does not have Ctrl + Z.

You protect yourself from this scenario by wrapping all DML statements in a transaction. So you have query windows with this:

BEGIN TRANSACTION;
UDPATE ...

-- COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

When you run the update, verify that you updated the right number of rows, the right rows, the right way, etc. And then highlight either the commit or the rollback, depending on whether you performed the update correctly.

On the flip side, be careful with this, as it can mess you up the other way - begin a transaction, forget to commit or rollback, then go out for lunch, leave for the day, go on vacation, etc.

Unfortunately that will only help you going forward. In your current scenario, your easiest path is going to be to restore a copy of the database, and harvest the data from that copy (you don't need to completely over-write the current database to restore the data affected by this update).

Answer 2

The short answer is: No.

However, you don't have to take the DB offline to do a partial restore on a table or tables.

You can restore a backup to a separate database and then use TSQL queries to restore the rows that were negatively impacted by your update. This can take place while the main database is online.

More info on restoring a database to a new location: http://technet.microsoft.com/en-us/library/ms186390.aspx

---

For future reference, as per my comment,

It is a good practice to use a TRANSACTION.

-- Execute a transaction statement before doing an update. 
BEGIN TRANSACTION 
... < your update code >

Then if the update is wrong or produces undesired results, you can ROLLBACK the TRANSACTION

-- Ooops I screwed up! Let's rollback!
--ROLLBACK TRANSACTION -- I have this commented out and then just select the command when needed. This helps to not accidentally rollback if you just press CTRL+E, (or F5 in SSMS 2012)

... and it goes away :)

When all is well you just COMMIT the TRANSACTION.

-- COMMIT TRANSACTION -- commented out, see above

Or else you lock the database for all users! So don't forget to commit!

Answer 3

Yes, besides doing a full restore, there is a viable solution provided by 3rd party tool, which reads information from a database transaction log, parse it, and then creates an undo T-SQL script in order to rollback user actions

Check out the How to recover SQL Server data from accidental updates without backups online article for more information. The article is focused on the UPDATE operation, but with appropriate settings and filters, you can rollback any other database change that's recorded within the transaction log

Disclaimer: I work as a Product Support Engineer at ApexSQL

Answer 4

It is not possible unless you version your data appropriately or do a restore.