Vérifier les sauvegardes SQL Server sous Windows

Ce que vous saurez

• La tâche de sauvegarde s’est exécutée, à l’heure, et a réussi. Deux étapes ajoutées à la tâche SQL Server Agent appellent une URL de battement de cœur : l’une quand la sauvegarde réussit, l’autre quand elle échoue. Si ni l’une ni l’autre n’arrive à temps, c’est aussi un problème.
• Chaque base de données a une sauvegarde complète récente. Un script demande à l’historique de sauvegarde de SQL Server lui-même, dans msdb, à quand remonte la dernière sauvegarde complète de chaque base. Il compte toutes les sauvegardes complètes que SQL Server a enregistrées, quel que soit l’outil qui les a faites et leur destination : un plan de maintenance, les scripts d’Ola Hallengren, ou une sauvegarde vers un partage ou vers une URL.
• En option, les fichiers de sauvegarde sont là et ne sont pas vides. L’agent peut surveiller l’arrivée, dans un dossier de sauvegarde, d’un nouveau fichier .bak de taille raisonnable.

Le battement de cœur surveille la tâche, et l’historique surveille les bases de données. Une tâche peut réussir en ne sauvegardant que les bases qu’on lui a indiquées, et une base ajoutée le mois dernier que personne n’a ajoutée à la tâche n’apparaît jamais dans son journal. La vérification de l’historique la signale comme jamais sauvegardée (never).

Ce que cela ne vous dira pas : si une sauvegarde se restaure, ni si une copie a quitté la machine. Seule une restauration d’essai prouve la première. Pour la seconde, pointez une vérification de fichier sur la copie hors site.

Avant de commencer

• La machine SQL Server, ajoutée comme hôte dans Gryphon.
• Une tâche SQL Server Agent qui effectue les sauvegardes. Avec SQL Server Express, qui n’a pas SQL Server Agent, voyez Variantes.
• Le compte du service SQL Server Agent doit pouvoir joindre https://gryphon.gocode.ca. Le battement de cœur est appelé par la tâche, pas par l’agent.
• Pour les vérifications de l’historique et des fichiers, l’agent Gryphon sur la même machine. Le battement de cœur fonctionne sans lui.

Étapes

1Ajouter le battement de cœur

Ouvrez l’hôte, allez dans Services, choisissez Ajouter le service, puis Battement de cœur.

Une fois la vérification ajoutée, sa ligne affiche son URL, avec un bouton Copier. Quiconque a l’URL peut signaler des résultats à cette vérification : traitez-la donc comme un mot de passe. Jusqu’à l’arrivée du premier ping, la vérification reste en attente.

2Faire en sorte que la tâche se signale

Exécutez ceci dans SQL Server Management Studio, en remplaçant les exemples par le nom de votre tâche et l’URL de votre battement de cœur. L’URL figure deux fois, la seconde avec /fail à la fin.

-- 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;

La sauvegarde reste l’étape 1. Quand elle réussit, l’étape 2 envoie un ping au battement de cœur. Quand elle échoue, l’étape 3 envoie un ping à l’URL /fail, et la tâche figure tout de même comme échouée dans son historique. Chaque ping est réessayé trois fois, à une minute d’intervalle. -UseBasicParsing et la ligne TLS 1.2 permettent à Windows PowerShell de fonctionner sous un compte de service et sur les anciennes versions de Windows Server.

Cela suppose que la sauvegarde est la seule étape de la tâche, comme dans les tâches d’Ola Hallengren et la plupart de celles faites à la main. Pour ajouter plutôt les étapes à la main, utilisez la page Steps de la tâche : deux nouvelles étapes de type PowerShell, puis, dans la page Advanced de l’étape 1, Go to step 2 en cas de succès et Go to step 3 en cas d’échec. Un plan de maintenance gère sa propre tâche ; pour un plan, vérifiez donc que les étapes sont toujours là après votre prochaine modification, ou fiez-vous seulement à la vérification de l’historique (étapes 4 et 5).

3Donner une connexion à l’agent

Sous Windows, l’agent s’exécute sous NT SERVICE\GryphonAgent. Passez cette étape si vous l’avez déjà faite pour Surveiller SQL Server sous Windows.

CREATE LOGIN [NT SERVICE\GryphonAgent] FROM WINDOWS;

Par défaut, cela suffit. Toute connexion peut lire l’historique de sauvegarde de msdb. Si votre serveur a été renforcé au point de l’en empêcher, la vérification indiquera The SELECT permission was denied on the object 'backupset', et ceci donne à l’agent cette seule table, et rien d’autre :

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

4Ajouter le script d’historique

Les vérifications par script sont désactivées tant que l’agent ne sait pas dans quel dossier les exécuter. Dans PowerShell en mode administrateur, ouvrez C:\ProgramData\Gryphon\agent.env dans le Bloc-notes (Notepad), décommentez la ligne GWC_SCRIPTS_DIR et redémarrez l’agent :

GWC_SCRIPTS_DIR=C:\ProgramData\Gryphon\scripts

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

Collez ceci dans le nouveau fichier et enregistrez-le :

# 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

Les quatre lignes du haut sont à adapter. Les heures sont comptées à partir de la fin de chaque sauvegarde, et 26 et 30 conviennent à une sauvegarde nocturne, avec de la marge pour une nuit lente. Mettez dans $Skip, par son nom, toute base que vous ne sauvegardez pas délibérément. Une base sans aucune sauvegarde dans son historique est signalée avec never. Si l’historique est complètement illisible, la vérification passe à inconnu plutôt qu’à problème ; ainsi, un serveur en panne n’est signalé qu’une fois, par la vérification qui surveille le serveur.

Créez le fichier dans le dossier, comme plus haut, plutôt que de l’y copier. Un nouveau fichier prend les autorisations du dossier. Un fichier déplacé d’ailleurs garde les siennes, et l’agent refuse de l’exécuter.

5Ajouter la vérification de l’historique

6En option, surveiller les fichiers

Si les sauvegardes aboutissent dans un dossier de cette machine, ou sur un partage qu’elle voit, l’agent peut vérifier que le dernier fichier qui s’y trouve est récent et non vide. Indiquez le dossier dans agent.env, permettez au compte de l’agent de le lister, ainsi que ses sous-dossiers, et redémarrez :

GWC_WATCH_DIRS=D:\Backups

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

(CI)(RX) permet à l’agent de lister le dossier et tous ses sous-dossiers, et c’est tout ce qu’il lui faut : il lit les noms, les dates et les tailles, et n’ouvre jamais une sauvegarde. Ajoutez ensuite Fraîcheur de fichier (agent) :

Une vérification de fichier ne regarde que les fichiers situés directement dans son dossier, pas dans les sous-dossiers. Les scripts d’Ola Hallengren écrivent chaque base de données dans son propre dossier ; il faut donc une vérification par base. Ajoutez-la pour les bases qui comptent le plus, ou pour le dossier où arrive une copie hors site.

Faire un essai

1. Lancez la tâche à la main : EXEC msdb.dbo.sp_start_job N'DatabaseBackup - USER_DATABASES - FULL'; Quand elle se termine, le battement de cœur passe en bon état en moins d’une minute, et la vérification de l’historique à sa prochaine exécution. Vérifier maintenant l’exécute tout de suite.
2. Créez une base de données que personne ne sauvegarde : CREATE DATABASE GryphonTest; À sa prochaine exécution, la vérification de l’historique indique No full backup in 30 hours: GryphonTest (never). Gryphon la refait ensuite chaque minute et alerte dès que trois résultats de suite concordent ; prévenez donc d’abord les personnes qui reçoivent les alertes. Supprimez-la ensuite avec DROP DATABASE GryphonTest;.
3. Pour voir une tâche en échec signalée, faites échouer l’étape 1 le temps d’une exécution, par exemple en sauvegardant vers un dossier qui n’existe pas, puis lancez la tâche. Le battement de cœur passe en problème en moins d’une minute. Rétablissez l’étape et relancez la tâche : le prochain ping réussi efface le problème.

Variantes

Sauvegardes du journal

Pour les bases en mode de récupération complète, copiez le script sous le nom sql-log-backups.ps1, ajoutez-le comme deuxième vérification et modifiez ces lignes : les sauvegardes du journal sont de type L, les bases en mode de récupération simple n’en ont pas, et les seuils se comptent en heures plutôt qu’en jours.

$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'

La base model est en mode de récupération complète dans la plupart des installations, mais son journal n’est jamais sauvegardé ; elle est donc ignorée. Remplacez aussi les mots full backup par log backup dans les trois messages de la fin. Une sauvegarde différentielle est de type I.

SQL Server Express

Express n’a pas SQL Server Agent ; la sauvegarde s’exécute donc habituellement depuis le Planificateur de tâches (Task Scheduler). Faites en sorte que le script PowerShell planifié signale lui-même le résultat :

$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 se termine avec un code d’erreur quand la sauvegarde échoue, ce qui envoie le ping /fail. La vérification de l’historique fonctionne telle quelle avec Express.

Groupes de disponibilité

Chaque réplica a sa propre base msdb, si bien que la vérification de l’historique sur un réplica ne voit que les sauvegardes faites sur celui-ci. Exécutez-la sur le réplica où vos sauvegardes sont faites et, sur les autres, mettez les bases du groupe dans $Skip.