Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted July 3, 2017

How to Read a Transaction Log Backup

By Greg Larsen

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:\Temp\Read_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:\temp\Read_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:\temp\Read_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-PC\GREG

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



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.