How to Read a Transaction Log Backup

The transaction log backup is stored in SQL Server proprietary format, just like the transaction log itself. Even though the transaction log backup is in a special format, Microsoft has provided us with the fn_dump_dblog() function to read transaction log backups.   This function is undocumented, so you should use care when using this function in a production environment.

The best way to show you how to use this function is with an example.  Suppose you what to find out when and who deleted a table from your database.  For this demo, I will be running the following code:

USE master;
GO
CREATE DATABASE Read_Backup_Logfile_Demo;
GO 
USE [Read_Backup_Logfile_Demo];
GO
CREATE TABLE Read_Backup_Log
(ID int, 
Status varchar(200));
GO
BACKUP DATABASE [Read_Backup_Logfile_Demo] TO  
DISK = N'C:TempRead_Backup_Logfile_DELETE_Demo.bak'
WITH FORMAT, INIT;
GO
DROP TABLE Read_Backup_Log;
GO
BACKUP LOG [Read_Backup_Logfile_Demo] TO  
DISK = N'C:tempRead_Backup_Logfile_DELETEDemo_Log.bak'  
WITH FORMAT,INIT;
GO
SELECT [Current LSN], [Operation], [Transaction Name], [Transaction ID], SUSER_SNAME ([Transaction SID]) AS DBUser 
FROM fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:tempRead_Backup_Logfile_DELETEDemo_Log.bak',
            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
        [Transaction Name] LIKE ('DROPOBJ')
 

In this code, you can see that I first created a database named “Read_Backup_Logfile_Demo”.  I then created and populated a table named “Read_Backup_Log”.  Once the table “Read_Backup_Log” was created and populated I then took a full database backup.  Once the backups was completed, I dropped the table and then took a transaction log backup.    Lastly, this code read the transaction log backup using the fn_dump_dblog()  function and constrained the rows returned to only those rows that have a transaction name that contains the string value “DROPOBJ”.  When I run this code I get the following output:

Current LSN              Operation                Transaction Name                  Transaction ID DBUser
 ----------------------- ------------------------ --------------------------------- -------------- ------

00000023:00000100:0001  LOP_BEGIN_XACT           DROPOBJ                           0000:0000032d  Greg-PCGREG

Here you can see when I passed the Transaction SID to the SUSER_SNAME function I was able identify the database user that deleted my table.  In this case, the user name is  “Greg-PC/Greg”.

This example only gave you a quick view on how to read the transaction log backup using the fn_dump_dblog() function.  It also only returned the user that deleted the table.  In my next tip, I will describe how to use the Transaction ID value to identify the object_id  for a deleted table.  Next time you need to determine who deleted a table  try using the fn_dump_dblog() function.

See all articles by Greg Larsen