How to Recover Data from Truncated, Deleted or Dropped Table in Microsoft SQL Server

Sometimes developers lose their data accidentally/mistakenly either by truncating or deleting or dropping an object from the database. Here I am going to explain how you can recover data from truncated or deleted or dropped table in MS SQL Server step by step.

Here I am creating a test database, you will use these steps on your required database.

CREATE DATABASE MyTestDB 
GO

Let's create a table Student and insert some demo records in it

USE MyTestDB 
GO

CREATE TABLE Student
(
 StudentID BIGINT IDENTITY PRIMARY KEY,
 StudentName VARCHAR(128),
 RollNo VARCHAR(10)
)
GO

INSERT INTO Student(StudentName,RollNo)
VALUES 
('Harry','101')
,('Hermione','102')
,('Ronald','103') 
,('Draco','104')

GO

Let's see the records in the Student Table

SELECT * FROM Student

Ok. Now start working
Take a full backup of the database on the disk

BACKUP DATABASE MyTestDB    
TO DISK = 'D:\MyTestDB.BAK'    
GO

 Now I'm going to Truncate the table

TRUNCATE TABLE Student
GO

-- Now Check if the table has been truncated

SELECT * FROM Student

Now I will recover truncated data of this table, I will do it by using Transaction Log with the help of inbuilt table valued function 'fn_dblog'
We will select only required columns

SELECT  
[Current LSN],      
Operation,  
[Transaction ID],  
[Begin Time],  
[Transaction Name]
FROM  
fn_dblog (NULL, NULL) where [Transaction Name] ='TRUNCATE TABLE'

This query will give us the following result

It is giving only one row for my case, it may give multiple records on the basis of different database operations. You can further refine the query on the basis of approx Begin Time column.
NOTE : Transaction Names  for Delete and Drop Commands are 'DELETE' and 'DROPOBJ' respectively. 
See the first column in the picture [Current LSN]  LSN stands for Log Sequence Number , the LSN uniquely identifies each record in the Transaction Log, these are interanally used in Restore Process.
And I will use it  here to Recover my truncated data.
The LSN value is in Hexadecimal format I have to convert it into Decimal format, why? I'll explain it later in this article.
Now let's have a look at Current LSN   00000028:00000158:0001  as We can see the value is in three parts separated by colons(:), We'll convert the three Hex numbers into decimal.
You can do it either by your calculation or by calculator or simply by Sql query, I'm here using Sql Query

SELECT CAST (CONVERT (VARBINARY,'0x'+'00000028', 1) AS INT) as FirstPart,   
CAST (CONVERT (VARBINARY,'0x'+'00000158', 1) AS INT) as SecondPart,   
CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as ThirdPart    
GO 

And the Output is

Now we have decimal equivalents of the three parts of Hexadecimal LSN, now do the needful
FirstPart       :  40                  [ Take First Part value with no leading Zeroes ]
SecondPart  :  0000000344    [ Convert Second Part value to 10 Character decimal number (include leading zeroes to form 10 character) ]
ThirdPart     :  00001             [ Convert Second Part value to 5 Character decimal number (include leading zeroes to form 5 character) ]
Finally we have Decimal LSN :  40000000034400001
Now take the Transaction Log Backup by following query

BACKUP LOG MyTestDB TO DISK = 'D:\MyTestDB.TRN'  
GO 

Now I will restore a copy of MyTestDB

RESTORE DATABASE MyTestDB_Copy  
    FROM DISK = 'D:\MyTestDB.bak'  
WITH  
    MOVE 'MyTestDB' TO 'D:\MyTestDB.mdf',  
    MOVE 'MyTestDB_log' TO 'D:\MyTestDB_log.ldf',  
    NORECOVERY       
    GO

 Now I'm going to use STOPBEFOREMARK option to recover data, as the name suggests it'll stop writing the transaction log just before the mark i.e. LSN number in this case. Remember I converted Hexadecimal LSN into its Decimal equivalent because STOPBEFOREMARK requires decimal number. Now Restore the Transaction Log

RESTORE LOG MyTestDB_Copy  
FROM  
    DISK = N'D:\MyTestDB.TRN'  
WITH  
    STOPBEFOREMARK = 'LSN:40000000034400001' 

Now test if my data is recovered or not

SELECT * FROM Student

Yes, I got my data back.

Conclusion : Basically what I've done is just creating a copy of the database and write it's transaction log till just before the truncate command. Now I've my tables data in MyTestDB_Copy database I can use it wherever I want.