In part one and two of our Tour of the Transaction Log articles we focused on reading events from the active transaction log, there can be times when that will not be enough and we actually have to dig deeper into transaction log backups to find the information we need.

In this article I will show you the use of the fn_dump_dblog function to see the contents of a transaction log backup!

As always, some commands like the fn_dblog and fn_dump_dblog are undocumented and not supported by Microsoft, use with caution!

Building the test database

We will be using the same scenario to test as in the precious articles, so let’s get our test database ready and insert some data (make sure to change the drive/folders):

CREATE DATABASE [TlogTest] ON PRIMARY

( 
NAME = N'TlogTest', FILENAME = N'D:\Data\TlogTest.mdf' , SIZE = 4096KB ,
FILEGROWTH = 1024KB 
)

LOG ON
( 
NAME = N'TlogTest_log', FILENAME = N'D:\Log\TlogTest_log.ldf' , SIZE = 1024KB ,FILEGROWTH = 10%
)
GO

Create our test table:

USE [TlogTest]

CREATE TABLE [Table01]
(
[n1] INT IDENTITY,
[n2] CHAR DEFAULT 'a'
)

And insert some data:

INSERT INTO Table01 DEFAULT VALUES
GO 1000

A very important part of this demo is to make sure that the database we created is in the right recovery model. We need to be sure the database is in the Full recovery model or else we won’t be able to make transaction log backups.

Run the following query and check if the TlogTest database is in the Full recovery model:

SELECT name, recovery_model_desc 
FROM sys.databases
GO

It should look something like this:

If your database is not in the Full recovery model change it through the database properties or a query:

ALTER DATABASE TlogTest
SET RECOVERY FULL WITH NO_WAIT
GO

Now that we have our test database in the correct recovery model we need to make the initial full database backup to make sure we can make a transaction log backup later on.

You can use the following query to backup the database (make sure to change the drive/folders):

BACKUP DATABASE TlogTest
TO DISK = 'D:\Backup\TlogTest.bak'

Now that we have our full backup, let’s start with changing some stuff around!

Reading information from a transaction log backup

So we got our test database ready and created a full database backup, let’s change some data so we actually have something to read from the transaction log.

For this example we will be deleting the same data as we did in the second part of the tour:

USE [TlogTest]

DELETE FROM Table01
WHERE n1 = 1
GO

If you read the previous article you know that if we ran fn_dblog against our test database we would get some information about the delete in the active transaction log however in this article we are interested in getting the information back when it is no longer in the active transaction log.

Let’s clear the active portion of the transaction log by running a transaction log backup (again change the drive/folder):

BACKUP LOG [TlogTest] 
TO  DISK = N'D:\Backup\TlogTest_log.trn' 
GO

Now that we made a transaction log backup our DELETE operation should no longer be in the active portion of the transaction log. We can run the query we used in the previous Tour article to read the active transaction log for DELETE operations:

USE TlogTest

SELECT
[Begin Time],
[Current LSN],
[Operation],
[Transaction Name],
[Transaction SID],
[Page ID],
[Slot ID]
FROM fn_dblog(DEFAULT, DEFAULT)
WHERE [Operation] = 'LOP_DELETE_ROWS' 
OR [Description] LIKE '%DELETE%'
ORDER BY [Current LSN] DESC
GO

As you can see in the screenshot below my transaction log is empty of all DELETE operations:

If we wanted to find out about operations that are no longer in the active portion of the transaction log we can use the (undocumented) function fn_dump_dblog against a transaction log backup.

A good reason to use this function is to find the LSN of the operation that did something unwanted to your data, with this LSN number you can perform a transaction log restore and stop right before that LSN. We won’t go into details about a STOPBEFOREMARK restore in this article but some information about it can be found here. 

Let’s use the fn_dump­dblog function against the transaction log backup we just made and look for our DELETE operation:

SELECT

[Begin Time],
[Current LSN],
[Operation],
[Transaction Name],
[Transaction SID],
[Page ID],
[Slot ID]
FROM
fn_dump_dblog 

(
NULL, NULL, N'DISK', 1, N'D:\Backup\TlogTest_log.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE [Operation] = 'LOP_DELETE_ROWS' 
OR [Description] LIKE '%DELETE%'
ORDER BY [Current LSN] DESC
GO

Take a look at the 63 parameters you need to supply! They need to be in here or else the function won’t work.

If everything worked you should get a result back like I did:

Pretty awesome!

Hopefully this information will help you out when you get into a situation where you need to restore to a certain LSN or want to know how modified some data that isn’t in the active transaction log anymore.

More information about fn_dump_dblog can be found on Paul Randal’s great blog at: http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/