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 «««23456»»

Restoring after TRUNCATE TABLE Expand / Collapse
Author
Message
Posted Wednesday, March 9, 2011 8:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 1,293, Visits: 1,645
alen teplitsky (3/9/2011)
if the TRUN CATE TABLE command is logged, why can't you run it on a table that is published for replication?


Essentially, TRUN CATE TABLE simply invalidates all of the pages related to the table in one operation with no row-level checking. Since replication acts on row-level processing, a table-level command like this is not allowed. Same reason you can't do it on tables with foreign keys and it won't fire any triggers. Also the same reason it's generally so much faster than DEL ETE

[Sorry for the extraneous spaces -- stupid IPS can't tell the difference between a comment and an attack]
Post #1075620
Posted Wednesday, March 9, 2011 9:02 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: Thursday, July 17, 2014 4:47 AM
Points: 3,352, Visits: 1,480
Steve Jones - SSC Editor (3/9/2011)
It's clear to me, and if you think there is some implication that you are restoring to some specific point in time, you are reading something into the question that is not there. The question asks simply if you can restore to a point in time, and you can, based on the transaction log backups. The truncate statement is listed to see if you think this affects your log backup/recovery, which it doesn't.

I'm sure you've read something about this on the Internet, but it's not true.


Thanks Steve. That's sums up my intentions when writing the question.
Post #1075633
Posted Wednesday, March 9, 2011 9:10 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: Today @ 6:57 PM
Points: 3,258, Visits: 1,954
Over-thought this one and missed it. I should have gone with my gut.... Doh!
Post #1075639
Posted Wednesday, March 9, 2011 9:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
sknox (3/9/2011)
alen teplitsky (3/9/2011)
if the TRUN CATE TABLE command is logged, why can't you run it on a table that is published for replication?


Essentially, TRUN CATE TABLE simply invalidates all of the pages related to the table in one operation with no row-level checking. Since replication acts on row-level processing, a table-level command like this is not allowed. Same reason you can't do it on tables with foreign keys and it won't fire any triggers. Also the same reason it's generally so much faster than DEL ETE

[Sorry for the extraneous spaces -- stupid IPS can't tell the difference between a comment and an attack]


that's why i think this is a bogus question. technically everything in SQL is logged or whatever because the system has to execute it and it has to know what to execute against. but practically this is not a logged operation.

i clicked the SQL Skills link expecting some cool info on how to recover the lost data, but no such luck


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1075640
Posted Wednesday, March 9, 2011 10:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 1,293, Visits: 1,645
alen teplitsky (3/9/2011)
sknox (3/9/2011)
alen teplitsky (3/9/2011)
if the TRUN CATE TABLE command is logged, why can't you run it on a table that is published for replication?


Essentially, TRUN CATE TABLE simply invalidates all of the pages related to the table in one operation with no row-level checking. Since replication acts on row-level processing, a table-level command like this is not allowed. Same reason you can't do it on tables with foreign keys and it won't fire any triggers. Also the same reason it's generally so much faster than DEL ETE

[Sorry for the extraneous spaces -- stupid IPS can't tell the difference between a comment and an attack]


that's why i think this is a bogus question. technically everything in SQL is logged or whatever because the system has to execute it and it has to know what to execute against. but practically this is not a logged operation.

i clicked the SQL Skills link expecting some cool info on how to recover the lost data, but no such luck


It is a logged operation, just not row-level-logged. All of the information needed to restore is there (i.e, to restore before the truncate, you roll forward the transaction log to the point before the truncate happened.) The question was concerning restoring to any point in time, which is still possible. If it were NOT a logged operation, it would not be possible, because restoring to after the truncate would not include the truncate.

For more information about point-in-time restore, a good starting point is here: http://msdn.microsoft.com/en-us/library/ms190982.aspx
Post #1075686
Posted Wednesday, March 9, 2011 10:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Nice question, thanks!

Though I probably would have asked about it in the BULK LOGGED recovery mode, as that would have made it more likely that a TRUNCATE TABLE might be a bulk operation that would prevent a point-in-time restore. (Within that one log backup.)
Post #1075715
Posted Wednesday, March 9, 2011 11:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 PM
Points: 21,187, Visits: 14,880
This should be an easy two points, but I have encountered far too many people that think there is no recovery possible upon truncating a table. It is very hard to reverse that thinking - even after showing them examples.

Thanks for the question.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1075763
Posted Wednesday, March 9, 2011 2:58 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 1:04 PM
Points: 1,313, Visits: 1,055
Good question. Please don't hesitate to submit more. I speak for the 62% who read the question as you intended and answered correctly. Our knowledge was reinforced. The folks who answered incorrectly but understood the question as worded really learned something today!
Post #1075914
Posted Thursday, March 10, 2011 1:00 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: Thursday, July 17, 2014 4:47 AM
Points: 3,352, Visits: 1,480
wware (3/9/2011)
Good question. Please don't hesitate to submit more. I speak for the 62% who read the question as you intended and answered correctly. Our knowledge was reinforced. The folks who answered incorrectly but understood the question as worded really learned something today!


I couldn't ask for a nicer comment than that. Thanks!
Post #1076040
Posted Thursday, March 10, 2011 3:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:23 PM
Points: 6,131, Visits: 7,170
I answered wrong but it was a good question. I brainfarted like a few others and went after the 'data', not the log... so you could restore anytime up till 9:20 in my mind. Would have helped if I had simply read the question twice, 'neh?

Ah well, thanks.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1076602
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse