• Adam Thurgar

Reading a transaction log file backup


Have you ever had to find out what happened to your SQL Server. Who deleted that data? What event triggered something to happen. Sometimes all you have is a transaction log backup as your source of information. Reading a transaction log file backup is not that easy, but it isn't that hard either. You just need to use the undocumented fn_dump_dblog. I usually do this in a temporary database, to not interfere with anything else.

Here is a quick example:

USE ScratchDB

GO

SELECT * INTO DUMPLOG FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'V:\DBBackup\Tran Log\MY_LOG_20180702_124501.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);

and then start reading through the results

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID]

from DUMPLOG where [Begin Time] > '2018/07/02 12:40:00:000' order by [Begin Time];

Hopefully from this you can track down that issue - or at least have a bit more information to help solve the puzzle.


1 view

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t