none
How can we rollback mistakenly UPDATE statement change in SQL Database.?

    Question

  • Hello all,

    Let's say we have a table customer and If someone tries to update a single row within it but by mistakenly update the entire row within a customer table. while update query someone forgot to put where condition and now all the rows of customer table are updated.

    Is there any solution to rollback the update statement and retrieve all rows back for that customer table.?

    After research ,I have found as the only option is to restore that previously backup Database . If our database runs in full recovery mode, we can:

    * backup the transaction log

    * restore the database from a full backup

    * restore all transaction logs (in sequence) to the time right before the accident occurred.

    Also, there is some cool tool called ApexSQL-Log which allows us to rollback the changes. But I haven't tried it yet before but I have downloaded the product and going for a test. You can easily download from the link below:

    http://www.apexsql.com/sql_tools_log.aspx

    So if anyone knows some idea regarding on this and if there is any solution lately with the release of MS-SQL 2012  then really want to here.

    Regards,

    Anil Maharjan

    Friday, June 29, 2012 8:15 AM

Answers

  • You can rollback your transactions through below options, which I have come across so far,

    1 - Restore the database backup(Full,Diff & Tran) till you performed update operation

    2 - Restore the dabase in some other server and update the values in current db looking at restored database records.

    3- If you have not truncated your log backup and db is under full recovery model, then you can use tools to read those transactions old and new value. Then take a decission to update/revert those transactions.

    Friday, June 29, 2012 8:33 AM
  • What is the context of your question?

    • It seems clear that this is a SSMS question pertaining to programmers, since hopefully, end users don't have the ability to run their own UPDATE statements.
    • Has this error happened already and you're trying to recover from it?  (If so, then unfortunately the damage is done, that update transaction got committed the moment the person erroneously updated all rows.  Restore from backup is your answer, but keep reading)
    • Or are you asking if there is a way to prevent or safeguard against this in the future? (Definitely, yes.  Use BEGIN TRAN/ROLLBACK TRAN/COMMIT TRAN)

    So, after you get done reading about BEGIN/ROLLBACK/COMMIT transactions, here is another way to prevent all this from happening:

    • Method 1: Within the context of a Proc, procedure, etc; Learn enough T-SQL to handle via code, such as Try/Catch, etc.
    • Method 2: Within the context of a developer having to do an ad-hoc data fix (which is the focus of what I say below), here's how I do any/all updates, inserts, deletes with a nice safety net:

    /* BEGIN TRAN -- ROLLBACK TRAN -- COMMIT TRAN */
    /* Example for ad-hoc updates; pre-test with Select, then Update, then manually ROLLBACK or COMMIT based on results */ Select -- Begin Tran; Update TABLENAME Set Data = Data * 2 from TableName where CustomerID = 1234;

    SELECT 'Tran Info' as _X_, @@rowcount as Rows, @@trancount as TrnCnt, @@Identity as Id3ntity

    1. I carefully make sure I have my query just right, by highlighting just the SELECT statement and running (via F5 or the Run icon) and confirming that the SELECT pre-testing step is returning the data I intend to update shortly.
    2. Only when I know my where clause is right: I highlight starting with "begin tran" on line 3, through the end.  Notice that this strategy, I'm physically highlighting and utilizing the exact same WHERE clause I tested carefully in step 1.
    3. I look closely at my output, and the follow-up query showing me @@Trancount and @@Rowcount.  The number of rows updated will hopefully match the number of rows I saw while testing the SELECT statement.
    4. I can even re-run the SELECT statement before deciding to commit. or rollback, but it has to be in the same window:  (Remember, you have a TRANSACTION in progress! 
    5. And once I know all is correct, I highlight just the COMMIT TRAN text in line 1 and run it.  (Or, if it was wrong, I highlight just the ROLLBACK TRAN text, and run that). 
    6. It's VERY IMPORTANT to remember to either COMMIT or ROLLBACK; This wouldn't be a good time to go to lunch while forgetting the transaction open!  :-)
    7. Subtle tip:  it's intentional that I put the the word WHERE is on a separate line to the rest of the where criteria:  This  is a safeguard:  If I were to absent-mindedly NOT highlight the WHERE clause criteria, I get a syntax error with this approach, rather than accidentally updating the entire table if I forgot the entire WHERE clause plus criteria.

    Obviously, this takes a few extra steps, but the careful testing via Select pays off: I haven't accidentally deleted all data in a table or updated all rows inadvertently...   lately... ;-)    (Similar strategy can be used for Deletes (also with pre-testing via select), and for Inserts (but pre-testing not applicable for inserts).  

    I'm always in absolute shock when I see people running ad-hoc UPDATE and DELETE statements without a safety net of some sort, whether via a carefully written procedure, or via a carefully managed and monitored manual process like this.  This semi-formal approach forces you to treat the task of running updates with more reverence: it's a big deal, if you mess up an ad-hoc update, you want to be sure you're protected!





    Friday, June 29, 2012 11:59 AM

All replies

  • Hello all,

    Let's say we have a table customer and If someone tries to update a single row within it but by mistakenly update the entire row within a customer table. while update query someone forgot to put where condition and now all the rows of customer table are updated.

    Is there any solution to rollback the update statement and retrieve all rows back for that customer table.?

    After research ,I have found as the only option is to restore that previously backup Database . If our database runs in full recovery mode, we can:

    * backup the transaction log

    * restore the database from a full backup

    * restore all transaction logs (in sequence) to the time right before the accident occurred.

    Also, there is some cool tool called ApexSQL-Log which allows us to rollback the changes. But I haven't tried it yet before but I have downloaded the product and going for a test. You can easily download from the link below:

    http://www.apexsql.com/sql_tools_log.aspx

    So if anyone knows some idea regarding on this and if there is any solution lately with the release of MS-SQL 2012  then really want to here.

    Regards,

    Anil Maharjan


    • Edited by Anil Maharjan Friday, June 29, 2012 8:14 AM link added
    • Changed type Naomi NModerator Friday, June 29, 2012 6:02 PM Question rather than discussion
    • Merged by Naomi NModerator Friday, June 29, 2012 7:21 PM Same topic
    Friday, June 29, 2012 7:53 AM
  • Hello Anil,

    You can start a transaction, do your update, check the result and then you can decide to eighter commit the transaction (persist changes) or to rollback the transaction.
    See http://msdn.microsoft.com/en-us/library/ms188929.aspx as a first start.

    Otherwise the only option is to restore the data from a backup, as you already wrote.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Friday, June 29, 2012 8:25 AM
  • The only available solution at SQL server is to restore last successful backups :- 

    • backup the transaction log
    • restore the database from a full backup
    • restore last differential backup
    • restore all transaction logs (in sequence)
    • restore t-log backup with stopat option to mention the time right before the accident occurred

    -Rohit 

    Friday, June 29, 2012 8:25 AM
  • This is the case that you need to consider before it actually happens. If you haven't done that, then the backup restoring and and restoring transaction logs to point before update statements were executed. That tool might help you, but I don't know I haven't try it.

    You can build solutions to prevent this kind of an things, but of course those won't help you afterwards.

    Friday, June 29, 2012 8:28 AM
  • use a stored procedure to handle your update by adding a parameter if you want to update it with a where clause.

    Thanks and regards, Rishabh K


    • Edited by Rishabh K Friday, June 29, 2012 8:32 AM
    Friday, June 29, 2012 8:31 AM
  • You can rollback your transactions through below options, which I have come across so far,

    1 - Restore the database backup(Full,Diff & Tran) till you performed update operation

    2 - Restore the dabase in some other server and update the values in current db looking at restored database records.

    3- If you have not truncated your log backup and db is under full recovery model, then you can use tools to read those transactions old and new value. Then take a decission to update/revert those transactions.

    Friday, June 29, 2012 8:33 AM
  • What is the context of your question?

    • It seems clear that this is a SSMS question pertaining to programmers, since hopefully, end users don't have the ability to run their own UPDATE statements.
    • Has this error happened already and you're trying to recover from it?  (If so, then unfortunately the damage is done, that update transaction got committed the moment the person erroneously updated all rows.  Restore from backup is your answer, but keep reading)
    • Or are you asking if there is a way to prevent or safeguard against this in the future? (Definitely, yes.  Use BEGIN TRAN/ROLLBACK TRAN/COMMIT TRAN)

    So, after you get done reading about BEGIN/ROLLBACK/COMMIT transactions, here is another way to prevent all this from happening:

    • Method 1: Within the context of a Proc, procedure, etc; Learn enough T-SQL to handle via code, such as Try/Catch, etc.
    • Method 2: Within the context of a developer having to do an ad-hoc data fix (which is the focus of what I say below), here's how I do any/all updates, inserts, deletes with a nice safety net:

    /* BEGIN TRAN -- ROLLBACK TRAN -- COMMIT TRAN */
    /* Example for ad-hoc updates; pre-test with Select, then Update, then manually ROLLBACK or COMMIT based on results */ Select -- Begin Tran; Update TABLENAME Set Data = Data * 2 from TableName where CustomerID = 1234;

    SELECT 'Tran Info' as _X_, @@rowcount as Rows, @@trancount as TrnCnt, @@Identity as Id3ntity

    1. I carefully make sure I have my query just right, by highlighting just the SELECT statement and running (via F5 or the Run icon) and confirming that the SELECT pre-testing step is returning the data I intend to update shortly.
    2. Only when I know my where clause is right: I highlight starting with "begin tran" on line 3, through the end.  Notice that this strategy, I'm physically highlighting and utilizing the exact same WHERE clause I tested carefully in step 1.
    3. I look closely at my output, and the follow-up query showing me @@Trancount and @@Rowcount.  The number of rows updated will hopefully match the number of rows I saw while testing the SELECT statement.
    4. I can even re-run the SELECT statement before deciding to commit. or rollback, but it has to be in the same window:  (Remember, you have a TRANSACTION in progress! 
    5. And once I know all is correct, I highlight just the COMMIT TRAN text in line 1 and run it.  (Or, if it was wrong, I highlight just the ROLLBACK TRAN text, and run that). 
    6. It's VERY IMPORTANT to remember to either COMMIT or ROLLBACK; This wouldn't be a good time to go to lunch while forgetting the transaction open!  :-)
    7. Subtle tip:  it's intentional that I put the the word WHERE is on a separate line to the rest of the where criteria:  This  is a safeguard:  If I were to absent-mindedly NOT highlight the WHERE clause criteria, I get a syntax error with this approach, rather than accidentally updating the entire table if I forgot the entire WHERE clause plus criteria.

    Obviously, this takes a few extra steps, but the careful testing via Select pays off: I haven't accidentally deleted all data in a table or updated all rows inadvertently...   lately... ;-)    (Similar strategy can be used for Deletes (also with pre-testing via select), and for Inserts (but pre-testing not applicable for inserts).  

    I'm always in absolute shock when I see people running ad-hoc UPDATE and DELETE statements without a safety net of some sort, whether via a carefully written procedure, or via a carefully managed and monitored manual process like this.  This semi-formal approach forces you to treat the task of running updates with more reverence: it's a big deal, if you mess up an ad-hoc update, you want to be sure you're protected!





    Friday, June 29, 2012 11:59 AM
  • There is no other way in SQL 2012 either. If the bad UPDATE has been run, the less expansive solution will be to restore the most recent backup into some temp DB name and attempt to restore only this single Customer table (first change DB access to single user so no more changes will be done to the data, Truncate the customer table, insert from the restored customer table in that temp DB). I assume that Customer table is not too big, less than 500K rows.

    Actually, most likely you will not be able to truncate that table - you first need to disable all constraints and triggers.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Friday, June 29, 2012 6:05 PM
    Moderator
  • I suggest that you don't just respond with a knee-jerk response to this problem.  Make sure whatever you do that you think it through thorougly.  What you don't want to do is to make your situation much worse by implementing a second change this is not thought all the way through.
    Friday, June 29, 2012 6:11 PM
    Moderator