Problem
My last tip was about how to find a user who ran a DROP or DELETE statement on your SQL Server objects. Now we will learn how to recover deleted SQL Server data with the help of the transaction log and the respective LSN numbers. This tip will give you step by step process to achieve this solution.
Solution
I have already discussed reading a SQL Server Transaction log and finding a user who has deleted or dropped SQL Server objects in my last two tips. I recommend reading my previous tips to understand how the transaction log is responsible for logging all database activities. In this tip, we will use the same undocumented function “fn_dblog” to find any unauthorized data deletion and recover the deleted data. For our examples, I assume the database is in full recovery mode. I also strongly suggest testing any undocumented code in a lab environment prior to production when the need arises.
If you know when your data was deleted, then it is not as challenging a task to recover the data. However, if you are not aware of the date and time when a particular data was deleted, then we will find out in a similar way as we determined who deleted data from the database in my last tip. Here we will first find the exact LSN under which the delete statement ran and then we will recover data until that LSN.
As per SQL Server Books Online “Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). The LSN of a log record at which a significant event occurred can be useful for constructing 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. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.” ( Introduction to Log Sequence Numbers)
NOTE:-DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRNOMENTS
Restoring deleted SQL Server data with the help of Log Sequence Number (LSN)
Step 1: Before moving ahead, we will create a database and a table on which I will run a DELETE statement and then we will find the deleted data and recover it. Run the T-SQL code below to create a database and table.
--Create DB.
USE [master];
GO
CREATE DATABASE ReadingDBLog;
GO
-- Create tables.
USE ReadingDBLog;
GO
CREATE TABLE [Location] (
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT 'Bangalore');
Step 2: We have created a database named “ReadingDBLog” and a table ‘Location’ with three columns. Now we will insert a few rows in the table.
USE ReadingDBLog
go
INSERT INTO Location DEFAULT VALUES ;
GO 100
Step 3: Now we will go ahead and delete some rows to recover them with the help of LSNs in the SQL Server Transaction Log.
USE ReadingDBLog
Go
DELETE Location
WHERE [Sr.No] < 10
go
select * from Location
You can see in above screenshot that rows less than 10 have been deleted from the table “Location”.
Step 4: Now we have to search the SQL Server Transaction Log to get the information about the deleted rows. Run the code below to get information about all deleted transactions.
use ReadingDBLog
go
SELECT
[Current LSN],
[Transaction ID],
Operation,
Context,
AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE
Operation = 'LOP_DELETE_ROWS'
We can see transaction IDs under which DELETE statement was ran in above screenshot. As we are aware that our data is deleted from the Location table, we will concentrate on only those transaction IDs under which the data is deleted from the location table. We can find the table name from “AllocUnitName” column. It says a DELETE statement has been performed on a HEAP table ‘dbo.Location’ under transaction ID 0000:0000055e. As there is only one transaction ID under which all DELETED rows are showing that means this action has been performed in a single batch. Now capture the transaction ID from here for our next command.
Step 5: We found the transaction ID from above screenshot/script which we will use in the below statement to get the LSN of the LOP_BEGIN_XACT operation. That means we need the exact LSN when the DELETE statement began its transaction.
USE ReadingDBLog
go
SELECT
[Current LSN],
Operation,
[Transaction ID],
[Begin Time],
[Transaction Name],
[Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = '0000:0000055e'
AND
[Operation] = 'LOP_BEGIN_XACT'
Here, we can see the LSN of this transaction. We can read above output like ” A DELETE statement was started at 2014/01/15 17:54:38:347 with LSN 00000016:000001a5:0001 under transaction ID 0000:0000055e”. Now our next step is to change the hexadecimal value of this LSN to decimal in order to recover our data.
Step 6: We will be using the STOPBEFOREMARK operation to recover our data. We cannot use this hexadecimal value to run the STOPBEFOREMARK operation so we need to change this value into decimal format and then put it together as a single number. I used this online Hex to Decimal Converter to change this LSN to decimal.
The LSN we have from the above screenshot is 00000016:000001a5:0001. Here we can see there are three parts. We can divide them as part A (00000016), part B (000001a5) and part C (0001). Now there is a separate process to convert the numbers. Let’s start with part A. We will convert this value to decimal with no leading zero which will be 22. Now convert part B to a 10-character decimal number, including leading zeroes, which will be 0000000421. Now convert part C to a 5-character decimal number, including leading zero, which will be 00001.
Now we have decimal value of part A, part B and part C. Put them together as one single number like this: 22000000042100001.
Step 7: Now run a transaction log backup on the database where the data was deleted if a transaction log backup has not ran since the data deletion. Next, we will restore this database to somewhere else or on same server with different name until the above LSN and then we will import the deleted data from newly restored database to your production database.
--Restoring Full backup with norecovery.
RESTORE DATABASE ReadingDBLog_COPY
FROM DISK = 'C:\ReadingDBLog_FULL_15JAN2014.bak'
WITH
MOVE 'ReadingDBlog' TO 'C:\ReadingDBLog.mdf',
MOVE 'ReadingDBlog_log' TO 'C:\ReadingDBLog_log.ldf',
REPLACE, NORECOVERY;
GO
--Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
RESTORE LOG ReadingDBLog_COPY
FROM
DISK = N'C:\ReadingDBlog_tlogbackup_15thJan610.trn'
WITH
STOPBEFOREMARK = 'lsn:22000000042100001'
Step 8: Now your database is restored and you can check whether your deleted data is recovered or not. Run the below SELECT statement to see the first ten rows of the location table which was deleted.
USE ReadingDBLog_COPY
GO
SELECT * from Location
Now you can see all deleted rows have been recovered and showing in above screenshot. Now you can use this deleted data as per your requirement. If you want to export this data to your live database you can use the SQL Server Integration Services import and export wizard to do that.
Recovery of SQL Server table if its dropped
Step 1: We have demonstrated how to recover deleted data, now what if someone has dropped the whole table? Now I will show you how to recover the entire table. Now consider same example and location table, which I will drop in this example. Go ahead and run the below commands to drop the “Location” table.
USE ReadingDBLog
GO
DROP TABLE Location
Step 2: As was the case with our first example, if you drop a table or you perform any transactions in your database, they will get logged in SQL Server Transaction Log which will be accessed by using the fn_dblog function. Run the below script to display entries which have been logged as a DROP statement.
USE ReadingDBLog
GO
SELECT
[Current LSN],
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
[Begin Time],
[SPID],
Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO
Step 3: Now you have the LSN of the begin transaction under which table was dropped. Now you can follow same process from above from STEP: 6 which we have followed in the first example. This way we can recover the unknown deleted data from your database.
Next Steps
- Use the fn_dblog function to do more research into your SQL Server Transaction Log. There is lot of informative data in more than 100 columns from this function. Just keep in mind you need to look into the issue and correlate it with other data.
- Build your knowledge with the SQL Server Database Administration Tips.
