SQL-Backups unabhängig prüfen:
Erfolgsmeldung reicht nicht aus

SQL-Backups selbst prüfen

Plus Speicherprognose

Foto: rock_rock / Pixabay

Der Wartungsplan meldet Erfolg, das Backup-Laufwerk ist trotzdem voll: Eine unabhängige Prüfung rechnet sogar aus, wann das passiert.

Auf einen Blick

• Ein Wartungsplan sieht seine eigenen Fehler nicht. Cleanup, Aufbewahrung und Platz passen trotz Erfolgsmeldung nicht zusammen.
• Der Watchdog liest drei Quellen. Dazu zählen die msdb-Historie, das Dateisystem und die Laufwerke.
• Zwei PRTG-Sensoren zeigen das Ergebnis. Der eine meldet den Status, der andere die Grösse jeder Datenbank samt Prognose.
• Der PRTG-Login darf nur lesen. Er braucht SELECT auf zwei Views, die Zugangsdaten liegen in den Geräte-Einstellungen.

Warum der Wartungsplan nicht genügt

Ein Backup-Laufwerk lief voll, obwohl alle Wartungspläne „erfolgreich“ meldeten. Aufbewahrung, Cleanup und Plattenplatz waren nicht aufeinander abgestimmt. Der Plan bewertete nur seine eigene Arbeit, und die war formal in Ordnung.

Eine Prüfung innerhalb des Wartungsplans übersieht genau solche Fehler. Gebraucht wird deshalb ein unabhängiger Blick auf drei Dinge: die Historie in msdb, die Dateien im Dateisystem und die Auslastung der Platten. Die Tabelle backupset in msdb führt jedes Backup mit Typ und Zeitpunkt. Was dort steht, muss noch lange nicht als Datei vorliegen.

Der Watchdog liefert drei Auswertungen. Er prüft den Status der Backups, zeigt die Grösse jeder Datenbank als Verlauf und rechnet eine Prognose für den Plattenplatz. Dazu kommen zwei Analysewerkzeuge: ein Export der bestehenden Wartungspläne und ein Report über doppelte, überflüssige und verwaiste Backup-Dateien, wahlweise mit Löschen.

So prüft der SQL-Server-Watchdog

Auf dem SQL Server läuft täglich ein Agent-Job. Er hat zwei Schritte. Der erste sammelt per PowerShell Fakten über Dateien und Laufwerke. Der zweite wertet diese Fakten zusammen mit msdb aus und schreibt das Ergebnis in die Datenbank BackupReporting. Dort liegen auch Konfiguration, Ausschlüsse, Kanalzuordnung, Views und die Berichtsprozedur.

PRTG liest nur die Views. Die Sensoren vom Typ EXE/Script Advanced laufen auf der PRTG-Probe und wandeln die Daten in Kanäle um. Einfache Lookups übersetzen Zahlen in OK, Hinweis und Fehler.

Die Prüfungen im Überblick:

Der Check FACTS ist ein Wächter für den Wächter. Läuft die Dateiprüfung nicht mehr, meldet der Watchdog das selbst.

Beispiel: Fakten sammeln

Das gekürzte Skript prüft zuerst die Dateien je Datenbank-Ordner. Danach fragt es in msdb nach der Datei des letzten echten Full-Backups und kontrolliert, ob sie noch existiert.

$folders = Get-ChildItem -LiteralPath $BackupRoot -Recurse -File |
    Where-Object { $_.Extension -in '.bak', '.dif', '.trn' } |
    Group-Object { $r = $_.FullName.Substring($rootLen); if ($r.Contains('\')) { $r.Split('\')[0] } else { '(root)' } } |
    ForEach-Object {
        [pscustomobject]@{
            Ordner = $_.Name; Dateien = $_.Count
            GB = [math]::Round(($_.Group | Measure-Object Length -Sum).Sum / 1GB, 2)
            Aelteste = ($_.Group | Measure-Object LastWriteTime -Minimum).Minimum
        }
    }

$cmd.CommandText = @"
SELECT bs.database_name, bmf.physical_device_name
FROM (SELECT database_name, MAX(backup_set_id) id FROM msdb.dbo.backupset
      WHERE type = 'D' AND is_copy_only = 0 GROUP BY database_name) x
JOIN msdb.dbo.backupset bs ON bs.backup_set_id = x.id
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE bmf.device_type = 2
"@

Dabei filtert die Abfrage Kopien aus (is_copy_only = 0). Sie fragt nur nach dem letzten echten Full. Anschliessend prüft das Skript per Test-Path, ob die Datei noch da ist, und liest ihre Grösse. Zum Schluss kommen die Laufwerke dazu: Gesamtgrösse und freier Platz.

Im SQL-Agent kommen Laufwerke wie V: und L: als ein einziger String an. Deshalb teilt das Skript sie vorab an Komma und Semikolon auf.

Beispiel: Grösse pro Datenbank für PRTG

Der zweite Sensor liefert für jede Datenbank einen Kanal und eine Summe. PRTG erwartet auf der Konsole ausschliesslich XML. Jeder Fehler wird deshalb als XML-Fehlermeldung ausgegeben.

function Write-PrtgError([string] $msg) { Write-Output "<prtg><error>1</error><text>$(Esc $msg)</text></prtg>"; exit 0 }

function Add-Result($sb, [string] $name, [double] $value) {
    [void]$sb.AppendLine("  <result><channel>$(Esc $name)</channel><value>$($value.ToString('F2', $inv))</value><unit>Custom</unit><customunit>GB</customunit><float>1</float></result>")
}

$sum = [double](($rows | Where-Object { $null -ne $_.gb } | Measure-Object gb -Sum).Sum)
Add-Result $sb 'Summe aller Datenbanken' $sum
foreach ($r in $rows) {
    $name = if ($r.database_name.Length -gt 50) { $r.database_name.Substring(0, 50) } else { $r.database_name }
    Add-Result $sb $name ([double]($(if ($null -eq $r.gb) { 0 } else { $r.gb })))
}

Der Kanalname entspricht dem Datenbanknamen. PRTG ordnet Verläufe über den Namen zu. Benennst du einen Kanal um, legt PRTG einen neuen an und die Historie bleibt zurück. Mit dem Schalter -DemoData testest du den Sensor, ohne eine SQL-Verbindung zu brauchen.

Einsatz in sieben Schritten

1. Auf dem SQL Server einen Ordner für die Werkzeuge anlegen und das Sammelskript hineinkopieren.
2. Das Setup-Skript ausführen. Es legt Datenbank, Tabellen, Prüfprozedur, Views und das Leserecht für den PRTG-Login an. Die SQL-Dateien nutzen SQLCMD-Variablen, deshalb aktivierst du in SSMS vorher den SQLCMD-Modus.
3. Den Agent-Job täglich nach dem Full-Backup einplanen und einmal manuell starten.
4. Mit SELECT * FROM BackupReporting.dbo.v_BackupCheckLatest; kontrollieren, ob Ergebnisse vorliegen.
5. Die beiden Sensor-Skripte in den Ordner für EXE/XML-Sensoren der PRTG-Probe kopieren, die Lookups in den Lookup-Ordner.
6. Die Sensoren anlegen, zum Beispiel mit -SqlServer "SQL01" -SqlUser "%dbuser" -SqlPassword "%dbpassword" -RetentionDays 21, Intervall eine Stunde.
7. Vorher mit -DemoData testen.

Du brauchst SQL Server mit Agent und für die Einrichtung sysadmin-Rechte. Das Dienstkonto des Agents benötigt Leserecht auf den Backup-Ordner. Die Zugangsdaten des PRTG-Logins kommen aus den Geräte-Credentials von PRTG (%dbuser, %dbpassword) und nie aus dem Skript.

Wichtige Parameter

Typische Fehler

Grenzen

Vor dem Produktivbetrieb testest du gegen die echte Instanz. Die Grenzwerte liegen in einer Konfigurationstabelle. Die Prognose braucht mindestens drei volle Backup-Tage. Externe Sicherungen, etwa mit einer separaten Backup-Software, fliessen nicht ein. Ein Grössen-Sensor deckt höchstens 45 Datenbanken ab, und jede Instanz braucht einen eigenen Aufbau. Das Löschskript für Altbackups läuft erst nach fachlicher Freigabe mit -Delete. Die Beispiele sind gekürzt und gehören vor dem Einsatz in ein Testlab. Wer ähnliche Grundlagen für andere Reports sucht, findet sie im Artikel PowerShell-Projektvorlage: jedes Inventory gleich aufgebaut.

Einordnung: Was das für SQL- und Monitoring-Admins heisst

Wer einem Wartungsplan blind vertraut, merkt Fehler erst bei der Wiederherstellung. Der unabhängige Watchdog verschiebt diesen Moment auf den Tag, an dem der Fehler entsteht. Die Prognose zählt dabei am meisten, weil sie Platzmangel Wochen vorher zeigt statt am Wochenende. Für MSPs mit PRTG lohnt sich das Muster, weil die Sensoren pro Instanz gleich aufgebaut sind. Ein Backup gilt erst dann als gut, wenn du eine Wiederherstellung getestet hast. Der Watchdog ersetzt diesen Test nicht, er sorgt dafür, dass vorher nichts fehlt.

Fazit

Ein Wartungsplan, der Erfolg meldet, sagt wenig über Dateien, Aufbewahrung und Platz. Ein unabhängiger Agent-Job mit msdb-Auswertung und zwei PRTG-Sensoren schliesst die Lücke. Er meldet fehlende Dateien, auffälligen Zuwachs und die Tage bis zur vollen Platte. Starte mit einer Instanz und stimme die Grenzwerte auf deine Umgebung ab.

Geschrieben von Andreas Brauckmann