CODEWITHSUNDEEP
sql-serversql-server-2008rollback
Marthin
Marthin

Reputation: 6543

How to rollback SQL update command?

In SQL server 2008 R2. Is it possible to do a rollback on a single update command?

I know there are other questiones like this on SO but i havent seen one specific for 2008 R2 and hence I may get the same answer, if that is the case then we can close this thread.

I did the a simple update without any transactions commands:

UPDATE myTable SET col1=somevalue WHERE....

Upvotes: 4

Views: 20246

Answers (2)

Kenneth Hampton
Kenneth Hampton

Reputation: 705

If your database is in full recovery mode you might want to try reading transaction log, finding which rows were affected and then reverting the update.

However, this is not supported by default, because MS stored transaction log in its own format that is not well documented.

Solution is to use commands such as DBCC LOG or fn_log or third party tool such as ApexSQL Log which does all of this automatically but comes with a price.

If you need more details, here are couple of posts on reading transaction log:

Read the log file (*.LDF) in sql server 2008

SQL Server Transaction Log Explorer/Analyzer

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453067

Of course you can use explicit transactions such as

BEGIN TRAN
UPDATE ...
ROLLBACK

but I don't think you are asking about that?

If you have the option SET IMPLICIT_TRANSACTIONS ON then the command will not be committed or rolled back until you do so explicitly but this is not the default behaviour.

By default transactions are auto committed so when the command finishes successfully the results of the update will be committed. If the update was to encounter an error - including the connection being killed mid update it would auto rollback.

Upvotes: 6

Related Questions