SQL Server: Backup-Strategie, Automatisierung und Wiederherstellung

Richtet die Sicherung einer installierten SQL Server-Instanz ein (Linux oder Windows; einzelne Instanz, FCI oder AG-Replicas) und stellt aus der Sicherung wieder her. Sicherung: Wahl des Wiederherstellungsmodells, wöchentlich Full + täglich Differential + Log-Sicherung alle n Minuten, COMPRESSION, CHECKSUM, Verschlüsselung mit Zertifikat, Aufbewahrung und Zeitplanung (SQL Server Agent-Jobs in T-SQL oder Ola Hallengren; unter Express Task Scheduler / cron), einmalige COPY_ONLY-Sicherung, Prüfung der Sicherungen und der Sicherungskette. Wiederherstellung: Restore der Full- / Differential- / Log-Kette, Rückkehr zu einem bestimmten Zeitpunkt mit STOPAT (PITR), markierte Transaktion, Restore unter anderem Namen oder auf einen anderen Server mit WITH MOVE, Tail-Log + Restore bei Verlust einer Datendatei, Page Restore, Restore von master / msdb / model, Login-Zuordnung (verwaiste Benutzer) und Laden des Zertifikats für eine verschlüsselte Sicherung.

Wähle auf der Szenariokarte, was du tun willst; du kannst auch die Fehlernummer (z. B. 824, 4305, 33111, 18456) in die Katalogsuche eingeben und direkt zum passenden Szenario springen. Um eine Tabelle oder ein Abfrageergebnis in eine Datei zu exportieren (Datenübernahme, keine Sicherung), siehe Export mit bcp.

Topologie: 1 Server, Failoverclusterinstanz oder Always On AG-Replicas (Restore auf einen anderen Server: Quelle + Ziel)

Szenarien

• Backup-Strategie einrichten (geplante Sicherungen + Aufbewahrung) — Neuer Server oder Datenbank ohne regelmäßige Sicherung: Full- / Differential- / Log-Jobs, Aufbewahrung und Überwachung werden eingerichtet.
• Einmalige Sicherung erstellen (z. B. COPY_ONLY vor einer Änderung) — Eine einzelne Sicherung vor einer Änderung, einem Patch oder Upgrade oder als Kopie für eine Testumgebung; COPY_ONLY unterbricht die Kette nicht.
• Sicherungen prüfen (Verlauf, VERIFYONLY, Test-Restore) — Um nachzuweisen, dass sich die Sicherungen tatsächlich wiederherstellen lassen: Sicherungsverlauf, VERIFYONLY, HEADERONLY / FILELISTONLY und Test-Restore in eine temporäre Datenbank + CHECKDB.
• Ist die Sicherungskette intakt? (LSN-Lücke, fehlende Datei) — Vor einem Restore oder als regelmäßige Kontrolle: rekonstruiert die Kette aus dem msdb-Verlauf und findet LSN-Lücken, auf dem Datenträger fehlende Dateien und dazwischen erstellte Sicherungen. Ändert nichts.
• Datenbank aus der Sicherung wiederherstellen (Kette, STOPAT / STOPATMARK, MOVE / REPLACE, anderer Server) — Daten wurden gelöscht oder falsch aktualisiert, eine Kopie der Datenbank wird gebraucht oder sie zieht auf einen anderen Server um; die Instanz läuft. Full- + Differential- + Log-Kette, ein bestimmter Zeitpunkt oder eine markierte Transaktion, anderer Name / anderes Verzeichnis, mit Tail-Log bis zum letzten Moment.
• Datenbank lässt sich nicht öffnen: Datendatei fehlt oder ist beschädigt (Tail-Log + Restore) — Die Datenbank ist RECOVERY_PENDING oder SUSPECT, .mdf/.ndf ist nicht lesbar, die Instanz läuft aber: zuerst wird mit NO_TRUNCATE ein Tail-Log gesichert, dann bis zum letzten Moment wiederhergestellt. Die Datenbank ist bis zum Abschluss nicht verfügbar.
• Einige Seiten beschädigt (Msg 823 / 824): Page Restore — Abfragen oder CHECKDB melden Fehler auf bestimmten Seiten (msdb.dbo.suspect_pages): nur die beschädigten Seiten werden aus der Sicherung wiederhergestellt. Unter Enterprise/Developer bleibt die Datenbank online, unter Standard/Express ist sie kurz offline.
• master-Datenbank wiederherstellen (Einzelbenutzermodus) — master ist beschädigt oder Logins / Verbindungsserver / Servereinstellungen sind verloren: die Instanz wird im Einzelbenutzermodus (-m) gestartet, master wird wiederhergestellt, SQL Server fährt sich danach selbst herunter. Erfordert eine Downtime; startet die Instanz gar nicht, werden zuerst die Systemdatenbanken neu erstellt.
• msdb- oder model-Datenbank wiederherstellen — Jobs, Sicherungsverlauf, Database Mail (msdb) oder die Vorlage für neue Datenbanken (model) sind beschädigt / verloren: Restore mit REPLACE aus einer Sicherung desselben Builds; der Agent steht kurz still.
• Benutzer können sich nach dem Restore nicht anmelden (verwaiste Benutzer, Login-Zuordnung) — Die Datenbank wurde auf einen anderen Server verschoben oder dort wiederhergestellt, die Anwendung erhält 'Login failed': die Logins werden mit SID und Kennwort-Hash von der Quelle übertragen, verbliebene Benutzer werden einem Login zugeordnet. Die Datenbank bleibt online.
• Verschlüsselte Sicherung lässt sich nicht öffnen (Zertifikat fehlt, Msg 33111) — Die Sicherung (oder die Sicherung einer TDE-Datenbank) ist mit einem Zertifikat verschlüsselt, das auf diesem Server fehlt: der Thumbprint wird ermittelt, das Zertifikat mit dem privaten Schlüssel geladen und die Sicherung geprüft. Bestehende Datenbanken bleiben unberührt.

Vor dem Start

• RPO (tolerierbarer Datenverlust) und RTO (akzeptable Ausfallzeit) sind schriftlich mit dem Fachbereich vereinbart. Gewählter Plan: …höchstens … Minuten Datenverlust…Rückkehr zur letzten Full-/Differential-Sicherung (SIMPLE: bis zu einem Tag Datenverlust)…; die Restore-Dauer wird mit einem Test-Restore der größten Datenbank gemessen.
• SQL Server läuft; mssql-tools18 (sqlcmd) ist installiert und … ist sysadmin. Lies vor dem Ausführen des Skripts das Kennwort ohne Bildschirmausgabe in eine Variable ein: read -rsp 'Kennwort: ' SQLCMDPASSWORD; export SQLCMDPASSWORD und sudo --preserve-env=SQLCMDPASSWORD bash <skript>.sh.
• PowerShell wurde als Administrator geöffnet (Start → „PowerShell“ → Rechtsklick → Run as administrator; in der Titelleiste steht Administrator:), mit einem Windows-Konto, das in SQL Server sysadmin ist; sqlcmd ist im PATH.
• Das Sicherungsverzeichnis (…) liegt auf einem anderen Datenträger als die Daten und hat genug freien Platz für mindestens eine Woche Sicherungen (grob: belegte Datengröße × 2 + tägliche Änderung × Anzahl Tage; mit Komprimierung meist 3- bis 5-mal weniger).
• Das Sicherungsverzeichnis (…) liegt auf einem anderen Datenträger als die Daten und hat genug freien Platz für mindestens eine Woche Sicherungen (grob: belegte Datengröße × 2 + tägliche Änderung × Anzahl Tage; mit Komprimierung meist 3- bis 5-mal weniger).
• Die Größen der Datenbanken sind bekannt; das Sicherungsfenster (die Dauer einer Full-Sicherung) überschneidet sich nicht mit den Geschäftszeiten.
• Es ist festgelegt, wie die Sicherungen vom Server weg kopiert werden (anderer Server, NAS, Objektspeicher, Backup-Software). Eine Sicherung auf demselben Datenträger nützt bei Verlust des Datenträgers oder Servers nichts.
• Die SQL Server-Version des Restore-Ziels ist gleich oder neuer als die Version, auf der gesichert wurde. Eine Sicherung aus einer neueren Version lässt sich nicht auf einer älteren wiederherstellen (z. B. 2022 → 2019 geht nicht).
• Das Anwendungsteam ist informiert und die Anwendung gestoppt: alle Verbindungen zur überschriebenen Datenbank werden getrennt, und die Datenbank ist während des Restore nicht nutzbar.
• Auf dem Ziel existieren die Verzeichnisse für Daten (…) und Log (…), Eigentümer ist mssql, und es ist freier Platz in Höhe der Dateigrößen der wiederherzustellenden Datenbank vorhanden (nicht die Sicherungsgröße; die Size-Werte aus FILELISTONLY).
• Auf dem Ziel existieren die Verzeichnisse für Daten (…) und Log (…), das SQL Server-Dienstkonto hat Schreibrechte, und es ist freier Platz in Höhe der Dateigrößen der wiederherzustellenden Datenbank vorhanden.
• Die Quelle (…) ist vom Zielserver (…) …per SSH (root oder ein Benutzer mit sudo)…über die administrative SMB-Freigabe (…)… erreichbar; die Sicherungsdateien lassen sich kopieren.

Phasen der Anleitung

• Aktueller Zustand — Datenbanken und letzte Sicherungen auflisten
• Backup-Strategie — Sicherungsverzeichnis vorbereiten · Sicherungsverzeichnis vorbereiten · Wiederherstellungsmodell festlegen (…) · AG-Sicherungseinstellung festlegen (…) · Bevorzugtes Replica prüfen · Zertifikat erstellen und sichern (Verschlüsselung) …
• Einmalige Sicherung — …COPY_ONLY-Full-Sicherung erstellen…Full-Sicherung erstellen…Differential-Sicherung erstellen…Log-Sicherung erstellen… (…) · Sicherungsverlauf prüfen (msdb.dbo.backupset)
• Restore — Restore-Kette aus dem Sicherungsverlauf ermitteln · Sicherungsdateien auf den Zielserver kopieren · Sicherungsdateien auf den Zielserver kopieren · Zertifikat auf den Zielserver laden · Zertifikat auf den Zielserver laden · Sicherungsdateien untersuchen (VERIFYONLY, HEADERONLY, FILELISTONLY) …
• Prüfung der Sicherung — Sicherungsverlauf prüfen (msdb.dbo.backupset) · Sicherungsdatei untersuchen (VERIFYONLY, HEADERONLY, FILELISTONLY) · Test-Restore durchführen und DBCC CHECKDB ausführen
• Prüfung der Sicherungskette — Diagnose: Zustand der Kette (Datenbank + msdb-Verlauf) · Liegen die Sicherungsdateien auf dem Datenträger? · Liegen die Sicherungsdateien auf dem Datenträger? · Jede Sicherung der Kette prüfen (VERIFYONLY) · Danach: bei unterbrochener Kette eine neue Full-Sicherung erstellen
• Datendatei fehlt / beschädigt: Tail-Log + Restore — Diagnose: Zustand der Datenbank und ihrer Dateien · Diagnose: liegen die Dateien auf dem Datenträger, ist die Logdatei intakt? · Diagnose: liegen die Dateien auf dem Datenträger, ist die Logdatei intakt? · Sicherheitsnetz: verbliebene Dateien kopieren · Sicherheitsnetz: verbliebene Dateien kopieren · Tail-Log sichern (NO_TRUNCATE) …
• Page Restore (beschädigte Seiten) — Diagnose: beschädigte Seiten finden (suspect_pages, CHECKDB) · Diagnose: zu welcher Tabelle / welchem Index gehört die Seite? · Sicherheitsnetz: Log sichern, Log-Sicherungsjobs anhalten · Restore-Reihenfolge aus dem msdb-Verlauf ermitteln (PAGE) · Tail-Log sichern (NORECOVERY): die Datenbank geht offline · Beschädigte Seiten wiederherstellen und Log-Sicherungen einspielen (NORECOVERY) …
• master restore — Diagnose: Sicherungsdatei, Version und was verloren geht · Diagnose: warum startet SQL Server nicht? · Diagnose: warum startet SQL Server nicht? · Systemdatenbanken neu erstellen (master startet nicht) · Systemdatenbanken neu erstellen (master startet nicht) · SQL Server im Einzelbenutzermodus starten …
• Restore von msdb / model — Diagnose: Zustand, Version und Sicherungsdatei · Sicherheitsnetz: aktuelle Datenbank … sichern · SQL Server Agent stoppen · Datenbank … wiederherstellen (REPLACE) · SQL Server Agent starten · Prüfen
• Login-Zuordnung (verwaiste Benutzer) — Diagnose: verwaiste Benutzer finden (Ziel) · Login-Skript auf der Quelle erzeugen (SID + Kennwort-Hash) · Login-Skript auf der Quelle erzeugen (SID + Kennwort-Hash) · Login-Skript auf das Ziel kopieren und ausführen · Login-Skript auf das Ziel kopieren und ausführen · Verbliebene Benutzer Logins zuordnen (ALTER USER WITH LOGIN) …
• Verschlüsselte Sicherung: Zertifikat laden — Diagnose: Zertifikate und Master Key auf diesem Server · Diagnose: Thumbprint der Zertifikatdatei · Diagnose: Thumbprint der Zertifikatdatei · Zertifikat mit privatem Schlüssel laden · Zertifikat mit privatem Schlüssel laden · Prüfen: die Sicherung ist jetzt lesbar …