Sql-server – How to check backup against SQL AG by Get-DbaLastBackup

availability-groupsbackupsql server

I use the following PowerShell script to check if the last full backup successful daily:

$SQL_Server_list =  Get-Content D:\Tools\DBA\SQLPRD_FINAL.txt
$SQL_Server_list | Get-DbaLastBackup | Where-Object { ($(get-date $_.LastFullBackup) -lt (Get-Date).AddDays(-1)) } | Format-Table -AutoSize | Out-File D:\Tools\DBA\SQL_Check_PRD_Backup.txt -Width 255

It works fine except the SQL AG.

I have SQLAG_Listener, SQLAG_NODE1, and SQLAG_NODE2 in SQLPRD_FINAL.txt. I set full backup prefer secondary on AG and I do get the full backup from SQLAG_NODE2.

But I still get "database not fully backed up in 24 Hours" alerts from SQLAG_Listener and SQLAG_NODE1. I cannot exclude the SQLAG_Listener or SQLAG_NODE1 since the fail-over might happen.

How can I avoid this?

BTW: If I do the full backup against primary side, I will get the alerts from secondary side instead…

Thanks in advance!

Best Answer

Get-DbaLastBackup currently doesn't support the ability to check the last backup on a database that's in an availability group. You will either need to modify the GetDbaLastBackup code or use another script that queries the servers in the availability group, collects the last backup information from each of them, and returns the latest date that was collected.

This would certainly be a nice feature--even some of the big money SQL Server monitoring tools don't provide a nice mechanism for monitoring transaction log backups on availability groups.