SQL Server: Use of FN_DBLOG

Use of FN_DBLOG

fn_dblog (Hero of this Post) is an undocumented SQL Server function that reads the active portion of an online transaction log.

Execute the below fn_dblog function to get the details:

Select * FROM sys.fn_dblog(NULL,NULL)

You can filter the result by adding where clause with options like - LOP_SHRINK_NOOP, LOP_INSERT_ROWS, LOP_MODIFY_ROW, LOP_DELETE_ROWS.
LOP_SHRINK_NOOP - This means taken log backup and shrinking the file.
LOP_INSERT_ROWS - Inserted new rows
LOP_MODIFY_ROW - Modified rows
LOP_DELETE_ROWS - Deleted rows

Lets now look into the situation where a user has deleted data and the techniques you can use to identify the user who has deleted data using the fn_dblog function and how you can restore up to the point prior to the data being deleted.

Let see through the example; Lets assume someone has deleted few rows from the table.
To be able to find any record of deleted data within the transaction log can be achieve using undocumented function fn_dblog and pass in two NULL values as shown below:

SELECT [Transaction ID], [Operation],[Context], [AllocUnitName]
FROM sys.fn_dblog(NULL,NULL) WHERE OPERATION = 'LOP_DELETE_ROWS';

Using the Null values means that I am not looking for a specific LSN Start and End point. When using this function a scan of the active log portion is carried out starting at the oldest uncommitted transaction up to the most recent record.
Result as below:



Using "Transaction ID" from above information we can identify who deleted the records using below script:
In this case we are taking [Transaction ID] = '0000:00001d88'

Use mydb1
Go

SELECT
[Current LSN],[Operation], 
[Transaction ID], [Begin Time],
[Transaction Name],
SUSER_SNAME ([Transaction SID]) [USER]
FROM sys.fn_dblog(NULL, NULL)
WHERE 
[Transaction ID] = '0000:00001d88'
AND
[Operation] = 'LOP_BEGIN_XACT'

The LOP_BEGIN_XACT is a log operation and includes information such as the SPID, transaction name and start time and by using the above script, you can find out the operation started at "2015/08/20 10:53:06:220" and was carried out by user "DOM\Amit_Bhardwaj".

With the above information now it is possible to restore the database prior to the deletion of rows. Using the "current LSN" number of "Transaction ID" -'0000:00001d88', will convert this value into a decimal value that shall be used within the log restore .
So the "Current LSN" full value is 00000045:00000109:0001 and break it and convert it as follows:-

Convert 1st Value- 00000045 from Hexadecimal to Decimal = 69
Convert 2nd Value- 00000109 from Hexadecimal to Decimal= 265
Convert 3rd Value- 0001 from Hexadecimal to Decimal = 1

Now using above value will create LSN number, which will use it into Restore Script with with the STOPBEFOREMARK option:
To Create LSN value,

Pick 1st Decimal Value- 69
Pick 2nd Decimal Value- 265
Pick 2nd Decimal Value- 1

Now as per LSN Number Syntax (LSN no.- 00 0000000000 00000)- 1st value contains 2 numeric decimal value (so its 69), 2nd value contains 10 numeric decimal value (so its 0000000265) and 3rd contains 5 numeric decimal value (so its 00001).
so according to this LSN number should be 69000000026500001

So first take the tail log backup.
Now Start the restoration:

1. Restore Full with norecovery
2. Restore log backup with norecovery and according to start time of the "Transaction ID" -'0000:00001d88' which is "2015/08/20 10:53:06:220". using this time restore this time log backup with the STOPBEFOREMARK option.

FUll backup mydb1_full.bak- taken at 2015/08/20 10:00
Tlog backup mydb1_trn1.trn taken at 2015/08/20 10:30
Tlog backup mydb1_trn2.trn taken at 2015/08/20 11:00

Here are the scripts to restore:

RESTORE DATABASE [mydb1] FROM  DISK = N'D:\Mydb1\mydb1_full.bak' WITH NORECOVERY
GO
RESTORE LOG [mydb1] FROM  DISK = N'D:\Mydb1\mydb1_trn1.trn' WITH NORECOVERY
GO
RESTORE LOG [mydb1] FROM  DISK = N'D:\Mydb1\mydb1_trn2.trn'
WITH RECOVERY STOPBEFOREMARK='LSN:69000000026500001'