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. Here is a Rollback TRUNCATE command demo you can run yourself.
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
GOWell, 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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
69 Comments. Leave new
Truncate *IS* a logged operation, it just doesn’t log removing the records, it logs the page deallocations. How do you think it can be rolled-back if it isn’t logged? Rollback is *purely* driven by what’s in the transaction log.
Based on your statement, “TRUNCATE is DDL operation and it is not logged in log file.” How can you rollback a truncate? It has to be logged. Reference Paul Randal posts and many others.
Thank you Paul,
I meant the same that truncate does not log removing record.
Thanks to your comment. I have reflected your comment in my blog post.
It is more clear now.
Kind Regards,
Pinal
Thanks Pinal,
I did not know this. Very simple and easy example.
Can we rollback particular row truncated from any table??
Mr. Pinal can you brief more on Truncate and Delete??
Warm Regards,
Bhavank
Hi Pinal,
As per you post, Truncated record can be rollback if we define transaction at begining and we can recover the truncated record after rollback it. But can not recover truncated records from the transaction log backup after commited truncated transaction.
Am i right?
Very correct Paresh.
Hi Pinal,
I have tried with follwing
1.
create a database
create table
insert records
Backup full of the database – Fullbackup1
2.
backup log the same database – Trnbackup1
3.
Apply truncate statement with and without transaction.
I have tried with both.
4.
Restore database full backup Fullbackup1
apply trnsaction log backup Trnbackup1
Can you tell me what happen here?
I am able to recovred the truncated records saucessfully after applying all the above.
Can you brief me in which scenario we can not recover truncated data?.
Can you send me your original code in email please at pinal ‘at’ sqlauthority.com ?
Additionally, we stay in same city, let us meet up some time.
Hello Pinal,
Following is sql code which you requested. I am ready to meet whenever you say.
1. — create a database
create database truncatetest
go
alter database truncatetest set recovery full
use truncatetest
go
2. — Creating table and Inserting records
create table tbl_truncatetest
(id int)
insert into tbl_truncatetest
select 1
union all
select 2
union all
select 3
select * from tbl_truncatetest
3. — Taking full backup and log backup
backup database truncatetest to disk = ‘e:truncatetest_data.bak’
backup log truncatetest to disk = ‘e:truncatetest_log.trn’
4. — Apply truncate statement on table
begin tran
truncate table tbl_truncatetest
commit tran
5. — Restoring full and log backup
restore filelistonly from disk = ‘e:truncatetest_data.bak’
restore database truncatetest_1 from disk = ‘e:truncatetest_data.bak’
with replace ,
move ‘truncatetest’ to ‘E:SQL_2005_DBtruncatetest_1.mdf’,
move ‘truncatetest_log’ to ‘E:SQL_2005_DBtruncatetest_1_log.LDF’,
norecovery
restore log truncatetest_1
from disk = ‘e:truncatetest_log.trn’
with recovery
5. — Viewing the data in table
use truncatetest_1
go
select * from tbl_truncatetest
— and the data is recover
6. — drop databases
drop database truncatetest
drop database truncatetest_1
HI Pinal
I want to count column name
eg.
suppose we have a table employee with id,name,no then it will give output 3
suppose we have student table with id,name,rollno,class then it will give 4
how can we count only column names from table defination.
Tejas.
Tejas – You can query the INFORMATION_SCHEMA.COLUMNS table for your requirement.
Nice post with examples. This clears my doubts as well. In some interview the person asked me the same with ALTER TABLE in stead of TRUNCATE in transaction, i.e.
BEGIN TRAN
UPDATE TESTME
SET COL2 = 30 WHERE COL1 = 3
ALTER TABLE TESTME
ADD COL3 VARCHAR(100)
ROLLBACK TRAN
SELECT * FROM TESTME
and he said the updates will be commited in this case. I also heard that in Oracle the updates will be committed in this case. Is it?
Thanks,
Prashant
No. Updates will not be commited
Everything will be rollback no matter whether it is update,delete or ddl statements
For Oracle, :
Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement.
So, it cannot be ROLLed BACK
So, only option to undo is to again execute a DDL statement?
Then I dont see any point in allowing them inside a transaction
Thanks Brian, this clears my concept…
@Paresh,
This is quite common/usual that when you have taken backup of the database before issuing truncate and later on if you restore database then definitely you will get all the records back.
So whats new in this. could you please highlight. as i am doing this since past four year and use to suggest my team member to take backup before any such kind of fatal command so that later on we can restore it.
Also we have a backup server where we use to take backup regularly.
Therefore I raised this point that it is quite common practice that once you have taken backup of database later on after restoring the same will give you all records back.
@Pinal,
I was not aware of that whether the truncate command being the part of transaction can be rolled-back. as I had not got any opportunity to try this in my career.
So thanks for the tip.
thanks for telling us, no one is as clear as you. You are good with images.
Paresh,
In your case, you have taken the log backup before the truncate operation. When you restore the log with original backup it will still show you the data.
If you take your backup of log after truncate operation, and you restore you will not able to see the data.
In fact, the same thing will happen in case of DELETE without where condition.
However, Truncate de-allocates the pages which stores the data and you will be not able to do point in time recovery. In case of delete if you have deleted data (I am assuming in chunks/parts) you will be able to recover the data using point in time recovery.
Hello Pinal,
If i take the log backup after the truncate the table and then restore the log then obviously i can not able to see the data because it was truncated , even i take log bakcup after delete the table data and then restore it and can not able to see the data, too.
In both of the Truncate and delete i can not able to see the data. In this case truncate and delete both are working same.
So, In which scenario and exectly what is the difference between truncate and delete in case of the recovery?.
Can you please give me the case when we can not recover the data for truncate and can recover the data for delete for the transaction log backup.
Thank you.
Paresh,
You can do point of time recovery if you have used delete statement. If you have used truncate you will be not able to do that. Also you will be not able to explore log using third party tools.
Kind Regards,
Pinal
What I mean by Point of Time Recovery with Delete is that you can move forward with time such that you have needed data available, whereas in case of TRUNCATE it happens all at once, so you will not have the data available.
Do send me email and we can discuss this further if you are still confused.
Hi Pinal,
I have sent you email regarding our need of backup and strategic maintenance planning.
It is for our client.
Roshni,
I have not received your email yet. I tried to send email to your address but it does not go through.
Excellent, You explained it very nice. I got it right away.
This is the reason, why we come to visit your blog.
This is Joe Again,
my office mate says you should change your domain name to SimpleSQL.com or SolidSQL.com
The current site already got popular
There is no point in changing it
Hi Pinal Sir,
Its Marvelllous ,Nodoubt You are the best SQL Player or challenger.
Yes you are rit sarika
Nice article, a very healthy discussion.
Thank you
Hi Pinal,
Thanks for nice article. Since it is DDL and logging page allocations / deallocations.
Can we run this in Database Mirroring without invalidating the mirroring? Most of the DDL operations can be mirrored from 2005 onwards…So it should be? Kindly explain its effect on Replication/Logshipping as well. Thanks!!!
Hi Neeraj,
In Log shipping the log backup are restored, thus the truncate command will certainly be reflected on the secondary with out any issues.
In Replication and Mirroring transaction are applied on the secondary server, thus the truncate command will be reflected.
hi all
I want to delete the row from the sql server table.
When i try to delete by using the following query
delete from person where personID = 9008210
it shows like 1 row affected
but it is not deleting from table
select * from table where personID = 9008210
it displaying the data from the table
I don’t know Why it is not deleting permanently from the table
I try to delete manually from the table also
but same problem occurs repetaedly
Can any help me regarding this issue
I appreciate it
thank u
Hello Usha,
Are you using SET IMPLICIT_TRANSACTIONS ON in your session?
Regards,
Pinal Dave
hello pinal,
delete can be rolled back where as truncate can not be rolled
back in sqlserver 2005.
Begin transaction
truncate table sample
rollback transaction
Above staements are executed i got the data of sample table.
can u explain clearly?
regards
kalyan
Hi Kalyan,
That is exactly I have explained in this blog post.
Kind Regards,
Pinal
Can we rollback particular row truncated from any table??
Regards
Azad
By rollbaking truncate, the table gets all data in whcih you can find the required row