Post reply

How to rollback an insert query on sql server ? , please suggest bit high priority .. thanks in advance

  • June 2, 2013 at 8:28 am

    #278126

    How to rollback an insert query on sql server ? , please suggest bit high priority .. thanks in advance

  • June 2, 2013 at 8:50 am

    #1620316

    sasi947 (6/2/2013)

    How to rollback an insert query on sql server ? , please suggest bit high priority .. thanks in advance

    Is it in an explicit transaction? If not, you can't roll it back. You would have to delete the row (and any other data created or modified as a result of the insert if you have triggers).

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • June 2, 2013 at 9:09 am

    #1620322

    Thank you

  • June 2, 2013 at 12:58 pm

    #1620340

    In my opinion you can also do a backup, and then recover to the proper point in time.

  • June 2, 2013 at 4:16 pm

    #1620353

    Yes, you could, but that's like killing a mosquito with a bazooka. Wrapping the statements in question in a transaction would be much less overhead and work.

  • June 2, 2013 at 9:20 pm

    #1620367

    pietlinden (6/2/2013)

    Yes, you could, but that's like killing a mosquito with a bazooka. Wrapping the statements in question in a transaction would be much less overhead and work.

    Heh... that is unless they commit the transaction and the term "rollback" is just wishful thinking on their part.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 2, 2013 at 9:41 pm

    #1620370

    Actually, a point in time restore is probably the best option... I was thinking that the damage had not already been done - but if it has, that's your best option.

    So you'd save the transaction logs from the new stuff (back it up), restore the database, rolling forward to a point in time, and then apply the logs, skipping the mistake?

  • June 2, 2013 at 9:44 pm

    #1620372

    You can't "skip" mistakes using restores of any kind. You can restore up until the point in time where the mistake was made.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 3, 2013 at 3:01 am

    #1620419

    pietlinden (6/2/2013)

    So you'd save the transaction logs from the new stuff (back it up), restore the database, rolling forward to a point in time, and then apply the logs, skipping the mistake?

    Take a log backup

    Restore the latest full, latest diff if it exists, all log backups including the last one you took, all with STOPAT specifying the time just before the insert was run.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 3, 2013 at 5:39 am

    #1620474

    AS long as your db has not got any other activity on it that is required then the restore is fine. If not and other activity since or during is required then best to restore a copy and remove unwanted data based upon a comparison query

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

Viewing 10 posts - 1 through 9 (of 9 total)

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