Determine who deleted SQL Server database records by querying the transaction log with PowerShell

Have you ever had a scenario where records from one or more SQL server database tables mysteriously came up missing and no one owned up to deleting them? Maybe it was an honest mistake or maybe a scheduled job deleted them. How do you figure out what happened without spending thousands of dollars on a third party product?

You need to determine what happened so this doesn’t occur again, but the immediate crisis is to get those records back from a restored copy of the database. How do you determine at what point the records were deleted so point in time recovery of the affected database can be performed into an alternate database so those records can be recovered?

You’ve got a major crisis on your hands and as always, no budget whatsoever in the form of time or money. You need this fixed now and you need it fixed for little or no cost!

PowerShell to the Rescue!

The following PowerShell function can be used to query the MSDB database on a SQL server instance to retrieve the list of transaction log backup files for a specific database and then query those files and/or the active transaction log for insert, update, or delete operations that have occurred within a specified date and time range.

Regardless if you have multiple files with a single transaction log backup in them, multiple transaction log backups in a single file, or transaction log backups striped across multiple files, this function will determine that and dynamically build and execute the necessary query to locate and return the desired transactions.

If the date specified for the EndDate parameter is greater than the end date of the last transaction log backup, then the function automatically knows that it needs to query the active transaction log for those transactions. Oh, and by the way, the query for the transaction log backups is different than the one for the active transaction log but no worries as this function takes care of all of that for you and seamlessly returns the desired results without the user of it ever having to know all of the intricacies of the process itself.

That’s the POWER of PowerShell!


In the following example, the Find-MrSqlDatabaseChange function is used to find any delete operations that have occurred in the pubs database in the past 14 days:


As the previous set of results show, there were two deletes that occurred in the pubs database in the past 14 days. Both occurred on July 2nd and both were performed as someone or something that was logged in as the administrator account in the mikefrobbins domain. The “Current LSN” can be used to know what point to restore the database to so the affected database records can be recovered.

The verbose parameter was specified in the previous example so more details about what is occurring as the function runs are displayed, otherwise that additional level of detail wouldn’t be present if the verbose parameter was omitted.

Be sure to check out my blog article from last week Query SQL Server from PowerShell without the SQL module or snapin as the Invoke-MrSqlDataReader function that was created and demonstrated in it is required by the Find-MrSqlDatabaseChange function.

The Find-MrSqlDatabaseChange function shown in this blog article can be downloaded as part of my MrSQL PowerShell module from GitHub.



  1. Bryan Cafferky

    This a great idea for query the database transaction log. Good one to have on hand. Thanks.

  2. MMB

    Will this work for third party backup tools?

  3. Wanderlei Santos (@wsantosf)

    Anyway of getting more details about the table/record being deleted?

  4. Johnson Welch

    I have tried to recover the deleted data in the same way as you documented but my copy database moves in restoring mode because we got the error “The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.” I have read another post which is similar to my query but stuck with mentioned problem.


Leave a Reply

%d bloggers like this: