How to Recover Deleted/truncated and Dropped Data ,objects in SQL Server Using Transaction Log and LSNs

This article is the most important part for DBAs and developers.

Sometimes DBAs and developers delete, truncate and drop data objects from the database. So I decided to recover data and objects using the transaction log and LSNs.

This is not as challenging a task to recover the data. However, you may not be aware of the date and the time when the exact data was deleted/truncated and the dropped data objects from the database. I recommend you read my previous tip how to Track Database Transaction Log in SQL Server 2012. This tip will help you to understand the Transaction Log.

Let's create a small Test Environment using the Transaction Log and LSNs using the undocumented function "fn_dblog”.

I assume the database is in full recovery mode. If you want to recover your data and objects I suggest you first use the following procedure on the test environment.

Now I will follow the test environment.

Step 1

Now I will create a database and a table that I will create delete and drop test for as in the following:

--Create Test DB.
USE [master];
GO
CREATE DATABASE RecoverDB;
GO
USE RecoverDB;
GO
--Create a table
CREATE TABLE Employee
(
Emp_ID INT IDENTITY(1, 1) ,
Emp_Name VARCHAR(55) ,
Emp_Technology VARCHAR(55) ,
Emp_Salary DECIMAL(10, 2) ,
Emp_Designation VARCHAR(20) )
GO
--Create another table
CREATE TABLE Personal_Detail
(
Emp_ID INT,
FName VARCHAR(55) ,
DOB VARCHAR(55) ,
[Address] VARCHAR(60) ,
Mobile INT ,
[State] VARCHAR(60) )
GO

Step 2

Now I will take a full backup of the preceding database.

Note: If you want to use an existing database then there is no need to take a new full backup for recovery, you just need the last created full backup of the database.

-- Create Backup of 'RecoverDB' database.
USE [Master]
GO
BACKUP DATABASE RecoverDB
TO DISK = 'D:\backup\RecoverDB.BAK'
GO

Step 3

I have created a database named RecoverDB and a table named Employee and Personal_Detail. Now I will insert a few records in the table.

Note: For an existing database there is no need to do this step.

SET NOCOUNT ON;
GO
USE RecoverDB;
GO
--Now Insert data to table Employee
INSERT INTO Employee
VALUES ( 'Sumit', 'SQL', 12000, 'SE' )
, ( 'Ravi', 'ASP.NET', 15000, 'TL' )
, ( 'Ajay', 'C#', 14000, 'SE' )
, ( 'vikas', 'JAVA', 22000, 'SSE' )
, ( 'deepak', 'VB', 18000, 'TH' )
-- Now Insert data to Personal_Detail
INSERT INTO Personal_Detail
VALUES ( 1, 'V.Agarwal', '28-12-1986', 'Noida', 965287985, 'UP' )
, ( 2, 'S.S.kumar', '02-06-1986', 'delhi', 96548200, 'Delhi' )
, ( 3, 'Garav.rai', '30-03-1987', 'Noida', 99922535, 'UP' )
, ( 4, 'A.R.Patak', '19-12-1986', 'Rampur', 808563131, 'UP' )
, ( 5, 'R.shah', '25-09-1985', 'Delhi', 954651423, 'Delhi' )
GO

Step 4

Now I will delete some rows from the table named Employee and truncate data from the table Personal_Detail.

-- delete data from Employee table.
USE RecoverDB
GO
DELETE FROM Employee
WHERE Emp_ID <3
GO
-- truncate data from Personal_Detail table.
TRUNCATE TABLE Personal_Detail
GO

Step 5

Now I will find the deleted and truncated rows using the Transaction log using the undocumented function "fn_dblog”. Run the code below to get information about all the deleted transactions.

SELECT
[Current LSN],
Operation,
[Transaction ID],
[Begin Time],
[Transaction Name],
[Transaction SID],
AllocUnitName
FROM
fn_dblog (NULL, NULL)

Step 6

Now I will take the deleted and truncated Transaction IDs like: Deleted-’0000:000004ea’,

Truncated: '0000:000004eb'. You can see that in the preceding screen and Find the LSN .

--For Delete
SELECT
[Current LSN],
Operation,
[Transaction ID],
[Begin Time],
[Transaction Name],
[Transaction SID],
AllocUnitName
FROM
fn_dblog (NULL, NULL)
WHERE
[Transaction ID] = '0000:000004ea'
AND
[Operation] = 'LOP_BEGIN_XACT'
GO
-- for truncate
SELECT
[Current LSN],
Operation,
[Transaction ID],
[Begin Time],
[Transaction Name],
[Transaction SID],
AllocUnitName
FROM
fn_dblog (NULL, NULL)
WHERE
[Transaction ID] = '0000:000004eb'
AND
[Operation] = 'LOP_BEGIN_XACT'

Step 7

Now I will drop the tables named Employee and Personal_Detail.

USE RecoverDB
GO
DROP TABLE Employee
USE RecoverDB
GO
DROP TABLE Personal_Detail
GO

Step 8

Now I will find the dropped tables LSN using the Transaction log using the undocumented function "fn_dblog”. Run the code below to get the information about all the dropped transactions.

--drop tables Name Employee and Personal_Detail.
SELECT
[Current LSN],
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
[Begin Time],
[SPID],
Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO

Role of Log Sequence number(LSN)

Log Sequence Number (LSN) has uniquely identified each record in the SQL Server transaction log. The LSN of a log record at which a significant event occurred can be useful for constructing the correct restore sequences. LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. For more details Overview to Log Sequence Numbers.

Step 9

You can see in the receding LSN for Delete and Truncate. To recover data I will be using the STOPBEFOREMARK operation. The LSN value is in hexadecimal format. If I use the STOPBEFOREMARK operation then I need to change this LSN value into decimal format to make a single number.

You can see in the receding screen the LSN value for deleted is 00000035:000000b1:0001; this value is in three parts.

I can be divide them as: part A (00000035), B (000000b1), C (0001). Now there is a separate process to convert the LSN numbers. You can covert them using Hex to Decimal Converter.

Or use a SQL Server command like

SELECT CAST (CONVERT (VARBINARY,'0x'+'Values', 1) AS INT)

For example, for a deleted LSN: 00000035:000000b1:0001.

Using Hex to Decimal Converter

You can get the same as in the above using Hex.

Using SQL Server

--For Deleted table Employee
SELECT CAST (CONVERT (VARBINARY,'0x'+'00000035', 1) AS INT) as A,
CAST (CONVERT (VARBINARY,'0x'+'000000b1', 1) AS INT) as B,
CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as C
GO
--For Truncated table Persona_Detail
SELECT CAST (CONVERT (VARBINARY,'0x'+'00000035', 1) AS INT) as A,
CAST (CONVERT (VARBINARY,'0x'+'000000b3', 1) AS INT) as B,
CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as C
GO
--For Dropped table Employee
SELECT CAST (CONVERT (VARBINARY,'0x'+'00000035', 1) AS INT) as A,
CAST (CONVERT (VARBINARY,'0x'+'000000b9', 1) AS INT) as B,
CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as C
GO
--For Dropped table Persona_Detail
SELECT CAST (CONVERT (VARBINARY,'0x'+'00000035', 1) AS INT) as A,
CAST (CONVERT (VARBINARY,'0x'+'000000ca', 1) AS INT) as B,
CAST (CONVERT (VARBINARY,'0x'+'0001', 1) AS INT)as C
GO

Let's start with part A. I will convert this value to decimal with no leading zeros, that will be 53. Now convert part B to a 10-character decimal number, including leading zeroes, that will be 0000000117. Now convert part C to a 5-character decimal number, including leading zeros, that will be 00001.

Now we have a decimal value of part A, part B and part C. Put them together as one single number like this:

Deleted: 53000000011700001
Truncated: 53000000017900001
Drop table Employee: 53000000018500001
Drop table Personal_Detail: 53000000020200001

Step 8: Now I will take a Transaction Log Backup using the following script:

--Create Log Backup
USE RecoverDB
GO
BACKUP LOG RecoverDB TO DISK = 'D:\backup\RecoverDB.TRN'
GO

Step 9

Now I will restore a copy of the RecoverDB database and restore a Log backup with the STOPBEFOREMARK option to recover the exact LSN.

--Restoring Full backup with norecovery.
RESTORE DATABASE RecoverDB_copy
FROM DISK = 'D:\backup\RecoverDB.bak'
WITH
MOVE 'RecoverDB' TO 'D:\backup\RecoverDB.mdf',
MOVE 'RecoverDB_log' TO 'D:\backup\RecoverDB_log.ldf',
REPLACE, NORECOVERY;
GO
Example 1
--Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
RESTORE LOG RecoverDB_copy
FROM
DISK = N'D:\backup\RecoverDB.trn'
WITH
STOPBEFOREMARK = 'lsn:53000000011700001'

Now you can get the deleted data in RecoverDB_copy.

You can follow the same process as in the preceding from STEP 9 that I followed in the first example. This way we can recover the truncated and drop data objects from your database.

You can see in the screen above that I have recovered the deleted data from the Employee table using the Transaction Log and LSNs.

Summary

This article described how to recover deleted, truncated and dropped data objects using the transaction log and LSNs in SQL Server.

6 Comments

Join the conversation! Your thoughts help the community grow.

Raveendrabj4y ago
Good help for me!
DEEPAK GUPTA11y ago
Hello Shivom We have tried to recover the deleted data in the same way as you documented but my copy database moves in restoring mode because we got the error "The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point." and now I am not able to see the deleted record in "fn_dblog" function. Please help us why so?
Shivom Agarwal11y ago
thanks sandeep
sandeep nair11y ago
Good article..so detailed. Keep up the good work.
Anupam Singh12y ago
An excellent article Shivom. Thanx for sharing .
- Shivom Agarwal12y ago
  Thanx Anupam