Check SQL Server backups on Windows

What you'll know

• The backup job ran, on time, and succeeded. Two steps added to the SQL Server Agent job call a heartbeat URL: one when the backup succeeds, another when it fails. If neither arrives on schedule, that's a problem too.
• Every database has a recent full backup. A script asks SQL Server's own backup history in msdb how long ago each database was last backed up in full. That counts every full backup SQL Server recorded, whatever took it and wherever it went: a maintenance plan, Ola Hallengren's scripts, or a backup to a share or to URL.
• Optionally, the backup files are there and not empty. The agent can watch a backup folder for a new .bak file of a reasonable size.

The heartbeat watches the job, and the history watches the databases. A job can succeed while backing up only the databases it was told about, and a database added last month that nobody added to the job never appears in its log. The history check lists it as never backed up.

What this can't tell you: whether a backup restores, or whether a copy left the machine. Only a test restore proves the first. Point a file check at the off-site copy for the second.

Before you start

• The SQL Server machine, added as a host in Gryphon.
• A SQL Server Agent job that takes the backups. On SQL Server Express, which has no SQL Server Agent, see Variations.
• The SQL Server Agent service's account must be able to reach https://gryphon.gocode.ca. The heartbeat is called from the job, not from the agent.
• For the history and file checks, the Gryphon agent on the same machine. The heartbeat works without it.

Steps

1Add the heartbeat

Open the host, go to Manage Services, choose Add service, and pick Heartbeat.

Once it's added, the check's row shows its URL, with a Copy button. Anyone who has the URL can report on this check, so treat it like a password. Until the first ping arrives, the check stays pending.

2Make the job report in

Run this in SQL Server Management Studio, with your job's name and your heartbeat URL in place of the examples. The URL appears twice, the second time with /fail on the end.

-- Adds two steps to a backup job whose backup is its only step: one that
-- tells Gryphon the backup worked, and one that tells it the backup failed.
DECLARE @job sysname = N'DatabaseBackup - USER_DATABASES - FULL';  -- your job's name

EXEC msdb.dbo.sp_add_jobstep
    @job_name = @job, @step_id = 2, @step_name = N'Tell Gryphon it worked',
    @subsystem = N'PowerShell',
    @command = N'$ErrorActionPreference = ''Stop''
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$url = ''https://gryphon.gocode.ca/hb/your-token''
Invoke-WebRequest -UseBasicParsing -TimeoutSec 10 -Uri $url | Out-Null',
    @retry_attempts = 3, @retry_interval = 1,
    @on_success_action = 1,   -- quit, reporting success
    @on_fail_action = 2;      -- quit, reporting failure

EXEC msdb.dbo.sp_add_jobstep
    @job_name = @job, @step_id = 3, @step_name = N'Tell Gryphon it failed',
    @subsystem = N'PowerShell',
    @command = N'$ErrorActionPreference = ''Stop''
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$url = ''https://gryphon.gocode.ca/hb/your-token/fail''
Invoke-WebRequest -UseBasicParsing -TimeoutSec 10 -Uri $url | Out-Null',
    @retry_attempts = 3, @retry_interval = 1,
    @on_success_action = 2,   -- quit, reporting failure: the backup failed
    @on_fail_action = 2;

-- The backup: on success go to step 2, on failure to step 3.
EXEC msdb.dbo.sp_update_jobstep
    @job_name = @job, @step_id = 1,
    @on_success_action = 4, @on_success_step_id = 2,
    @on_fail_action = 4, @on_fail_step_id = 3;

The backup stays step 1. When it succeeds, step 2 pings the heartbeat. When it fails, step 3 pings the /fail URL, and the job still ends as failed in its history. Each ping is retried three times, a minute apart. -UseBasicParsing and the TLS 1.2 line keep Windows PowerShell working under a service account and on older Windows Server releases.

This assumes the backup is the job's only step, as it is in Ola Hallengren's jobs and most hand-made ones. To add the steps by hand instead, use the job's Steps page: two new steps of type PowerShell, then step 1's Advanced page set to Go to step 2 on success and Go to step 3 on failure. A maintenance plan manages its own job, so for a plan, check the steps are still there after you next edit it, or rely on step 3's history check alone.

3Give the agent a login

On Windows, the agent runs as NT SERVICE\GryphonAgent. Skip this if you've already done it for Monitor SQL Server on Windows.

CREATE LOGIN [NT SERVICE\GryphonAgent] FROM WINDOWS;

Out of the box, that's enough. Every login can read msdb's backup history. If your server has been hardened so that it can't, the check will say The SELECT permission was denied on the object 'backupset', and this gives the agent that one table and nothing else:

USE msdb;
CREATE USER [NT SERVICE\GryphonAgent] FOR LOGIN [NT SERVICE\GryphonAgent];
GRANT SELECT ON dbo.backupset TO [NT SERVICE\GryphonAgent];

4Add the history script

Script checks are off until the agent is told which folder to run them from. In an administrator's PowerShell, open C:\ProgramData\Gryphon\agent.env in Notepad, uncomment the GWC_SCRIPTS_DIR line, and restart the agent:

GWC_SCRIPTS_DIR=C:\ProgramData\Gryphon\scripts

Restart-Service GryphonAgent
notepad C:\ProgramData\Gryphon\scripts\sql-backups.ps1

Paste this into the new file and save it:

# How long ago did each database last have a full backup? Read from msdb, so
# it counts every full backup SQL Server took, wherever it was written.
# Exit 0 healthy, 1 warning, 2 problem, 3 unknown.
$Server       = 'localhost'   # a named instance is localhost\NAME
$WarnHours    = 26
$ProblemHours = 30
$Skip         = @()           # names of databases you don't back up, e.g. @('Scratch')

$ErrorActionPreference = 'Stop'
$query = @'
SELECT d.name,
       DATEDIFF(MINUTE, MAX(b.backup_finish_date), GETDATE()) / 60.0 AS hours
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
       ON b.database_name = d.name AND b.type = 'D'
WHERE d.name <> 'tempdb'
  AND d.state_desc = 'ONLINE'
  AND d.source_database_id IS NULL
GROUP BY d.name
ORDER BY d.name
'@

$late = @()
$overdue = @()
$count = 0
try {
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=$Server;Database=master;Integrated Security=SSPI;Connect Timeout=5;Application Name=Gryphon"
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandTimeout = 5
    $cmd.CommandText = $query
    $reader = $cmd.ExecuteReader()
    while ($reader.Read()) {
        $name = [string]$reader['name']
        if ($Skip -contains $name) { continue }
        $count++
        if ($reader.IsDBNull(1)) {
            $overdue += "$name (never)"
            continue
        }
        $hours = [double]$reader['hours']
        if ($hours -ge $ProblemHours) {
            $overdue += '{0} ({1:N0} h)' -f $name, $hours
        } elseif ($hours -ge $WarnHours) {
            $late += '{0} ({1:N0} h)' -f $name, $hours
        }
    }
    $conn.Close()
} catch {
    $e = $_.Exception
    if ($e.InnerException) { $e = $e.InnerException }
    Write-Output ('Cannot read the backup history on {0}: {1}' -f $Server, ($e.Message -split '\r?\n')[0])
    exit 3
}

if ($overdue.Count -gt 0) {
    Write-Output ('No full backup in {0} hours: {1}' -f $ProblemHours, (($overdue + $late) -join ', '))
    exit 2
}
if ($late.Count -gt 0) {
    Write-Output ('No full backup in {0} hours: {1}' -f $WarnHours, ($late -join ', '))
    exit 1
}
if ($count -eq 0) {
    Write-Output 'No databases to check.'
    exit 3
}
Write-Output "All $count databases have a full backup from the last $WarnHours hours."
exit 0

The four lines at the top are yours to change. Hours are counted from when each backup finished, and 26 and 30 fit a nightly backup with room for one slow night. Put any database you deliberately don't back up in $Skip, by name. A database with no backup in its history is reported as never. If the history can't be read at all, the check is unknown rather than a problem, so a server that's down is reported once, by the check that watches the server.

Create the file in the folder, as above, rather than copying it in. A new file takes the folder's permissions. A file moved in from elsewhere keeps its own, and the agent refuses to run it.

5Add the history check

6Optionally, watch the files

If the backups land in a folder on this machine, or on a share it can see, the agent can check that the newest file in it is recent and not empty. Name the folder in agent.env, let the agent's account list it and its subfolders, and restart:

GWC_WATCH_DIRS=D:\Backups

icacls D:\Backups /grant "NT SERVICE\GryphonAgent:(CI)(RX)"
Restart-Service GryphonAgent

(CI)(RX) lets the agent list the folder and every folder under it, which is all it needs: it reads names, dates and sizes, and never opens a backup. Then add File freshness (agent):

A file check looks only at the files directly in its folder, not in subfolders. Ola Hallengren's scripts write each database to a folder of its own, so this is one check per database. Add it for the databases that matter most, or for the folder an off-site copy lands in.

Test it

1. Start the job by hand: EXEC msdb.dbo.sp_start_job N'DatabaseBackup - USER_DATABASES - FULL'; When it finishes, the heartbeat turns healthy within a minute, and the history check does on its next run. Check now runs it straight away.
2. Make a database nobody backs up: CREATE DATABASE GryphonTest; On its next run, the history check reads No full backup in 30 hours: GryphonTest (never). Gryphon then checks every minute, and alerts once three results in a row agree, so warn whoever gets the alerts first. Drop it afterwards with DROP DATABASE GryphonTest;.
3. To see a failed job reported, break step 1 for one run, for example by backing up to a folder that doesn't exist, and start the job. The heartbeat becomes a problem within a minute. Put the step back and run the job again, and the next good ping clears it.

Variations

Log backups

For databases in the full recovery model, copy the script to sql-log-backups.ps1, add it as a second check, and change these lines: log backups are type L, databases in the simple recovery model have none, and the thresholds are hours rather than days.

$WarnHours    = 1
$ProblemHours = 2
$Skip         = @('model')
...
       ON b.database_name = d.name AND b.type = 'L'
...
  AND d.source_database_id IS NULL
  AND d.recovery_model_desc <> 'SIMPLE'

model is in the full recovery model on most installations but never has its log backed up, so it's skipped. Change the words full backup in the three messages at the end to log backup too. A differential backup is type I.

SQL Server Express

Express has no SQL Server Agent, so the backup usually runs from Task Scheduler. Have the scheduled PowerShell script report the result itself:

$hb = 'https://gryphon.gocode.ca/hb/your-token'
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

sqlcmd -S localhost\SQLEXPRESS -E -b -Q "BACKUP DATABASE Sales TO DISK = 'D:\Backups\Sales.bak' WITH INIT"
if ($LASTEXITCODE -eq 0) { $url = $hb } else { $url = "$hb/fail" }
Invoke-WebRequest -UseBasicParsing -TimeoutSec 10 -Uri $url | Out-Null

sqlcmd -b exits with an error code when the backup fails, which sends the /fail ping. The history check works on Express unchanged.

Availability groups

Each replica keeps its own msdb, so the history check on one replica sees only the backups taken there. Run it on the replica your backups are taken from, and on the others put the group's databases in $Skip.