"Last xxxx backup times" checks after AG failover #893
Description
Feature Request
After an AG failover, checks for Full/Diff/Log backup time check fails because we are looking for the current primary node.
Please give your feedback to add (or not) this feature regarding the additional information below.
New Check
What would you like to check?
Check backup time on all secondary to get the most recent of all backups
What should be configurable for the results of the check ?
A new configuration item might enable this option. Because scanning all secondaries might take a lot of time depending on the replicas and database number.
Set-PSFConfig -Module dbachecks -Name policy.backup.getbackuphistoryallreplicas -Value $true -Initialize -Description "Checks for last backups accross all Availability Group replicas"
Additional information
The following code will only test Log backups but Full and Diff are already written.
I only have ~50 databases on several AGs with 2 replicas. Running those new tests is time consuming.
Need help to evaluate the performance impact and intensive testing on large environments to add this feature or not.
Describe "Last Log Backup Times" -Tags LastLogBackup, LastBackup, Backup, DISA, Varied, $filename {
$maxlog = Get-DbcConfigValue policy.backup.logmaxminutes
$graceperiod = Get-DbcConfigValue policy.backup.newdbgraceperiod
$skipreadonly = Get-DbcConfigValue skip.backup.readonly
$skipsecondaries = Get-DbcConfigValue skip.backup.secondaries
$getbackuphistoryallreplicas = Get-DbcConfigValue policy.backup.getbackuphistoryallreplicas
[DateTime]$sqlinstancedatetime = $InstanceSMO.Query("SELECT getutcdate() as getutcdate").getutcdate.ToUniversalTime()
[DateTime]$oldestbackupdateallowed = $sqlinstancedatetime.AddHours( - $graceperiod)
if ($NotContactable -contains $psitem) {
Context "Testing last log backups on $psitem" {
It "Can't Connect to $Psitem" {
$true | Should -BeFalse -Because "The instance should be available to be connected to!"
}
}
}
else {
Context "Testing last log backups on $psitem" {
@($InstanceSMO.Databases.Where{ (-not $psitem.IsSystemObject) -and $Psitem.CreateDate.ToUniversalTime() -lt $oldestbackupdateallowed -and $(if ($Database) { $PsItem.Name -in $Database }else { $ExcludedDatabases -notcontains $PsItem.Name }) }).ForEach{
if ($psitem.RecoveryModel -ne "Simple") {
if ($psitem.AvailabilityGroupName) {
$agReplicaRole = $InstanceSMO.AvailabilityGroups[$psitem.AvailabilityGroupName].LocalReplicaRole
}
else {
$agReplicaRole = $null
}
$skip = ($psitem.Status -match "Offline") -or ($psitem.IsAccessible -eq $false) -or ($psitem.Readonly -eq $true -and $skipreadonly -eq $true) -or ($agReplicaRole -eq 'Secondary' -and $skipsecondaries -eq $true)
It -Skip:$skip "Database $($psitem.Name) log backups should be less than $maxlog minutes old on $($psitem.Parent.Name)" {
if ($psitem.AvailabilityGroupName -and $getbackuphistoryallreplicas) {
$AGReplicas = ($InstanceSMO | Get-DbaAvailabilityGroup -AvailabilityGroup $psitem.AvailabilityGroupName).AvailabilityReplicas.Name
$LastLogBackup = ((($AGReplicas | Get-DbaLastBackup -Database $psitem.Name) | select-object @{Label = "LastLogBackup" Expression = { if ($_.LastLogBackup) { $_.LastLogBackup } else { [datetime]::ParseExact('01/01/1900', 'dd/MM/yyyy', $null) } } }).LastLogBackup.ToUniversalTime() | Measure-Object -Max).Maximum
$LastLogBackup | Should -BeGreaterThan $sqlinstancedatetime.AddMinutes( - ($maxlog) + 1) -Because "Taking regular backups is extraordinarily important"
}
else {
$psitem.LastLogBackupDate.ToUniversalTime() | Should -BeGreaterThan $sqlinstancedatetime.AddMinutes( - ($maxlog) + 1) -Because "Taking regular backups is extraordinarily important"
}
}
}
}
}
}
}