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