That doesn't really clear it up for me. She talks about rolling back the same transaction that the truncate was done in. There are exclusive locks: "and just like all X locks, they are held until the end of the transaction" . So if you use a fire and forget query in SSMS or whatever to truncate your "not needed" table, or simply think you're happy and commit the transaction then a week later want to restore the db (or a subset of the data that was in the table) what do you do? It isn't clear to me that you aren't then pouched.
as per Kalen's answer to Raj (in the Comments section)...
suppose we have done commit truncate table ..but can we recover that trucate table through transactional logged backup ? as we can do that for deleted rows ...
September 22, 2012 4:08 AM
Kalen Delaney said:
We can use a log backup to recover any changes by just restoring to a time BEFORE the change took place, whether it's a delete, truncate or drop table, it doesn't matter. The restore does not actually undo anything.. it just brings the database back to a point before you removed the data.
So, if you're using FULL/BULK LOGGED and you can replay the backups to the point just before the TRUNCATE took place, you can recover the data - and if I were about to carry out such work on important data (and through change control if Prod) I would take a backup of the TLog just before carrying out the TRUNCATE operation, making sure I took a note of the TLog Backup name, so that if we every need to recover the data we have a record of at what point we would need to recover to.
If you were only using SIMPLE RECOVERY, then, again depending on the importance of the data (and through change control if Prod) either a FULL or DIFF backup just before carrying out the TRUNCATE operation. Same as above, having a record of the backup name should we need to recover the data.
And, of course, we can recover over the original DB, or if that is not possible, then to a suitable secondary SQL Instance and recovery the table data that way and use a number of methods that may be open to us to transfer the data back to the original truncated table.
Edit: for clarity, changed '...making sure I took a note of the TLog name...' to '...making sure I took a note of the TLog Backup name...'