SQL Server backup age (per database) powershell

Download sql-backup-age.ps1

Queries msdb.dbo.backupset on a SQL Server instance and reports the age in seconds of each user database's most recent FULL backup. Plus the worst-age across all dbs for a single capture.value check that covers the whole instance.

Placeholders only. Before running, replace YOUR_URL with your capture endpoint's POST URL . (Open this page from your capture object to have these auto-filled.)

# sql-backup-age.ps1 — newest FULL-backup age per database.
# Requires the SqlServer PowerShell module (Install-Module SqlServer -Scope CurrentUser)
# and Windows auth or a SQL login with VIEW SERVER STATE rights on msdb.

$Url        = "YOUR_URL/sqlbackup"
$ServerName = "localhost"   # or "HOST\INSTANCE"

$Query = @"
SELECT
  d.name AS DatabaseName,
  ISNULL(DATEDIFF(SECOND, MAX(b.backup_finish_date), GETUTCDATE()), 999999999) AS AgeSeconds
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b
  ON b.database_name = d.name AND b.type = 'D'
WHERE d.database_id > 4   -- skip master / model / msdb / tempdb
GROUP BY d.name
"@

try {
  $Rows = Invoke-Sqlcmd -ServerInstance $ServerName -Query $Query -ErrorAction Stop
} catch {
  $Body = (@{ hostname = $env:COMPUTERNAME; error = "$($_.Exception.Message)" }) | ConvertTo-Json -Compress
  Invoke-RestMethod -Uri $Url -Method Post -Body $Body -ContentType "application/json" -TimeoutSec 15 | Out-Null
  exit 1
}

$Stats    = @{}
$WorstAge = 0
foreach ($r in $Rows) {
  $age = [int]$r.AgeSeconds
  $Stats[$r.DatabaseName] = $age
  if ($age -gt $WorstAge) { $WorstAge = $age }
}

$Body = (@{
  hostname           = $env:COMPUTERNAME
  server             = $ServerName
  databases          = $Stats
  worst_age_seconds  = $WorstAge
  db_count           = $Rows.Count
}) | ConvertTo-Json -Compress -Depth 5

Invoke-RestMethod -Uri $Url -Method Post -Body $Body `
  -ContentType "application/json" -TimeoutSec 15 | Out-Null

Recommended pairing

Add a capture.value check to this capture object.
json_path = worst_age_seconds, op = >, threshold = 90000 (~25 hours — gives a daily backup some slack). Or per-DB: json_path = databases.MyDb, op = >, threshold = 90000.

What is the filename?

sql-backup-age.ps1 — this is the suggested name for the downloaded file. Rename freely if you prefer.