Koen Verbeeck (3/9/2011)

---

I did

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.

Duncan Pryde (3/9/2011)

---

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 (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!

mohammed moinudheen (3/9/2011)

---

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.