Restoring after TRUNCATE TABLE

  • Comments posted to this topic are about the item Restoring after TRUNCATE TABLE

  • Not sure I get the point of the trick question.

    It should be clear that you can restore to ANY point in time (at each 15 minute interval in this example). However, restoring to the 9:30 state will not recover your TRUNCATEd table ... so was that thrown in as a red herring ?

    If so, it got me ...

  • I learned something form this question., Nice question.

    -----------------
    Gobikannan

  • Ol'SureHand (3/8/2011)


    Not sure I get the point of the trick question.

    It should be clear that you can restore to ANY point in time (at each 15 minute interval in this example). However, restoring to the 9:30 state will not recover your TRUNCATEd table ... so was that thrown in as a red herring ?

    If so, it got me ...

    The question asked: Is it now possible to restore to a given point in time?

    It is possible, so the answer is certainly yes. The question didn't ask if it was possible to restore that specific table to any given point in time.

    Anyway, nice question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Ol'SureHand (3/8/2011)


    Not sure I get the point of the trick question.

    It should be clear that you can restore to ANY point in time (at each 15 minute interval in this example). However, restoring to the 9:30 state will not recover your TRUNCATEd table ... so was that thrown in as a red herring ?

    If so, it got me ...

    Hi - I think you may have overthought things a bit - it wasn't meant to be a complicated or trick question, but just to find out if people thought that TRUNCATE TABLE somehow affected the validity of the transaction log. It doesn't.

    Thanks for the feedback though, maybe I should have made the question clearer somehow.

    Duncan

  • Gobikannan (3/8/2011)


    I learned something form this question., Nice question.

    Thanks, glad you liked it.

    Duncan

  • Koen Verbeeck (3/8/2011)


    Ol'SureHand (3/8/2011)


    Not sure I get the point of the trick question.

    It should be clear that you can restore to ANY point in time (at each 15 minute interval in this example). However, restoring to the 9:30 state will not recover your TRUNCATEd table ... so was that thrown in as a red herring ?

    If so, it got me ...

    The question asked: Is it now possible to restore to a given point in time?

    It is possible, so the answer is certainly yes. The question didn't ask if it was possible to restore that specific table to any given point in time.

    Anyway, nice question.

    Thanks for the feedback. You read the question as I meant it to be read, so I hope you got it right!

    Cheers,

    Duncan

  • Thanks for the question Duncan. I had a feeling that this question was more at testing our understanding of the truncate statement than the recovery models.

    M&M

  • Two points for this?

    Great!!!

  • Duncan Pryde (3/9/2011)


    Thanks for the feedback. You read the question as I meant it to be read, so I hope you got it right!

    I did 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (3/9/2011)


    Duncan Pryde (3/9/2011)


    Thanks for the feedback. You read the question as I meant it to be read, so I hope you got it right!

    I did 😀

    Good for you! :w00t:

  • Easy two points. Thanks.

  • mohammed moinudheen (3/9/2011)


    Thanks for the question Duncan. I had a feeling that this question was more at testing our understanding of the truncate statement than the recovery models.

    It was sort of a bit of both. A misconception appears to be that TRUNCATE TABLE is non-logged or minimally logged. Minimally logged operations occur in BULK_LOGGED (and SIMPLE) recovery and prevent point-in-time restores for affected log backups. TRUNCATE TABLE, even though it behaves similarly to minimally logged operations, is not one and doesn't prevent point-in-time restore - even in BULK_LOGGED recovery.

    A good article by Kalen Delaney that explains the difference is here

    Duncan

  • This is odd. I'm sure I've read stuff that implies TRUNCATE TABLE *does* invalidate log backups until the next full backup is run, which means none of the suggested answers was right--yet everyone is saying this is incorrect and that point-in-time restore is still perfectly possible after doing one of these. I'm confused!

  • Duncan Pryde (3/9/2011)


    mohammed moinudheen (3/9/2011)


    Thanks for the question Duncan. I had a feeling that this question was more at testing our understanding of the truncate statement than the recovery models.

    It was sort of a bit of both. A misconception appears to be that TRUNCATE TABLE is non-logged or minimally logged. Minimally logged operations occur in BULK_LOGGED (and SIMPLE) recovery and prevent point-in-time restores for affected log backups. TRUNCATE TABLE, even though it behaves similarly to minimally logged operations, is not one and doesn't prevent point-in-time restore - even in BULK_LOGGED recovery.

    A good article by Kalen Delaney that explains the difference is here

    Duncan

    Thanks Duncan for sharing the link.

    M&M

Viewing 15 posts - 1 through 15 (of 54 total)

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