Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Restoring after TRUNCATE TABLE Expand / Collapse
Author
Message
Posted Wednesday, March 9, 2011 2:56 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
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!
Post #1075399
Posted Wednesday, March 9, 2011 2:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 558, Visits: 1,495
Easy two points. Thanks.
Post #1075402
Posted Wednesday, March 9, 2011 3:05 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
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
Post #1075405
Posted Wednesday, March 9, 2011 3:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:32 AM
Points: 1,639, Visits: 5,721
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!
Post #1075412
Posted Wednesday, March 9, 2011 3:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
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.


Mohammed Moinudheen
Post #1075421
Posted Wednesday, March 9, 2011 3:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
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.


Mohammed Moinudheen
Post #1075422
Posted Wednesday, March 9, 2011 5:31 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
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/
Post #1075459
Posted Wednesday, March 9, 2011 5:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 109, Visits: 103
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.
Post #1075474
Posted Wednesday, March 9, 2011 6:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 18, 2013 9:05 AM
Points: 100, Visits: 179
Trick Question and answer
Post #1075481
Posted Wednesday, March 9, 2011 6:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 6:14 AM
Points: 1,200, Visits: 3,237
Good question
i would like to ask, we can restore at 9:15 right?
But not after that?

Regards,
Sushant
Post #1075483
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse