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.

  1#Requires -Version 3.0
  2function Find-MrSqlDatabaseChange {
  3
  4<#
  5.SYNOPSIS
  6    Queries the active transaction log and transaction log backup file(s) for
  7    insert, update, or delete operations on the specified database.
  8
  9.DESCRIPTION
 10    Find-MrSqlDatabaseChange is a PowerShell function that is designed to query
 11    the active transaction log and transaction log backups for either insert,
 12    update, or delete operations that occurred on the specified database within
 13    the specified datetime range. The Invoke-MrSqlDataReader function which is
 14    also part of the MrSQL script module is required.
 15
 16.PARAMETER ServerInstance
 17    The name of an instance of the SQL Server database engine. For default
 18    instances, only specify the server name: 'ServerName'. For named instances,
 19    use the format 'ServerName\InstanceName'.
 20
 21.PARAMETER TransactionName
 22    The type of transaction to search for. Valid values are insert, update, or
 23    delete. The default value is 'Delete'.
 24
 25.PARAMETER Database
 26    The name of the database to query the transaction log for.
 27
 28.PARAMETER StartTime
 29    The beginning datetime to start searching from. The default is at the
 30    beginning of the current day.
 31
 32.PARAMETER EndTime
 33    The ending datetime to stop searching at. The default is at the current
 34    datetime (now).
 35
 36.PARAMETER Credential
 37    SQL Authentication userid and password in the form of a credential object.
 38
 39.EXAMPLE
 40     Find-MrSqlDatabaseChange -ServerInstance sql04 -Database pubs
 41
 42.EXAMPLE
 43     Find-MrSqlDatabaseChange -ServerInstance sql04 -TransactionName Update `
 44     -Database Northwind -StartTime (Get-Date).AddDays(-14) `
 45     -EndTime (Get-Date).AddDays(-7) -Credential (Get-Credential)
 46
 47.EXAMPLE
 48     'AdventureWorks2012' | Find-MrSqlDatabaseChange -ServerInstance sql02\qa
 49
 50.INPUTS
 51    String
 52
 53.OUTPUTS
 54    DataRow
 55
 56.NOTES
 57    Author:  Mike F Robbins
 58    Website: http://mikefrobbins.com
 59    Twitter: @mikefrobbins
 60#>
 61
 62    [CmdletBinding()]
 63    param (
 64        [Parameter(Mandatory)]
 65        [string]$ServerInstance,
 66
 67        [ValidateSet('Insert', 'Update', 'Delete')]
 68        [string]$TransactionName = 'Delete',
 69
 70        [Parameter(Mandatory,
 71                   ValueFromPipeline)]
 72        [string]$Database,
 73
 74        [ValidateNotNullOrEmpty()]
 75        [datetime]$StartTime = (Get-Date).Date,
 76
 77        [ValidateNotNullOrEmpty()]
 78        [datetime]$EndTime = (Get-Date),
 79
 80        [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty
 81    )
 82
 83    BEGIN {
 84        $Params = @{
 85            ServerInstance = $ServerInstance
 86        }
 87
 88        if($PSBoundParameters.Credential) {
 89            $Params.Credential = $Credential
 90        }
 91    }
 92
 93    PROCESS {
 94        Write-Verbose -Message "Obtaining a list of transaction log backup files for the $Database database"
 95
 96        $TransactionLogBackupHistory = Invoke-MrSqlDataReader @Params -Database msdb -Query "
 97        SELECT backupset.backup_set_id, backupset.last_family_number, backupset.database_name, backupset.recovery_model, backupset.type,
 98        backupset.position, backupmediafamily.physical_device_name, backupset.backup_start_date, backupset.backup_finish_date
 99        FROM backupset
100        INNER JOIN backupmediafamily
101        ON backupset.media_set_id = backupmediafamily.media_set_id
102        WHERE database_name = '$Database'
103        AND type = 'L'
104        AND backup_start_date >= '$StartTime'"
105
106        $TransactionLogBackups = $TransactionLogBackupHistory | Where-Object backup_finish_date -le $EndTime
107        $Params.Database = $Database
108
109        if (($TransactionLogBackups.count) -ne (($TransactionLogBackups | Select-Object -ExpandProperty backup_set_id -Unique).count)) {
110            Write-Verbose -Message 'Transaction log backups were found that are striped accross multiple backup files'
111
112            $UniqueBackupSetId = $TransactionLogBackups | Select-Object -ExpandProperty backup_set_id -Unique
113
114            $BackupInfo = foreach ($SetId in $UniqueBackupSetId) {
115                Write-Verbose -Message "Creating an updated list of transaction log backup files for backup set $($SetId)"
116
117                $BackupSet = $TransactionLogBackups | Where-Object backup_set_id -in $SetId
118                [pscustomobject]@{
119                    backup_set_id = $BackupSet | Select-Object -First 1 -ExpandProperty backup_set_id
120                    last_family_number = $BackupSet | Select-Object -First 1 -ExpandProperty last_family_number
121                    database_name = $BackupSet | Select-Object -First 1 -ExpandProperty database_name
122                    recovery_model = $BackupSet | Select-Object -First 1 -ExpandProperty recovery_model
123                    type = $BackupSet | Select-Object -First 1 -ExpandProperty type
124                    position = $BackupSet | Select-Object -First 1 -ExpandProperty position
125                    physical_device_name = $BackupSet.physical_device_name
126                    backup_start_date = $BackupSet | Select-Object -First 1 -ExpandProperty backup_start_date
127                    backup_finish_date = $BackupSet | Select-Object -First 1 -ExpandProperty backup_finish_date
128                }
129            }
130        }
131        else {
132            Write-Verbose -Message 'No transaction log backup sets were found that are striped accross multiple files'
133            $BackupInfo = $TransactionLogBackups
134        }
135
136        foreach ($Backup in $BackupInfo) {
137            Write-Verbose -Message "Building a query to locate the $TransactionName operations in transaction log backup set $($Backup.backup_set_id)"
138
139            $Query = "SELECT [Current LSN], Operation, Context, [Transaction ID], [Transaction Name],
140                      Description, [Begin Time], SUser_SName ([Transaction SID]) AS [User]
141                      FROM fn_dump_dblog (NULL,NULL,N'DISK',$($Backup.Position),
142                      $("N$(($Backup.physical_device_name) | ForEach-Object {"'$_'"})" -replace "' '","', N'"),
143                      $((1..(64 - $Backup.last_family_number)) | ForEach-Object {'DEFAULT,'}))
144                      WHERE [Transaction Name] = N'$TransactionName'
145                      AND [Begin Time] >= '$(($StartTime).ToString('yyyy/MM/dd HH:mm:ss'))'
146                      AND ([End Time] <= '$(($EndTime).ToString('yyyy/MM/dd HH:mm:ss'))'
147                      OR [End Time] is null)" -replace ',\)',')'
148
149            $Params.Query = $Query
150
151            Write-Verbose -Message "Executing the query for transaction log backup set $($Backup.backup_set_id)"
152            Invoke-MrSqlDataReader @Params
153        }
154
155        if ($EndTime -gt ($TransactionLogBackupHistory | Select-Object -Last 1 -ExpandProperty backup_finish_date)) {
156            Write-Verbose -Message "Building a query to locate the $TransactionName operations in the active transaction log for the $Database database"
157
158            $Query = "SELECT [Current LSN], Operation, Context, [Transaction ID], [Transaction Name],
159                      Description, [Begin Time], SUser_SName ([Transaction SID]) AS [User]
160                      FROM fn_dblog (NULL, NULL)
161                      WHERE [Transaction Name] = N'$TransactionName'
162                      AND [Begin Time] >= '$(($StartTime).ToString('yyyy/MM/dd HH:mm:ss'))'
163                      AND ([End Time] <= '$(($EndTime).ToString('yyyy/MM/dd HH:mm:ss'))'
164                      OR [End Time] is null)"
165
166            $Params.Query = $Query
167
168            Write-Verbose -Message "Executing the query for the active transaction log for the $Database database"
169            Invoke-MrSqlDataReader @Params
170        }
171    }
172}

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!

bam.jpg

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:

1Find-MrSqlDatabaseChange -ServerInstance sql04 -Database pubs -StartTime (Get-Date).AddDays(-14) -Verbose

sql-db-changes1a.jpg

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.

µ