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
Sql Server Powershell 