0

The answer to this question: How to recover data from truncated table - says that truncated data can be rolled back if surrounded by transaction. Otherwise it is lost.

Assuming the database is in full recovery mode, will weekly full backups and hourly log backups, I have tested that - truncated data can be recovered using log backups.

What is the final verdict?

variable
  • 8,262
  • 9
  • 95
  • 215
  • The final verdict is the one it always was: use backups. Recovering anything from the transaction log is a last resort, especially as there may not be anything left to recover, eg if the database is in Simple recovery mode. In the Full mode any operations are retained in the transaction log until it's backed up. – Panagiotis Kanavos Oct 06 '21 at 06:46
  • But truncate doesn't write much to the transaction log so how can tlog be useful to recover entire truncated data? – variable Oct 06 '21 at 07:20
  • If that were the case, transactions wouldn't work either. It *does* write to the transaction log, even if it's only the deallocation operation. When a transaction is rolled back the log information is used to bring the table back to its original state. Outside of a transaction, there are no guarantees apart from backups. In the linked question one answer restores from backup up to the point right before the `TRUNCATE TABLE` operation. Obviously, you need those backups. Others are quite risky - retrieve the page IDs and try to read the pages, hoping they haven't been overwritten – Panagiotis Kanavos Oct 06 '21 at 07:27
  • Inside a transaction, even if the data page is overwritten the log will contain enough information to revert the UPDATE or INSERT operation so the data pages will be in their original stage when `TRUNCATE` itself is reverted. I suspect the server won't rush to reuse deallocated pages before a transaction completes though – Panagiotis Kanavos Oct 06 '21 at 07:30
  • So is the accepted answer on the linked question wrong? – variable Oct 06 '21 at 08:59

0 Answers0