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!
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
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.
µ