Subscribe to
Posts
Comments

Delete Can Not be Rolled Back

December 28th, 2007 by Andrew Chen

I follow Pinal Dave’s blog for a while. It is a blog about SQL Server and it has been giving me

a lot of great information on SQL server. But I didn’t think his comment on Delete and Truncate in his recent post was right. In that post he mentions that “Truncate” can not be rolled back but delete can.

Rolled back is a concept related to transaction. It is to undo everything that has been done so far in the current transaction and close the transaction so that the data is in the exact same state right before the transaction started. When a transaction is already done or closed, that means the changes made in a transaction is already committed, it can not be rolled back any more. A single delete statement itself equals a transaction. Once it’s done, it’s done. It can not be rolled back. The only way to recover the data is to restore your database to a state back in time when the data still exist.


Related Posts:

  • Recover Data Using Transaction Log
  • How to De-dup a Table That Has Records Duplicated on Two or More fields Efficiently
  • MySQL vs SQL Server in Data File Management
  • Hard to Kill Malware: Wintems.exe, Hldrrr.exe and Random Number.exe
  • Upgrade WordPress From 2.12 to 2.50 to Prevent Being Hacked


    • Posted in SQL Server | 3,684 views

    1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
    Loading ... Loading ...

    RSS feed | Trackback URI

    3 Comments

    Comment by Pinal Dave Subscribed to comments via email
    2007-12-28 19:41:37
    MyAvatars 0.2

    Hi Andrew,

    Thanks for comment. I have tested it earlier and delete can be rolled back using log files even though it is committed. You will have to use point of time restore using log files. However, Truncate can not be restored using log files. I tried to convey this message in title of this post.

    I appreciate your regular participation in this blog. Your comments are always valuable.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

    Log in to Reply
    Comment by Andrew Chen Subscribed to comments via email
    2007-12-29 04:26:34
    MyAvatars 0.2

    Hi Pinal,

    Thanks for replying. I feel confuse when you mention roll back using log file together with the “Roll Back” statement. Roll back statement only rolls back the current open transaction in current database session. It can not roll back a committed transaction.

    Roll back using log file however will roll back many transactions. we can not roll back a specific delete that way. Everthing done after the delete will also get rolled back. I got the impression from your post that we can roll back a specific delete statement using log file.

    As you point out roll back using log file is to restore a database to a point of time

    Log in to Reply
     
     
    Comment by Neeru
    2010-12-10 23:26:10
    MyAvatars 0.2

    Hi,

    We can rolled back the delete if there is a condition. like :
    Delete from where id = 1

    but we can’t rolled back delete if there is no condition. like :
    Delete from

    and also we can’t rolled back the truncate .

    I have checked this…..

    if this is wrong please specify it.

    Log in to Reply
     

    Sorry, the comment form is closed at this time.