SQL Server Performance

emergency rollback

Discussion in 'SQL Server 2005 General Developer Questions' started by Phthisis, Feb 12, 2007.

  1. Phthisis New Member

    Hello all, i just got myself into a big problem. How do i rollback an update transaction that i just sent? Here was the code i used to update:

    update address set pcode = left(pcode,3) + ' ' + right(left(pcode,6),3)

    i stupidly left out the where clause. I was using microsoft sql server management studio that comes with .net 2005 to write the query.

    help is much appreciated
  2. ndinakar Member

    do you have any timestamp column in the table? or even a recent back up? or has the db been recently refreshed in another environment (like Qa/DEV) from where you can get the data for this table?

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  3. madhuottapalam New Member

    do u have Snapshot backup of the database. If yes it is easy? if not, if the database is Full Recovery model use backup restore using STOPAT method to create a new database
    Madhu
  4. Phthisis New Member

    luckily i averted a crisis. I luckily just made a backup just before the query. I restored that database and all is good.

    I am still wondering though is there tsql transaction code i can wrap an update statement that will give me the option to commit based on rows affected or other variables?
  5. MohammedU New Member

    You can use explicit transaction...

    BEGIN TRAN
    update...
    IF @@ROWCOUNT > <Value>
    ROLLBACK TRAN
    ELSE COMMIT TRAN

    Note: You can create database snapshot before big update like this...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  6. Phthisis New Member

    thank you very much Mohammedu, i will use the transaction code you outlined.

    As for the database snapshot, i am unfamiliar with what that is. Ill have to look it up. Is that something you do from enterprise manager like a backup?
  7. MohammedU New Member

  8. keithhowes New Member

    quote:Originally posted by MohammedU

    You can use explicit transaction...

    BEGIN TRAN
    update...
    IF @@ROWCOUNT > <Value>
    ROLLBACK TRAN
    ELSE COMMIT TRAN

    Note: You can create database snapshot before big update like this...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    You would think that this code would be more commonly known. I too learned how to use this bit of code the hard way. There's nothing worse than that feeling after you've just pressed F5 and realized you left off the where clause....
  9. ndinakar Member

    I do it in a similar way..Usually for Updates and Deletes I run the SELECT with the same WHERE condition first to get the number of rows that *should have been* affected. Then I do a:


    BEGIN TRAIN

    UPDATE...
    -- COMMIT TRAN
    -- ROLLBACK TRAN

    I have the COMMIT and ROLLBACK commented. If the UPDATE affects the same rows as expected I execute the COMMIT else ROLLBACK appropriately.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  10. Phthisis New Member

    quote:Originally posted by ndinakar

    I do it in a similar way..Usually for Updates and Deletes I run the SELECT with the same WHERE condition first to get the number of rows that *should have been* affected. Then I do a:


    BEGIN TRAIN

    UPDATE...
    -- COMMIT TRAN
    -- ROLLBACK TRAN

    I have the COMMIT and ROLLBACK commented. If the UPDATE affects the same rows as expected I execute the COMMIT else ROLLBACK appropriately.


    so after you have begun the transaction which includes the update how exactly do you execute the commit/rollback? Do you comment out begin tran and the update and only execute "commit tran"? eg:

    --BEGIN TRAIN

    --UPDATE...
    COMMIT TRAN
    -- ROLLBACK TRAN

    or do you run the entire transaction again this time with commit or rollback uncommented?

    BEGIN TRAIN

    UPDATE...
    COMMIT TRAN
    -- ROLLBACK TRAN

    Thank you for all of the great feedback!

    PS. yes, i am working with sql server 2000 using the 2005 query analyzer.
  11. ndinakar Member

    I should have mentioned, when I have to do any update or delete manually through QA I use the approach I mentioned above. If its in a proc the only way is to check for @@ERROR. The only way to validate the UPDATE/DELETE us by doing a SELECT prior to the UPDATE/DELETE, get the @@Rowcount and compare. Then do a COMMIT or ROLLBACK.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  12. MohammedU New Member

    When ever I do manual delete/update the data, I copy the data to different table...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page