ROLLBACK Transaction

  • Gurus,

    In a session i have run the UPDATE statement..but accidentely i pressed EXECUTE..

    Steps:

    1. update statement without WHERE clause.

    2. Click on 'EXECUTE'.

    So is there any way that i retrieve the old data for the table...

    Please help,

    NOTE: I have not done any DECLARE for transaction.

    Regards,

    ~Sri

  • It looks to me your transaction got commited.

    How about point-in-time recovery?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Do you have any backups taken? If so, you can restore it to a different database and copy the table to the database which lost the data.

  • yes,my transaction got committed...i guess i need to perform recovery.

  • sridhar.tondapi (2/5/2009)


    yes,my transaction got committed...i guess i need to perform recovery.

    Then, Restore it to a different database name and copy the table to the target database.

  • And next time use a BEGIN TRAN so you can roll back if needed.

    FYI, when making changes to data like this, I usually take a few precautions.

    1. Back up the table first by creating a copy with a different name, such as:

    select * into Table_20090205

    from Table

    2. Run a select to see how many records will get updated and Review the data to make sure it looks like the right records:

    select * FROM Table_20090205

    WHERE ColumnA Is Null

    3. I would run the update in a transaction so you can undo it right then if you make a mistake:

    begin tran

    UPDATE T SET ColumnA = 'NewValue'

    FROM Table T

    WHERE ColumnA Is Null

    rollback

    commit

    If you get the correct count, then: COMMIT

    If you think it's wrong and need to undo, then : ROLLBACK

    If you accidentally run the script all at once, having the rollback in there will undo it

    4. When you're all finished, save the whole script somewhere in case you need to look back sometime.

    Record the number of rcds changed in the script

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply