How to recover data from truncated table

Question

while going though sql server interview question in book of mr. shiv prashad koirala. i got to know that, even after using truncate table command the data can be recovered.

please tell me how can we recover data when data is deleted using 'delete' command and how can data be recover if data is deleted using 'truncate' command.

what i know is that when we use delete command to delete records the entry of it is made in log file but i don't know how to recover the data from and as i read that truncate table not enters any log entry in database then how can that also be recovered.

if you can give me any good link to do it practically step by step than that will be great help to me.

i have got sql server 2008.

Thanks

Solution 1

If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

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.

USE tempdb
GO
-- 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

Solution 2

By default none of these two can be reverted but there are special cases when this is possible.

Truncate: when truncate is executed SQL Server doesn’t delete data but only deallocates pages. This means that if you can still read these pages (using query or third party tool) there is a possibility to recover data. However you need to act fast before these pages are overwritten.

Delete: If database is in full recovery mode then all transactions are logged in transaction log. If you can read transaction log you can in theory figure out what were the previous values of all affected rows and then recover data.

Recovery methods:

• One method is using SQL queries similar to the one posted here for truncate or using functions like fn_dblog to read transaction log.

• Another one is to use third party tools such as ApexSQL Log, SQL Log Rescue, ApexSQL Recover or Quest Toad

Solution 3

SQL server keeps the entry (Page # & file #) of the truncated records and those records, you can easily browse from the below query. Once you get the page ID & file ID , you can put it in the DBCC PAGE to retreive the complete record.

SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]
,[Slot ID],[AllocUnitId]
FROM    sys.fn_dblog(NULL, NULL)  
WHERE    
AllocUnitId IN 
(Select [Allocation_unit_id] from sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)  
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 
AND partitions.partition_id = allocunits.container_id)  
Where object_id=object_ID('' + 'dbo.Student' + ''))
AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS') 
AND Description Like '%Deallocated%'

Given below is the link of article that explains , how to recover truncated records from SQl server. http://raresql.com/2012/04/08/how-to-recover-truncated-data-from-sql-server-without-backup/

Comments

[nvogel]

TRUNCATE is logged and can be "rolled back" by restoring from database or log backups in the same way that DELETE can.

[Abhay Prince]

If your database is in full recovery mode you can recover data either by truncated, deleted or dropped Complete Step by Step Article is here code.abhayprince.com/article/…

[OfirD]

@nvogel, is this article an example to such restore, or did you have something else in mind?

[variable]

If TRUNCATEd data can be recovered from database or log backups, then why is this an accepted answer? - this answer reads: "If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file."