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 TestTableSummary : 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 (http://www.SQLAuthority.com)
pinaldave
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 Andrew,
Thanks for comment. I have tested it earlier and delete can be rolled back using log files even though it is committed. You will have to use point of time restore using log files. However, Truncate can not be restored using log files. I tried to convey this message in title of this post.
I appreciate your regular participation in this blog. Your comments are always valuable.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
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”.
Andrew,
Point in time restore using log file can not roll back TRUNCATE if the data file which is deallocate is over-written. That is the difference.
That is why I used last statement in post “TRUNCATE may or may not be recovered always from log files.”
Kind Regards,
Pinal Dave ( http://www.SQLAuthority.com)
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 :))
Hi Alexandr Volok and Andrew,
Both of yours comment makes this article very valuable. Please continue writing your opinion and comments.
Kind Regards,
Pinal Dave ( http://www.SQLAuthority.com )
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
Hello Andrew,
Thank you for posting clarification and as well we are in agreement. I respect your additional research as well writing about this on your blog http://www.siusic.com/wphchen/recover-data-using-transaction-log-144.html
You are always welcome to comment and participate here to help community.
Kind Regards,
Pinal Dave ( http://www.SQLAuthority.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.