Powershell retrieve Log Sequence Number

The following Powershell class has functions that execute SQL queries and formats the results into a Hash table. The queries revolve around retrieving database Log Sequence Numbers and can be used to analyzing database backups.

class SQLBackupHelpers
{
    [string]$SqlServer
    [string]$DBName
    [string]$BackupType="D"
    [string]$BackupFile=""
    [void] Initialize () {
        Import-Module SQLPS -DisableNameChecking
    }
    [psobject] RunSQLCommand ([string]$SqlCommand) {
    [psobject]$row=$null;
    Try { 
        $ConnectionString="Server=$($this.SqlServer);Database=$($this.DBName);Trusted_Connection=yes";
        $conn = new-object System.Data.SqlClient.SqlConnection $ConnectionString
        $conn.Open()
        $comm = $conn.CreateCommand()
        $comm.CommandText = $SqlCommand
        $reader = $comm.ExecuteReader()
        
        # Read results into PSobject and return to calling function
        while($reader.Read())
        {            
            $row = new-object PSObject
            for($i = 0; $i -lt $reader.FieldCount; ++$i)
            {
                add-member -inputObject $row -memberType NoteProperty -name $reader.GetName($i) -value $reader.GetValue($i) | Out-Null
            }
        }
        $reader.Close()
        $conn.Close()
    }
    Catch { throw; } 
    return $row;
    }
    [psobject] LatestBackupMetadata () {
        $out = New-Object PSObject;
        $sqlcmd=$this.BackupCheckpointLSNQuery();
        try{
            $out = $this.RunSQLCommand($sqlcmd)
        }
        finally{
            Write-Information "query='$sqlCmd' executed."
        }
        return $out;
    }
    [psobject] LatestBackupFileMetadata () {
        $out = New-Object PSObject;
        $sqlcmd=$this.DifferentialBaseLSNQuery();
        try{
            $out = $this.RunSQLCommand($sqlcmd)
        }
        finally{
            Write-Information "query='$sqlCmd' executed."
        }
        return $out;
    }
    [string] BackupCheckpointLSNQuery(){
    return "SELECT top 1
        b.[backup_start_date],
        (CASE b.[type] WHEN N'D' THEN N'Full' WHEN N'I' THEN N'Diff' WHEN N'L' THEN N'Log' ELSE N'Unknown' END) AS N'Type',
        b.[name],
        b.[backup_set_uuid],
        b.[differential_base_guid],
        b.[checkpoint_lsn],
        b.[first_lsn],
        b.[last_lsn],
        b.[database_backup_lsn],
	    bm.physical_device_name AS FilePath
        FROM [msdb].[dbo].[backupset] b
	    INNER JOIN [msdb].[dbo].[backupmediafamily] bm ON b.media_set_id = bm.media_set_id
        WHERE [database_name] = N'$($this.DbName)' AND [type] = N'$($this.BackupType)'
        ORDER BY [backup_start_date] desc";
    }
    [string] DifferentialBaseLSNQuery(){
        return "RESTORE HEADERONLY FROM DISK=N'$($this.BackupFile)';";
    }
    [bool] ValidateDiffBackup () {
        $fullBackup = $this.LatestBackupMetadata();
        $diffFile = $this.LatestBackupFileMetadata();
        return $fullBackup.checkpoint_lsn -eq $diffFile.DifferentialBaseLSN;
    }
}
#[DiffBackupHelpers]$diffBackupHelper = New-Object DiffBackupHelpers
#$diffBackupHelper.Initialize();
#$diffBackupHelper.SqlServer = 'sqlserver_01'
#$diffBackupHelper.DBName = 'example_db'
#$diffBackupHelper.BackupType = 'D'
#$diffBackupHelper.BackupFile = '\\example\example_db.bak'
#$diffBackupHelper.RunSQLCommand("select @@version")
#$diffBackupHelper.BackupCheckpointLSNQuery()
#$diffBackupHelper.DifferentialBaseLSNQuery()
#$diffBackupHelper.LatestBackupMetadata()
#$diffBackupHelper.LatestBackupFileMetadata();
#$diffBackupHelper.ValidateDiffBackup()

Leave a Reply

Your email address will not be published. Required fields are marked *