How to rollback truncated data in sql server

Truncated data can be rollback if you are using transaction in sql server. If transaction is committed then can not be recovered as its not logged operation. Whenever you truncate any data, sql server deallocate data pages related to the table.

Truncate command comes under DDL operation in sql server so only deallocation of data pages get recorded in logs not data.

Lets understand this with an example, here we will execute truncate statement inside begin transaction and will check whether transaction can be rollback or not.

use tempdb

–> Creating temporary table for demo

create table #truncatedata (Num int)

–> Inserting dummy data using multiple insert option in sql server 2008

insert into #truncatedata


–> Checking data before truncate operation

Select * from #truncatedata

–>Begin of transaction

Begin tran

–> Truncate statement against temporary table

Truncate table #truncatedata

–> Checking data in temp table after truncate operation

Select * from #truncatedata

–> Now rollback the transaction

Rollback tran

–> If you will fire a select query against temp table, can see data is recovered.

Select * from #truncatedata


–>Drop table #truncatedata


Share your comments

Leave a Reply




2 + 6 =