This is a very common concept that truncate cannot be rolled back. Let us learn in today’s blog post that Rollback TRUNCATE is possible. I always hear the conversation between the developer if truncate can be rolled back or not. Let me just say it clearly today – TRUNCATE can be rolled back.
Truncate is indeed a logged operation, it just does not log removing the records but it logs the page deallocations. Whereas DELETE command actually logs every single record it removes from the table, hence it takes a bit longer to execute.
In any case, both of the keywords are logged and can be rolled back.
Following example demonstrates how during the transaction truncate can be rolled back.
The code to simulate the above result is here.
-- Create Test Table CREATE TABLE TruncateTest (ID INT) INSERT INTO TruncateTest (ID) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 GO -- Check the data before truncate SELECT * FROM TruncateTest GO -- Begin Transaction BEGIN TRAN -- Truncate Table TRUNCATE TABLE TruncateTest GO -- Check the data after truncate SELECT * FROM TruncateTest GO -- Rollback Transaction ROLLBACK TRAN GO -- Check the data after Rollback SELECT * FROM TruncateTest GO -- Clean up DROP TABLE TruncateTest GO
Well, I hope you find this example fun and also removes any confusion you may have on this topic. Here are a few related blog posts you should read on the same topic.
SQL SERVER – TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed
I hope from today’s blog post it is clear that rollback truncate command is possible. You can watch my videos on my youtube channel over here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
69 Comments. Leave new
Hi Pinal,
I am Using SQL Server 2005
Suppose we have Table Tutorials_tbl
Create Table Tutorials_tbl (tutorial_ID INT)
We have inserted the 3 values
INSERT INTO dbo.Tutorials_tbl
Select ‘1’, Union All
Select ‘2’,Union All
Select ‘3’,
Select * from Tutorials_tbl
tutorial_ID
1
2
3
Delete From Tutorials_tbl (DELETE command will delete all the data in the table)
Truncate Table Tutorials_tbl (TRANCATE command will delete all the data in the table)
If we had use the TRANCATE or DELETE either of the command will have same effect in the Table Tutorials_tbl (Command will delete all the data in the table)
Select * from Tutorials_tbl
tutorial_ID tutorial_title tutorial_author submission_date
If we had use the TRANCATE or DELETE either of the command by using BEGIN TRANSACTION (Like below)
For TRANCATE
Begin Tran
Truncate Table Tutorials_tbl
Go
For DELETE
Begin Tran
Delete From Tutorials_tbl
Go
We had Recovered the Data by Below command using for both TRANCATE OR for DELETE
Rollback tran
Then what is the difference between them to recover the data? Why we say that Delete can be rolled back if we can’t rollback if we are not using Transaction? Please tell me command to get the data back. Please clear my doubts…
Would be waiting for your Reply.
Thanks
Brijesh
Hello dear, My question is how can i get my deleted or updated data from table in sql server using rollback functionality.
Hi ,
I have question related to Rollback Transaction
My test script as
——————————————————————
–CREATE TABLE ABC
–(
— ID BIGINT IDENTITY(1,1),
— VID BIGINT
–)
BEGIN TRANSACTION
INSERT INTO ABC(VID) VALUES (2)
SELECT * FROM ABC
ROLLBACK TRUNCATE
————————————————————
Now my identity column value doesn’t reset.
While we test T-SQL Script on production , we have to maintain identity column.
How to avoid such corrections or identity creation from rollback ?