Have you ever encountered a situation where data has been deleted from your SQL Server database and you need to find out who did it and when? In this article, we will explore how to use transaction log backups to recover deleted data in SQL Server.

First, let’s set up a scenario. Assume we have a database called “ReadingDBLog” in full recovery mode with regular transaction log backups. We have a table named “Location” with 100 rows. Now, let’s delete the first nine rows of this table:

USE ReadingDBLog
GO
DELETE Location 
WHERE [Sr.No] < 10
GO
SELECT * FROM Location

After running this code, we can see that the first nine rows of the “Location” table have been deleted.

If we try to find information about these deleted rows from the active transaction log file using the fn_dblog function, we won’t find anything because the data has been flushed. Instead, we need to rely on the transaction log backups.

To find the deleted data, we will use the undocumented function fn_dump_dblog. This function allows us to read the transaction log backup and retrieve the required details. We can specify the backup file location and name as parameters to this function.

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], SUSER_SNAME([TRANSACTION SID]) as [LoginName]
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'D:\ReadingDBLog_201503022236.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'

This query will give us a list of all rows where DELETEs occurred that are part of the transaction log backup. We can also retrieve the login name of the user who performed the DELETE operation.

To find out when the DELETE occurred and who did it, we can look for the LOP_BEGIN_XACT operation along with the Transaction ID obtained from the previous query. We can use the SUSER_NAME function to get the actual login that performed the DELETE.

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], SUSER_SNAME([TRANSACTION SID]) as [LoginName]
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'D:\ReadingDBLog_201503022236.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 [Transaction ID] = '0000:000003af'
AND Operation = 'LOP_BEGIN_XACT'

Now that we have the Transaction ID and Partition ID, we can find the actual table where the DELETEs occurred:

USE ReadingDBLog
GO
SELECT so.* 
FROM sys.objects so
INNER JOIN sys.partitions sp on so.object_id = sp.object_id
WHERE partition_id = 72057594038779904

Once we have the LSN for the DELETE transaction, we can recover the deleted data using the techniques described in another article titled “Recover deleted SQL Server data and tables with the help of Transaction Log and LSNs”.

In summary, by using transaction log backups and the fn_dump_dblog function, we can find deleted data in SQL Server and recover it. This can be a valuable tool for investigating unauthorized data modifications and ensuring data integrity.