CODEWITHSUNDEEP
sql-serversql-server-2008truncate
Dr. Rajesh Rolen
Dr. Rajesh Rolen

Reputation: 14285

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 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

Upvotes: 10

Views: 48943

Answers (4)

Abhay Prince
Abhay Prince

Reputation: 2162

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

Upvotes: 0

Nath_Math
Nath_Math

Reputation: 259

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:

Upvotes: 5

user1059637
user1059637

Reputation: 712

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/

Upvotes: 4

Pankaj Agarwal
Pankaj Agarwal

Reputation: 11309

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

Upvotes: 6

Related Questions