You might have listened and read either of following sentence many many times.
“DELETE can be rolled back and TRUNCATE can not be rolled back”.
OR
“DELETE can be rolled back as well as TRUNCATE can be rolled back”.
As soon as above sentence is completed, someone will object it saying either TRUNCATE can be or can not be rolled back. Let us make sure that we understand this today, in simple words without talking about theory in depth.
While database is in full recovery mode, it can rollback any changes done by DELETE using Log files. TRUNCATE can not be rolled back using log files in full recovery mode.
DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.
Let us understand this concept in detail.
In case of DELETE, SQL Server removes all the rows from table and records them in Log file in case it is needed to rollback in future. Due to that reason it is slow.
In case of TRUNCATE, SQL Server deallocates the data files in the table and records deallocation of the data files in the log files. If deallocated data files are overwritten by other data it can be recovered using rollback. There is no guarantee of the rollback in case of TRUNCATE. However, while using T-SQL following code demonstrates that TRUNCATE can be rolled back for that particular session.
First create test table which some data. Afterwards run following T-SQL code in Query Editor and test the effect of TRUNCATE on created test table.
BEGIN TRAN
TRUNCATE TABLE TestTable
-- Following SELECT will return TestTable empty
SELECT *
FROM TestTable
-- Following SELECT will return TestTable with original data
ROLLBACK
SELECT *
FROM TestTable
Summary : DELETE can be recovered from log file always if full recovery mode is set for database. TRUNCATE may or may not be recovered always from log files.
Reference : Pinal Dave (https://blog.sqlauthority.com)
36 Comments. Leave new
i have table with the data like below:
ids_cloumn
========================
1 , 2 , 4 , 23 , 12 , 11 , 9 , 8 , 7 , 10 ,
1 , 3 , 4 , 21 , 16 , 12 , 19 , 10 ,
2 , 3 , 6 , 24 , 15 , 18 , 9 , 8 ,
3 , 5 , 8 , 27 , 14 , 19 , 39 , 7 , 13 ,
1 , 7 , 9 , 28 , 19 , 11 , 29 , 8 ,
i am having a list of nos with me (coming from another query)
ex1: 1, 3 & 23
ex2 : 1, 6, 34 & 23
now i want to retrieve the list of rows which is having any of the nos.
is there any other way with out using “LIKE %”
If i am using like i need to write so many OR conditions based on the inputs.
thanks
The ping back doesn’t seem to work. Anyway I don’t think a commited delete statement can be rolled back either. The deleted data can be restored from backup only.
Hi Pinal,
Thanks for replying. I feel confuse when you mention roll back using log file together with the “Roll Back” statement. Roll back statement only rolls back the current open transaction in current database session. It can not roll back a committed transaction.
Roll back using log file however will roll back many transactions. we can not roll back a specific delete that way. Everthing done after the delete will also get rolled back. I got the impression from your post that we can roll back a specific delete statement using log file.
As you point out roll back using log file is to restore a database to a point of time
I forgot to mention that I think point in time restore using log file can also roll back “TRUNCATE”.
Pinal,
Sorry for being stubborn. But as far as I know in a point in time restore. A full database backup has to be be restored first and then the log backup is actually used to “Roll Forward” the database to the point in time when the data deleted or turncated were still exist. So it would not be a problem whether or not the data file got over written because it will be re-created in database restore anyway.
Maybe I miss unstand something but if you can share an example of using log file to “Roll Back” a commited DELETE then it can make things clear. I believe it would be something a lot of DBAs like know about
Appreciated
Andrew
I’m completely agree with Andrew. Truncate table not just safe for rollback in transaction scope. It still possible undo truncation of table using restore log and stopat.
There just reproduction code:
— STEP 1. Creating DB
SET NOCOUNT ON
CREATE DATABASE TEST
GO
ALTER DATABASE TEST SET RECOVERY FULL
USE TEST
GO
— STEP 2. Creating table with some data (100 rows)
CREATE TABLE data (f1 INT IDENTITY)
GO
INSERT data DEFAULT VALUES
GO 100
— STEP 3. Performing full backup
BACKUP DATABASE TEST TO DISK=’c:test_full.bak’ WITH INIT
GO
— STEP 4. Performing log backup
DECLARE @date DATETIME
— variant1: Date mark just before truncation
SET @date = DATEADD(s, 0, GETDATE())
— variant2: Date mark after truncation
— SET @date = DATEADD(s, 1, GETDATE())
TRUNCATE TABLE data
WAITFOR DELAY ’00:00:03′
BACKUP LOG TEST TO DISK=’c:test_log.bak’ WITH INIT
— STEP 5. Restoring Test DB.
USE MASTER
RESTORE DATABASE TEST FROM DISK=’c:test_full.bak’ WITH NORECOVERY, REPLACE
RESTORE LOG TEST FROM DISK=’c:test_log.bak’ WITH RECOVERY, STOPAT=@date
GO
USE TEST
GO
select COUNT(*) AS CNT FROM data
go
USE MASTER
DROP DATABASE TEST
p.s. Please, delete my previous comment :))
Acutally there are third party transaction Log reading Tools that are able to read transaction logs and allow you undone commited statements or transactions. Tuncate satement may or may not be undone using such tools. SQL server itselft does not provide this capability.
Siusic Dot Com
Thank you!
I’m understanding now. So, To make a rollback over an alter data. I need to restore the last full backup and next the differential and log backup before to make the change?
I have any other way to rollback the sentence update?
dear pinal ,
when i take full backup on during that time some one delete table row .how can i recovery that in sql 2005
@Raj,
Take transactional backup, Then try to restore database using Stop at function, give the time when you think the deleted record was available, and database will be restored till that time, and the record will be available. This process will create a new database. Be careful dont over write the old database.
Hope this helps,
Imran.
Hi Folks,
I have a different observation on this rolling back / recovering the data lost by truncate. The example provided by Alexander is perfect. But here the data truncated is not exactly recovered by restoring the log file to a point before truncate. We are taking a full backup after inserting 100 records, and then truncating the table. So restoring the full backup took care of retrieving all records not the transaction log.
I have a different case to prove that the data lost due to a TRUNCATE can be recovered, but not 100%. As storage engine only records the deallocation of pages in log, and releases the pages and its corresponding extent for allocation there is definite possibility for data loss. The percentage of data loss depends on how soon we take a log backup and the new requests for page allocation. My simple guess is the transaction log backup we are taking itself can grab those deallocated pages of TRUNCATE and cause data loss. To prove this I have made little changes to Alexander’s code as below. I have added a INSERT statement (highlighted in ‘==’) after taking full backup. These inserts are logged in only transaction log, not backedup during full backup.
— STEP 1. Creating DB
SET NOCOUNT ON
CREATE DATABASE TEST
GO
ALTER DATABASE TEST SET RECOVERY FULL
USE TEST
GO
— STEP 2. Creating table with some data (100 rows)
CREATE TABLE data (f1 INT IDENTITY)
GO
INSERT data DEFAULT VALUES
GO 100
— STEP 3. Performing full backup
BACKUP DATABASE TEST TO DISK=’c:\test_full.bak’ WITH INIT
GO
–The above full backup will guarantee the first hundred recovery.
–===================
INSERT data DEFAULT VALUES
GO 100
–====================
— STEP 4. Performing log backup
DECLARE @date DATETIME
— variant1: Date mark just before truncation
SET @date = DATEADD(s, 0, GETDATE())
— variant2: Date mark after truncation
–SET @date = DATEADD(s, 1, GETDATE())
TRUNCATE TABLE data
WAITFOR DELAY ’00:00:03′
–This log backup now has all the 100 records inserted after full backup
–making total count to 200 records in the table
BACKUP LOG TEST TO DISK=’c:\test_log.bak’ WITH INIT
— STEP 5. Restoring Test DB.
USE MASTER
RESTORE DATABASE TEST FROM DISK=’c:\test_full.bak’ WITH NORECOVERY, REPLACE
RESTORE LOG TEST FROM DISK=’c:\test_log.bak’ WITH RECOVERY, STOPAT=@date
GO
USE TEST
GO
–This will return count less than 200
select COUNT(*) AS CNT FROM data
go
USE MASTER
DROP DATABASE TEST
This test gives us a different count every time which confirms a data loss. I am glad to see this forum and will appreciate your feed back.
Truncate can be very much rolled back when it is used in a transaction.
–select * from emp
begin transaction
truncate table emp
–rollback transaction
above is the code snap short please try and let me know.
Hey Guys,
I’m not sure if modification I have done are correct or not but I’m able recover all the rows brefore the truncate statement. I just made the wait time little longer…
–STEP 1. Creating DB
SET NOCOUNT ON
CREATE DATABASE TEST
GO
ALTER DATABASE TEST SET RECOVERY FULL
USE TEST
GO
–STEP 2. Creating table with some data (100 rows)
CREATE TABLE data (f1 INT IDENTITY)
GO
INSERT data DEFAULT VALUES
GO 100
select COUNT(*) AS CNT,’1st 100′ AS Remark FROM data
–STEP 3. Performing full backup
BACKUP DATABASE TEST TO DISK=’D:\test_full.bak’ WITH INIT
GO
–The above full backup will guarantee the first hundred recovery.
–===================
INSERT data DEFAULT VALUES
GO 100
select COUNT(*)AS CNT,’2nd 100 AFTER full backup’ AS Remark FROM data
–====================
–STEP 4. Performing log backup
DECLARE @date DATETIME
–variant1: Date mark just before truncation
SET @date = DATEADD(s, 0, GETDATE())
select @date as TimeOfRB
–variant2: Date mark after truncation
–SET @date = DATEADD(s, 1, GETDATE())
TRUNCATE TABLE data
WAITFOR DELAY ’00:00:10′
select COUNT(*) AS CNT,’trun’ AS Remark FROM data
–This log backup now has all the 100 records inserted after full backup
–making total count to 200 records in the table
BACKUP LOG TEST TO DISK=’D:\test_log.bak’ WITH INIT
–STEP 5. Restoring Test DB.
USE MASTER
RESTORE DATABASE TEST FROM DISK=’D:\test_full.bak’ WITH NORECOVERY, REPLACE
RESTORE LOG TEST FROM DISK=’D:\test_log.bak’ WITH RECOVERY, STOPAT=@date
GO
USE TEST
GO
–This will return count less than 200
select COUNT(*)AS CNT ,’last’ AS Remark FROM data
go
USE MASTER
DROP DATABASE TEST
I read abt Truncate command is
The TRUNCATE command Drop The Table and And Recreate it.
Hi
I have tested the pinal dave post.. Truncate can be rollback whether the database is Full recovery model or simple recovery model…
Still am not clear please explain me. I am using SQL SERVER 2008
thanks
Raj…..-)
Sir
I have one table (Example – table name)
In that table two fileds is there 1) slno – int, 2)name – varchar(50)
I entered the records
slno Name
1 a
2 b
3 c
First I Delete one record using Delete Command
delete from example where slno =’1′
after that
truncate the table using truncate command
truncate table example
Now
I roll back record of 1
how can i?
i try this but i can’t? Please help me
it was nice thanks dear
SELECT GETDATE() fn_GetDate, SYSDATETIME() fn_SysDateTime
This SYSDATETIME() is not get a proper value and sql server 2005 show error message.
suppose, i have one table with four columns(name,id,city,salary)
in name column,i’l have raj,ravi,ashok,poonam,pooja,etc and i want to add ‘s with every name. So,what can i do.
Hi
Pinaldave
I am using Sql Server 2008
accedently data delete from table but I have no Transactional Backup and my database in full recovery .
I have Full Backup of last 1 Month .so plz suggest me how to recover data from table …plz it is very urgent ….