Sql Server Powershell

Quick not fully tested script to check whether a set of provided backup files contain a valid log sequence number chain.

 using namespace Microsoft.SqlServer.Management.smo
using namespace System.Collections.Generic


function Test-LSNChain{
    param(
        [Parameter(Mandatory)]
        [string[]]$File,
        [ValidateNotNullOrEmpty()]
        [string]$ComputerName = $env:computername,
        [ValidateNotNullOrEmpty()]
        $Port = 1433
    )
    begin{
        $objects = New-Object List[PSCustomObject];
    }
    process{
        
        try{
            $srv = [Server]::new("$ComputerName,$Port");
            foreach($f in $File){

                if(!(Test-Path -Path $f -PathType Leaf)){
                    throw "File $f could not be located."
                }

                $restore = [Restore]::new();
                $restore.Devices.Add(([BackupDeviceItem]::new($f, [DeviceType]::File)));
                $header = $restore.ReadBackupHeader($srv);
                $type = switch($header.BackupType){
                    (1){
                        "FULL"
                        break;
                    }
                    (5){
                        "DIFF"
                        break;
                    }
                    (2){
                        "LOG"
                        break;
                    }
                    (default){
                        throw "unsupported backup type"
                    }
                } # switch

                [void]$objects.Add([PSCustomObject]@{
                    BackupType = $type 
                    File = $f
                    CreationTime = [System.IO.File]::GetCreationTime($f)
                    ComputerName = $header.ServerName 
                    DatabaseName = $header.DatabaseName 
                    #DatabaseVersion = $header.DatabaseVersion 
                    ServerVersion = ([string]$header.SoftwareVersionMajor + '.' + [string]$header.SoftwareVersionMinor + '.' +[string]$header.SoftwareVersionBuild)
                    SizeMB = [Math]::Round($header.BackupSize/1MB,2)
                    IsCopyOnly = $header.IsCopyOnly 
                    FirstLSN = $header.FirstLSN
                    LastLSN = $header.LastLSN 
                    CheckpointLSN = $header.CheckpointLSN 
                    DatabaseBackupLSN = $header.DatabaseBackupLSN 
                    BackupStartDate = $header.BackupStartDate
                    BackupFinishDate = $header.BackupFinishDate 
                    DifferentialBaseLSN = $header.DifferentialBaseLSN 
                });
            } 
            
            # full can have a different DatabaseBackupLSN than the rest of the backups...this is just a short-circuit...the rest should all share the same backuplsn
            #if(($objects | ?{$_.BackupType -ne 'FULL'} | Group-Object -Property DatabaseBackupLSN | Measure-Object).Count -gt 1){
            if(($objects | Group-Object -Property DatabaseBackupLSN | Measure-Object).Count -gt 1){
                Write-Warning "There are more than one Database Backup LSN's listed in the backup set of files supplied."
                return $false;
            }

            $objects = ($objects | Sort BackupFinishDate);

            if($objects[0].BackupType -ne 'FULL'){
                write-warning "The first backup file $($objects[0].File) in this set is not a full backup."
                return $false;
            }

            for($i=0; $i-lt$objects.Count; $i++){
                $backup = $objects[$i];
                $nextBackup = $objects[$i+1];

                <#
                $backup | select BackupType, FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN, BackupStartDate, BackupFinishDate, DifferentialBaseLSN | ft -AutoSize
                $nextBackup | select BackupType, FirstLSN, LastLSN, CheckpointLSN, DatabaseBackupLSN, BackupStartDate, BackupFinishDate, DifferentialBaseLSN | ft -AutoSize
                #>

                if($null -eq $nextBackup){
                    return $true;
                }  

                if($backup.BackupType -eq 'FULL' -and $nextBackup.BackupType -eq 'DIFF'){  #diff databasebackuplsn must = full checkpointlsn
                    if($backup.CheckpointLSN -ne $nextBackup.DatabaseBackupLSN){
                        write-warning "The differential backup file $($nextBackup.File) is not the correct differential backup for the full backup $($backup.File)"
                        return $false;
                    }
                }
                elseif($backup.BackupType -eq 'FULL' -and $nextBackup.BackupType -eq 'LOG'){  #full backup must be > log.firstlsn and < log.lastlsn
                    if($backup.LastLSN -gt $nextBackup.LastLSN){
                        Write-Warning "Transaction log backup $($nextBackup.File) is too late to apply to Full backup $($backup.File)"
                        return $false;
                    }
                    if($nextBackup.LastLSN -lt $backup.FirstLSN){
                        Write-Warning "Transaction log backup $($nextBackup.File) is too recent to apply to Full backup $($backup.File)"
                        return $false;
                    }
                }
                elseif($backup.BackupType -eq 'LOG' -and $nextBackup.BackupType -eq 'LOG'){ # previous log backup lastlsn must = next log backup firstlsn
                    if($backup.LastLSN -ne $nextBackup.FirstLSN){
                        write-warning "The transaction log backup $($nextBackup.File) does not apply to the previous transaction log file $($backup.File)"
                        return $false;
                    }
                }
                elseif($backup.BackupType -eq 'DIFF' -and $nextBackup.BackupType -eq 'LOG'){  # diff checkpointlsn must match next log checkpointlsn & diff lastlsn must be > log firstlsn & < log lastlsn
                    if($backup.CheckpointLSN -ne $nextBackup.CheckpointLSN){
                        write-warning "The transaction log backup file $($nextBackup.File) does not apply to the current differential backup file $($backup.File)"
                        return $false;
                    }
                    if($backup.LastLSN -gt $nextBackup.LastLSN){
                        Write-Warning "Transaction log backup $($nextBackup.File) is too late to apply to Diff backup $($backup.File)"
                        return $false;
                    }
                    if($nextBackup.LastLSN -lt $backup.FirstLSN){
                        Write-Warning "Transaction log backup $($nextBackup.File) is too recent to apply to Diff backup $($backup.File)"
                        return $false;
                    }
                }
                elseif($backup.BackupType -eq 'LOG' -and $nextBackup.BackupType -eq 'DIFF'){  # diff lastlsn+1 must be between the log firstlsn & log lastlsn
                    if(($nextBackup.LastLSN+1) -gt $backup.FirstLSN -and ($nextBackup.LastLSN+1) -lt $backup.LastLSN){
                        write-waring "The differential backup file $($nextBackup.File) does not apply to transaction log file $($backup.File)"
                        return $false;
                    }
                }

            } # for

            

        }  # try
        catch{
            throw $_ 
        }  # catch
        finally{
            if($srv){
                $srv.ConnectionContext.Disconnect();
            }
        }  # finally

    }  # process
    end{

    } # end

} # function