Converting a SQL Server Log Sequence Number with PowerShell
As demonstrated in one of my previous blog articles
Determine who deleted SQL Server database records by querying the transaction log with PowerShell,
someone or something has deleted records from a SQL Server database. You've used my
Find-MrSqlDatabaseChange
function to determine when the delete operation occurred based on
information contained in either transaction log backups or the active transaction log:
1Find-MrSqlDatabaseChange -ServerInstance SQL01 -Database pubs -StartTime (Get-Date -Date '03/28/2016 14:55 PM')
You're ready to perform point in time recovery of the database to the LSN (Log Sequence Number) just
before the delete occurred but the LSN provided from the transaction log is in a different format
than what's required to perform a restore of the database. How can the Current LSN
as shown in
the previous set of results be converted to what's needed to perform point in time recovery of the
database? You could use some cryptic T-SQL code, but a PowerShell function can also be written as a
reusable tool and added to your SQL toolkit:
1#Requires -Version 3.0
2function Convert-MrSqlLogSequenceNumber {
3
4<#
5.SYNOPSIS
6 Converts a SQL LSN (log sequence number) from a three part hexadecimal number
7 format to a decimal based string format.
8
9.DESCRIPTION
10 Convert-MrSqlLogSequenceNumber is an advanced PowerShell function that converts
11 one or more SQL transaction log sequence numbers from a three part hexadecimal
12 format that is obtained from querying a transaction log backup or the active
13 transaction log to a decimal based string format which can be used to perform
14 point in time recovery of a SQL Server database using the StopAtMark option.
15
16.PARAMETER LogSequenceNumber
17 The three part hexadecimal SQL log sequence number obtained from an insert, update,
18 or delete operation that has been recorded in the transaction log or log backup.
19
20.EXAMPLE
21 Convert-MrSqlLogSequenceNumber -LogSequenceNumber '0000002e:00000158:0001'
22
23.EXAMPLE
24 '0000002e:00000158:0001' | Convert-MrSqlLogSequenceNumber
25
26.INPUTS
27 String
28
29.OUTPUTS
30 PSCustomObject
31
32.NOTES
33 Author: Mike F Robbins
34 Website: http://mikefrobbins.com
35 Twitter: @mikefrobbins
36#>
37
38 [CmdletBinding()]
39 param (
40 [Parameter(Mandatory,
41 ValueFromPipeline,
42 ValueFromPipelineByPropertyName)]
43 [ValidateScript({
44 If ($_ -match '^[0-9A-Fa-f]+:[0-9A-Fa-f]+:[0-9A-Fa-f]+$') {
45 $True
46 }
47 else {
48 Throw "$_ is not a valid three part hexadecimal SQL log sequence number from a transaction log backup."
49 }
50 })]
51 [Alias('LSN', 'Current LSN')]
52 [string[]]$LogSequenceNumber
53 )
54
55 PROCESS {
56
57 foreach ($Number in $LogSequenceNumber) {
58 $i = 1
59
60 $Results = foreach ($n in $Number -split ':') {
61 $Int = [convert]::ToInt32($n, 16)
62 switch ($i) {
63 1 {$Int; break}
64 2 {"$([string]0*(10-(([string]$Int).Length)))$Int"; break}
65 3 {"$([string]0*(5-(([string]$Int).Length)))$Int"; break}
66 Default {Throw 'An unexpected error has occured.'}
67 }
68 $i++
69 }
70
71 [pscustomobject]@{
72 'OriginalLSN' = $Number
73 'ConvertedLSN' = $Results -join ''
74 }
75
76 }
77
78 }
79
80}
Take a moment to notice how the code in the previous example is formatted. It's formatted for
readability. Your co-workers and future self will thank you. Also notice that it contains a
Requires
statement which states the minimum version of PowerShell that's required. It includes
comment based help so help can be obtained just like it is for any other cmdlet. It uses parameter
validation to require that a value is provided and that it's in a specific format. All of these are
standard items that should be included in any PowerShell function
The conversion returns both the original LSN and the converted LSN:
1Convert-MrSqlLogSequenceNumber -LSN 0000002e:00000158:0001
An even better option is to make PowerShell tools modular so the Convert-MrSqlLogSequenceNumber
function can accept the output of my previously created Find-MrSqlDatabaseChange
function via
pipeline input so the necessary log sequence number can be determined with a single PowerShell
one-liner instead of having to run one command and then copy and paste its output as input for the
next command:
1Find-MrSqlDatabaseChange -ServerInstance SQL01 -Database pubs -StartTime (Get-Date -Date '03/28/2016 14:55 PM') |
2Convert-MrSqlLogSequenceNumber
Maybe there are multiple log sequence numbers that need translating? That functionality is built in as well:
1Find-MrSqlDatabaseChange -ServerInstance SQL01 -Database pubs -StartTime (Get-Date -Date '03/01/2016 14:55 PM') |
2Convert-MrSqlLogSequenceNumber
All of the PowerShell functions shown and/or referenced in this blog article can be downloaded from my SQL repository on GitHub.
µ