Hi Friends, The Truth is: Truncate Table Command can be rolled back. Actually Truncate Table command is minimally logged, SQL Server logs only the page deallocations in TLog. So, in order to rollback the command, the Truncate Table statement should be a part of an explicit transaction. Just like a Delete Statement. Explanation: The reason is very clear: WAL Protocol, (Write-Ahead Logging Protocol) Until and unless any transaction is either commited or rolledback, checkpoint process will not make the changes permanent on Disk(Data file) and as i said Truncate table statement is a minimally logged operation & it logs only the page de-allocation in the log file. But what if you accidently executed the statement without starting an explicit transaction and you need your data back? In this situation your only option without using any third party tool is to restore your backups and do a point in time recovery. –>Another reason why you should have a good backup strategy in place. To test the same you can follow the below mentioned steps:
Create table employee ( emp_id int primary key, emp_name varchar(20), age int ) --To insert test data in table insert into employee values(1,'Amit',29) insert into employee values(2,'Sarab',27) insert into employee values(3,'Sachin',28) insert into employee values(4,'Amit',30) insert into employee values(5,'Rakesh',33) select * from employee --execute the truncate table command in an explicit transaction begin tran truncate table employee --check the table, the data is gone. select * from employee --Rollback and your data is back. rollback select * from employee
Regards
Sarabpreet Anand
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
