Geek Answers Handbook

How to undo the SQL update command?

In SQL Server 2008 R2. Can I roll back one update command?

I know there are other questions on SO like this one, but I have not seen one specific for 2008 R2, and therefore, I can get the same answer, if so, then we can close this thread.

I did a simple update without transaction commands:

UPDATE myTable SET col1=somevalue WHERE....
+4
source share
2 answers

Of course, you can use explicit transactions such as

 BEGIN TRAN UPDATE ... ROLLBACK

but I don’t think you are asking about this?

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

By default, transactions are automatically committed, so when the command completes successfully, the update results will be committed. If an error occurs in the update - including the connection that was killed in the middle of the update, it will automatically roll back.

+6
source

If your database is in full recovery mode, you can try to read the transaction log, find which rows were affected, and then return the update.

However, this is not supported by default, since MS stores the transaction log in its own format, which is poorly documented.

The solution is to use commands like DBCC LOG or fn_log or a third-party tool like ApexSQL Log , which does all this automatically, but comes with a price.

If you need more information, here are a few posts about reading a transaction log:

Read the log file (* .LDF) in SQL Server 2008

SQL Server Explorer / Transactional Analyzer

+1
source

More articles:


All Articles