Rollback

  • Hi there.

    just yesterday i was working on my development sql server 2000. I was running queries from query editor. There was an update statement and i forgot the where clause. It ran but updated the whole table instead of just one row. I know that Sql has auto commit. I had to restore to undo those changes. Upon reading I found out that using transaction statements its possible to rollback. Can anyone help me with...creating a template of

    Begin tran....

    where i could insert my ad-hoc queries and roll back if i need to...else commit

    Thanks in advance...

  • IF @@ROWCOUNT > 10 -- assume that normal query to affect not more than 10 rows

    ROLLBACK

    ELSE

    COMMIT

    _____________
    Code for TallyGenerator

  • In short - at any time between the BEGIN TRAN and the COMMIT.

    So -

    BEGIN TRAN

    (do whatever you need to do)

    (test that it did what you want)

    COMMIT TRAN

    or

    ROLLBACK

    Keep in mind that you will likely be keeping some things locked/unavailable while you're in the transaction, so don't take overly long so as to not disrupt the regular flow of data.

    You should also do some reading on "Isolation levels" so as to determine what stays locked or should stay locked during your transection.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • A log recovery tool such as SQL Log Rescue might be something you'd find useful in situations like this. It's not a replacement for managing transactions but it can be very handy for recovering from accidental modifications.

    http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

Viewing 4 posts - 1 through 3 (of 3 total)

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