ROLLBACK TRUNCATE in SQL Server

“DELETE can be rolled back… everyone knows that.

But what about TRUNCATE?

Is TRUNCATE permanent?
Or can we actually ROLLBACK a TRUNCATE in SQL Server?

In this demo we will see either we can ROLLBACK the Truncated table.

“YES, we can rollback TRUNCATE — but only inside a transaction otherwise we can’t rollback this”.

Let’s see the demo

Creating a table and inserting few records.

create table TruncateTest ( ID int identity(1,1) , Nm varchar(20), mob varchar(10) )  --inserting few records insert into TruncateTest (Nm,mob) values ('Bagesh','888880XXXX') insert into TruncateTest (Nm,mob) values ('Ramesh','888880XXXY') insert into TruncateTest (Nm,mob) values ('Ganesh','888880XXXZ') insert into TruncateTest (Nm,mob) values ('Mahesh','888880XXXA') insert into TruncateTest (Nm,mob) values ('Mohan' ,'888880XXXB')

See the data in this table

Now we will see in case of truncate

Table has been truncated. The table is empty. ROLLBACK does nothing. Because without BEGIN TRAN, TRUNCATE is auto-committed. We can’t rollback this. We can’t recover the data.

Now we are using transaction and truncating the data.

BEGIN TRAN; truncate table TruncateTest SELECT * FROM TruncateTest;  -- returns 0 rows

Now we are rollbacking the transaction.

ROLLBACK;   SELECT * FROM TruncateTest;  -- data is back

“TRUNCATE can be rolled back when used inside an explicit transaction.”