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
sknox
sknox
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 2711
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]
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
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.
Ken Wymore
Ken Wymore
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 2342
Over-thought this one and missed it. I should have gone with my gut.... Doh!
alen teplitsky
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
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
sknox
sknox
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 2711
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
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
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.)
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1062
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!
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
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!
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
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
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