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