How To Recover Data From Truncated Table

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

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 TableCREATETABLE TruncateTest (ID INT)
INSERTINTO TruncateTest (ID)
SELECT1UNIONALLSELECT2UNIONALLSELECT3
GO
-- Check the data before truncateSELECT*FROM TruncateTest
GO
-- Begin TransactionBEGIN TRAN
-- Truncate TableTRUNCATETABLE TruncateTest
GO
-- Check the data after truncateSELECT*FROM TruncateTest
GO
-- Rollback TransactionROLLBACK TRAN
GO
-- Check the data after RollbackSELECT*FROM TruncateTest
GO
-- Clean upDROPTABLE 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
INNERJOIN sys.partitions partitions ON (allocunits.type IN (1, 3)  
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type =2AND 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/

Solution 4:

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 https://codingfry.blogspot.com/2018/09/how-to-recover-data-from-truncated.html