How to find a Transaction in a Backup using fn_dblog()

In SQL Server it is difficult to guess what gets backed up in the database, if you have set the database in full backup, then its work is to note the LSN of the transaction log’s, Read the allocated extents in the various data files, Note LSN again, read all the LSN between first and last LSN.

A transaction will be reflected at the time of database restoration if it commits before or on the LSN. Otherwise, it will be undone. It may well commit and rolled back during the backup operation.

Now I will use the database “mydb” and will set it to full recovery mode-

ALTER DATABASE mydb SET RECOVERY FULL;
BACKUP DATABASE mydbTO DISK=‘C:\backup\mydb’ WITH INIT;
GO

Now clear all the backup history tables in MSDB:

USE msdb;
GO
EXEC sp_delete_backuphistory ’01/01/2015′;
GO

Now I am going to set “0” to column “population” :

UPDATE mydb.country.peoples SET population = 0;
GO
CHECKPOINT;
GO

This contrived transaction will generate many transaction log and In first connection I start the backup:

BACKUP DATABASE mydb TO DISK=‘C:\backup\mydb.bck’ WITH INIT;
SELECT GETDATE ();
GO

In second connection I start these contrived transactions.

BEGIN TRAN
DECLARE var1 INT
DECLARE var2 INT
SELECT var1 = 1
WHILE (var1 < 6)

BEGIN
SELECT var2 = 1
WHILE (var2 < 201)

BEGIN
UPDATE mydb.country.peoples SET population = var2
SELECT var2=var2+1
END
SELECT var1=var1+1
END
COMMIT TRAN;
SELECT GETDATE ();
GO

The backup process ends at 01-01-2015 03:13:29.312 and the transaction process ends at 01-01-2015 03:13:29.025.

Lets check the actual changes by running following command:

SELECT MAX (population) FROM mydb.country.peoples ;
GO

Is the transactions contained in the backup? Lets find in last LSN in the full backup

SELECT Backup_Start_Date, Backup_Finish_Date, First_LSN, Last_LSN
FROM msdb.dbo.backupset WHERE database_name = ‘mydb’;
GO

And we get:

It is tough to detect what is in decimal, So, to find what is in the log we ,it is necessary to convert these three values to hex.

After converting it to hex we get 2B:2123:7. Now, see the transaction log for mydb using fn_dblog() function.

USE mydb;
GO
SELECT [Current LSN], Operation, [Transaction ID], AllocUnitName FROM fn_dblog (NULL, NULL);
GO

The LSN for that log is:

00000033:00000335:0014  LOP_MODIFY_ROW  0000:00000988  country.peoples.PK_peoples_peopleID

The LSN is showing at the middle of the transaction.

0000012e:00000534:01var1  LOP_MODIFY_ROW  0000:00000988  country.peoples.PK_peoples_peopleID

0000012e:00000534:01var2  LOP_COMMIT_XACT    0000:00000988   NULL

Now, take a log backup of that transaction.

BACKUP LOG mydb TO DISK=‘C:\backup\mydb_Log.bck’ WITH INIT;
GO

Now I will restore the full backup and see the transaction is in there or not:

USE master;
GO
RESTORE DATABASE mydb FROM DISK=‘C:\backup\mydb.bck’ WITH REPLACE, RECOVERY;
GO 
SELECT MAX (population) FROM mydb.country.people;
GO

The output is 0, hence the transaction is not in there. Now again, lets restore from full backup using NORECOVERY.

RESTORE DATABASE mydb FROM DISK=‘C:\ backup\mydb.bck’ WITH REPLACE, NORECOVERY;
GO

RESTORE LOG mydb FROM DISK=‘C:\ backup\mydb _Log.bck’ WITH RECOVERY;
GO

SELECT MAX (population) FROM mydb. country.people;
GO

It returns 350. So, after doing this we know how we can use the undocumented function fn_dblog() to check that a transaction is contained in a backup or not.