Restoring after TRUNCATE TABLE

Question

Restoring after TRUNCATE TABLE

Duncan Pryde

SSCertifiable

Points: 7956
More actions
March 8, 2011 at 8:53 pm

#236254

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

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

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

Ol'SureHand SSCrazy Points: 2744 More actions · Answer 1

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 ...

Gobikannan SSCrazy Points: 2735 More actions · Answer 2

I learned something form this question., Nice question.

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

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

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 4

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

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 5

Gobikannan (3/8/2011)
I learned something form this question., Nice question.

Thanks, glad you liked it.

Duncan

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 6

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

M&M SSC-Insane Points: 21700 More actions · Answer 7

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

paul s-306273 SSChampion Points: 10727 More actions · Answer 8

paul s-306273

SSChampion

Points: 10727

March 9, 2011 at 2:38 am

#1296374

Two points for this?

Great!!!

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 9

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

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 10

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:

Chris Houghton SSCrazy Points: 2557 More actions · Answer 11

Chris Houghton

SSCrazy

Points: 2557

March 9, 2011 at 2:59 am

#1296380

Easy two points. Thanks.

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 12

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

paul.knibbs SSCoach Points: 15320 More actions · Answer 13

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!

M&M SSC-Insane Points: 21700 More actions · Answer 14

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