Skip to content

Get-DbaLastBackup - Add -ExcludeReplica switch for AlwaysOn preferred backup replica filtering #10240

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
potatoqualitee merged 1 commit into from
Mar 16, 2026
Merged

Get-DbaLastBackup - Add -ExcludeReplica switch for AlwaysOn preferred backup replica filtering #10240

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
24 changes: 24 additions & 0 deletions public/Get-DbaLastBackup.ps1
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -25,6 +25,12 @@ function Get-DbaLastBackup {
Excludes specific databases from the backup history check. Commonly used to skip system databases or test databases.
Use this when you want to check most databases but exclude certain ones like tempdb, development databases, or databases with known backup exemptions.

.PARAMETER ExcludeReplica
When this switch is enabled, databases in an AlwaysOn Availability Group where the current SQL Server instance
is not the preferred backup replica are excluded from the results.
This is useful when running Get-DbaLastBackup against multiple servers in an availability group to avoid
false positives where secondary replicas appear to have missing backups because backups are taken on the preferred replica only.

.PARAMETER EnableException
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Expand Down Expand Up @@ -94,6 +100,13 @@ function Get-DbaLastBackup {
PS C:\> Get-DbaLastBackup -SqlInstance ServerA\sql987 | Where-Object { $_.LastFullBackupIsCopyOnly -eq $true }

Filters for the databases that had a copy_only full backup done as the last backup.

.EXAMPLE
PS C:\> Get-DbaLastBackup -SqlInstance sql2019, sql2019b -ExcludeReplica

Returns last backup information for all databases on both instances, excluding availability group databases
where the current instance is not the preferred backup replica. This prevents false alerts when secondary
replicas appear to have no recent backups because backups are only performed on the preferred replica.
#>
[CmdletBinding()]
param (
Expand All @@ -103,6 +116,7 @@ function Get-DbaLastBackup {
$SqlCredential,
[object[]]$Database,
[object[]]$ExcludeDatabase,
[switch]$ExcludeReplica,
[switch]$EnableException
)
begin {
Expand Down Expand Up @@ -131,6 +145,16 @@ function Get-DbaLastBackup {
if ($ExcludeDatabase) {
$dbs = $dbs | Where-Object Name -NotIn $ExcludeDatabase
}

if ($ExcludeReplica -and $server.IsHadrEnabled) {
Write-Message -Level Verbose -Message "Excluding non-preferred backup replicas for $instance"
$notPreferredQuery = "SELECT DB_NAME(database_id) AS DatabaseName FROM sys.databases WHERE sys.fn_hadr_backup_is_preferred_replica(DB_NAME(database_id)) = 0"
$notPreferredDbs = ($server.Query($notPreferredQuery)).DatabaseName
if ($notPreferredDbs) {
$dbs = $dbs | Where-Object { $_.Name -notin $notPreferredDbs }
}
}

# Get-DbaDbBackupHistory -Last would make the job in one query but SMO's (and this) report the last backup of this type regardless of the chain
$FullHistory = Get-DbaDbBackupHistory -SqlInstance $server -Database $dbs.Name -LastFull -IncludeCopyOnly -Raw
$DiffHistory = Get-DbaDbBackupHistory -SqlInstance $server -Database $dbs.Name -LastDiff -IncludeCopyOnly -Raw
Expand Down
1 change: 1 addition & 0 deletions tests/Get-DbaLastBackup.Tests.ps1
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -15,6 +15,7 @@ Describe $CommandName -Tag UnitTests {
"SqlCredential",
"Database",
"ExcludeDatabase",
"ExcludeReplica",
"EnableException"
)
Compare-Object -ReferenceObject $expectedParameters -DifferenceObject $hasParameters | Should -BeNullOrEmpty
Expand Down
Loading