Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restoring after TRUNCATE TABLE


Restoring after TRUNCATE TABLE

Author
Message
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
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 :-D


Good for you! w00t
Chris Houghton
Chris Houghton
SSChasing Mays
SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)SSChasing Mays (622 reputation)

Group: General Forum Members
Points: 622 Visits: 1664
Easy two points. Thanks.
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
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
paul.knibbs
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1972 Visits: 6211
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
M&M
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 3898
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
M&M
M&M
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 3898
paul.knibbs (3/9/2011)
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!


Were you referring to this statement 'backup log with truncate_only'. Running this would invalidate the log backups and we would need to take a full backup again to overcome this. As per my knowledge, a truncate statement wouldn't invalidate log backups.

M&M
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
mohammed moinudheen (3/9/2011)
paul.knibbs (3/9/2011)
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!


Were you referring to this statement 'backup log with truncate_only'. Running this would invalidate the log backups and we would need to take a full backup again to overcome this. As per my knowledge, a truncate statement wouldn't invalidate log backups.


Apparently there did use to be non-logged operations in SQL Server 7, but TRUNCATE TABLE wasn't one of them. These days there are no non-logged operations as far as I'm aware.

Link: http://support.microsoft.com/kb/272093

Also, see Steve's editorial from yesterday: http://www.sqlservercentral.com/articles/Editorial/72676/
mvelic
mvelic
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 115
Well, I got this wrong but I think I would have worded it differently. If you don't take a tail of the log backup after the TRUNCATE TABLE (or wait for the next log backup at 9:30), then you really can only restore up to 9:15. I think with just a little more description the question could have been more clear.
Jim Sleeman-388184
Jim Sleeman-388184
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 179
Trick Question and answer
SKYBVI
SKYBVI
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1231 Visits: 3239
Good question
i would like to ask, we can restore at 9:15 right?
But not after that?

Regards,
Sushant
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search