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.

Lacagnia

I know it doesn't help now, but for next time: use a BEGIN TRANSACTION statement before doing an update. Then if it is wrong or produces undesired results, you can use ROLLBACK TRANSACTION and it goes away :) ( And if it is good to go, COMMIT TRANSACTION or else you lock the database for all users, hehe ) – Acrodrome

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).

Ladonnalady

If the recovery model is full, does not SQL server maintain the transaction log with all the changes? – Ciro

@zespri if the recovery model is full, and the log hasn't been backed up, and you know how to identify the precise transaction that this update affected, and you know how to read the before image of those rows and build reversing updates from them (and still have to determine if your correction should over-write any updates that have happened since). In other words, a lot of ifs. I've updated my answer to be more explicit. – Ladonnalady

Yep, thank you, that was my understanding too =) Just checking! – Ciro

DML stands for Data Manipulation Language for those that would have had to look this up like I did! https://mcmap.net/q/73678/-what-are-ddl-and-dml – Hf

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!

Acrodrome

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

Belia

Answer 4

As a slightly modified version to the answers above, I sometimes like to use an automatically rolled back transaction in combination with the OUTPUT keyword and the INSERTED internal table to see what will actually update as a result set.

For instance,

BEGIN TRANSACTION;

UPDATE TableA
SET TableA.Column1 = @SomeValue
OUTPUT INSERTED.*
WHERE <condition>

ROLLBACK TRANSACTION;

If the result set looks good to me, then I'll change the last statement to COMMIT TRANSACTION;.

The one caveat to keep in mind with this approach is that if you are attempting an INSERT, you will still burn through primary key values even after a ROLLBACK TRANSACTION. So beware of your .ldf files from growing too large.

Cryptoclastic

Answer 5

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

Lindsylindy

Answer 6

Y'all forgetting about taking individual table backups?

select * into table_bak_20240815 from table

easy peasy

then just restore the OG table from the backup!

Misplay

Answer 7

Possible but It will require lot of efforts.

SQL Server maintains logs for DELETED/UPDATED/INSERTED data in non-readable format and to read them you should have the efficient tool Event Log Analyzer.

Logwood