Sicherungsstrategie für SQL Server: Recovery Model und Log-Sicherungen

Sicherungsstrategie für SQL Server: Wahl des Recovery Models, Kette aus Voll-, Differenz- und Log-Sicherung, Rückkehr zu einem bestimmten Zeitpunkt mit STOPAT, Ursachen für Log-Wachstum und Konflikte mit Image-Sicherungen.
Technische Zusammenfassung
Technologie
SQL Server, T-SQL, BACKUP/RESTORE, VSS, msdb, DBCC CHECKDB
Thema
Recovery Model, Log-Sicherungen und Rückkehr zu einem bestimmten Zeitpunkt
Niveau
Mittel bis fortgeschritten
Voraussetzungen
  • SQL-Server-Berechtigung: für Sicherung db_backupoperator oder sysadmin, für Wiederherstellung dbcreator oder sysadmin
  • Separates Sicherungsrepository
  • Separate SQL-Server-Instanz für Tests
In diesem Beitrag8 Abschnitte

Ein Anruf aus der Buchhaltung: „Um 14:20 Uhr hat jemand ein falsches Update ausgeführt – können wir zu diesem Zeitpunkt zurück?“ Haben Sie nur das nächtliche VM-Image, lautet die Antwort „wir können zu gestern Nacht zurück“, und das wird meist nicht akzeptiert. Eine Image-Sicherung deckt den vollständigen Verlust des Servers ab, nicht die Rückkehr zu einem bestimmten Zeitpunkt. Im Folgenden beschreiben wir mit T-SQL-Beispielen die Wahl des Recovery Models, wie Voll-, Differenz- und Transaktionsprotokollsicherungen zusammenwirken, die Rückkehr zu einem bestimmten Zeitpunkt, warum die Protokolldatei wächst und wo sich Image-Sicherung und native SQL-Sicherung in die Quere kommen.

Wahl des Recovery Models

Das Recovery Model bestimmt, wie das Transaktionsprotokoll verwaltet wird und welche Wiederherstellungsoptionen möglich sind. Falsches Modell bedeutet falscher RPO.

Modell Log-Sicherung Rückkehr zu einem bestimmten Zeitpunkt Typische Verwendung
SIMPLE Keine; das Log wird per Checkpoint automatisch wiederverwendet Nein, nur letzte Voll-/Differenzsicherung Test, Reporting, reproduzierbare Daten
FULL Pflicht; das Log wird erst nach einer Log-Sicherung wiederverwendet Ja Produktive OLTP-Datenbanken
BULK_LOGGED Pflicht; Massenvorgänge werden minimal protokolliert Nein bei Log-Sicherungen mit Massenvorgängen Vorübergehend in großen Datenladefenstern

Die Entscheidungsregel ist einfach: Ist der RPO der Datenbank kürzer als das Intervall der Voll- oder Differenzsicherung, ist das Modell FULL nötig. Wird ein RPO von einem Tag akzeptiert, bleiben Sie bei SIMPLE und ersparen sich den Aufwand der Log-Verwaltung. Häufig anzutreffen ist das Gegenteil: Das Modell ist FULL, aber ein Log-Sicherungsjob wurde nie eingerichtet. Modell prüfen und ändern:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

ALTER DATABASE [Vertrieb] SET RECOVERY FULL;

Der Wechsel von SIMPLE zu FULL startet die Log-Kette nicht sofort. Die Kette beginnt mit der ersten Voll- (oder Differenz-)Sicherung nach dem Wechsel; bis zu dieser Sicherung verhält sich die Datenbank faktisch wie SIMPLE. Erstellen Sie unmittelbar nach der Modelländerung eine Vollsicherung.

Sicherungsarten und Kette

  • Vollsicherung: Enthält die gesamte Datenbank und den für eine konsistente Wiederherstellung nötigen Teil des Logs. Ausgangspunkt der übrigen Sicherungen.
  • Differenzsicherung (Differential): Enthält die seit der letzten Vollsicherung geänderten Extents. Sie ist kumulativ: Bei der Wiederherstellung wird nur die letzte Differenzsicherung benötigt.
  • Transaktionsprotokollsicherung: Enthält die seit der vorherigen Log-Sicherung geschriebenen Log-Einträge. Log-Sicherungen werden der Reihe nach eingespielt. Fehlt eine Log-Sicherung, kann eine Wiederherstellung, die bei derselben Voll- oder Differenzsicherung beginnt, zu jedem Zeitpunkt bis zum Ende der letzten Log-Sicherung vor der fehlenden zurückkehren; den Zeitraum der fehlenden Sicherung und alles danach kann diese Kette nicht wiederherstellen. Gibt es eine nach der Lücke erstellte Voll- oder Differenzsicherung, wird die Wiederherstellung von dieser Sicherung gestartet, und die danach erstellten Log-Sicherungen sind wieder nutzbar. Verloren sind die Wiederherstellungspunkte zwischen dem Beginn der fehlenden Log-Sicherung und der nächsten Voll- oder Differenzsicherung.
  • COPY_ONLY-Sicherung: Eine unabhängige Sicherung, die die Reihenfolge der Kette nicht beeinflusst. Eine Copy-only-Vollsicherung ändert die Basis der Differenzsicherung nicht; eine Copy-only-Log-Sicherung kürzt das Log nicht.

Differenzsicherungen beziehen sich immer auf die letzte normale Vollsicherung. Eine mit einem anderen Werkzeug erstellte Vollsicherung, die nicht copy-only ist, ändert stillschweigend die Basis Ihrer Differenzsicherungen; bis zum Tag der Wiederherstellung bemerkt es niemand. Im Abschnitt zur Image-Sicherung kommen wir darauf zurück.

Beispielhafter Sicherungsplan

Ein typischer Plan für eine Produktionsdatenbank mit 15 Minuten RPO: wöchentlich eine Vollsicherung, jede Nacht eine Differenzsicherung, alle 15 Minuten eine Log-Sicherung. Die Sicherungen sollten nicht auf der lokalen Festplatte des Servers liegen, sondern in einem separaten Repository. Eine Sicherung, die mit dem Server verloren geht, ist keine Sicherung.

-- Sonntag 01:00 Vollsicherung
BACKUP DATABASE [Vertrieb]
  TO DISK = N'\\backup01\sql\Vertrieb\Vertrieb_FULL_20261108.bak'
  WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Montag–Samstag 01:00 Differenzsicherung
BACKUP DATABASE [Vertrieb]
  TO DISK = N'\\backup01\sql\Vertrieb\Vertrieb_DIFF_20261110.bak'
  WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- Alle 15 Minuten Log-Sicherung
BACKUP LOG [Vertrieb]
  TO DISK = N'\\backup01\sql\Vertrieb\Vertrieb_LOG_202611101415.trn'
  WITH COMPRESSION, CHECKSUM;

Die Dateinamen können Sie im Schritt des SQL-Server-Agent-Jobs mit Datum und Uhrzeit erzeugen oder verbreitete Wartungsskripte nutzen. Zwei Dinge sind Pflicht: Jede Sicherung wird in eine eigene Datei geschrieben, und CHECKSUM wird verwendet. Mit CHECKSUM prüft SQL Server die Seitenprüfsummen während der Sicherung und fügt der Sicherung eine eigene Prüfsumme hinzu. Bei der Ersteinrichtung ist eine häufige Störung die Berechtigung: Hat das SQL-Server-Dienstkonto, unter dem der Job läuft, keine Schreibberechtigung auf die Zielfreigabe, scheitert die Sicherung mit dem Fehler „Operating system error 5 (Access is denied)“. Um die Auswirkung des Plans auf die Größe des Sicherungsspeichers zu berechnen, kann die Methode aus dem Beitrag Kapazitätsberechnung für Sicherungsspeicher verwendet werden.

Wiederherstellung zu einem bestimmten Zeitpunkt

Szenario: Am 10. November um 14:22 Uhr wurde ein fehlerhaftes UPDATE ausgeführt und etwa eine Stunde später bemerkt. Der erste Reflex ist, über die Produktionsdatenbank wiederherzustellen. Tun Sie das nicht. Stellen Sie die Daten unter einem anderen Namen auf den Zeitpunkt 14:21 Uhr wieder her und korrigieren Sie die betroffenen Datensätze per Vergleich; so bleiben die in der Zwischenstunde korrekt eingegebenen Datensätze erhalten.

  1. Erstellen Sie zuerst die letzte Log-Sicherung; so gelangen die Log-Einträge rund um den Zeitpunkt des Ereignisses in die Kette.
  2. Stellen Sie die letzte Vollsicherung mit NORECOVERY unter einem neuen Datenbanknamen wieder her.
  3. Spielen Sie die letzte Differenzsicherung mit NORECOVERY ein.
  4. Spielen Sie die Log-Sicherungen nach der Differenzsicherung der Reihe nach ein; verwenden Sie bei der Log-Sicherung, die den Zeitpunkt des Ereignisses enthält, STOPAT.
  5. Öffnen Sie die Datenbank mit RECOVERY und vergleichen Sie die Daten.
RESTORE DATABASE [Vertrieb_1421]
  FROM DISK = N'\\backup01\sql\Vertrieb\Vertrieb_FULL_20261108.bak'
  WITH MOVE N'Vertrieb' TO N'D:\Data\Vertrieb_1421.mdf',
       MOVE N'Vertrieb_log' TO N'L:\Log\Vertrieb_1421.ldf',
       NORECOVERY, STATS = 10;

RESTORE DATABASE [Vertrieb_1421]
  FROM DISK = N'\\backup01\sql\Vertrieb\Vertrieb_DIFF_20261110.bak'
  WITH NORECOVERY;

-- Log-Sicherungen nach 01:00 der Reihe nach ...
RESTORE LOG [Vertrieb_1421]
  FROM DISK = N'\\backup01\sql\Vertrieb\Vertrieb_LOG_202611101430.trn'
  WITH STOPAT = '2026-11-10T14:21:00', NORECOVERY;

RESTORE DATABASE [Vertrieb_1421] WITH RECOVERY;

Die mit MOVE zu verwendenden logischen Dateinamen ermitteln Sie mit RESTORE FILELISTONLY. Soll die gesamte Datenbank auf den Zeitpunkt des Ereignisses zurückgesetzt werden (etwa bei Ransomware oder schwerer Beschädigung), wird die letzte Log-Sicherung mit BACKUP LOG ... WITH NORECOVERY erstellt; diese „Tail-Log“-Sicherung versetzt die Datenbank in den Wiederherstellungszustand und verhindert, dass die letzten Transaktionen verloren gehen. Sind die Datendateien beschädigt, wird die Option NO_TRUNCATE ergänzt. Ohne Tail-Log-Sicherung gehen die Transaktionen der letzten Minuten verloren.

Warum die Protokolldatei wächst

Kommt um Mitternacht der Alarm „Laufwerk L: ist voll“, ist der Grund meist derselbe: Im Modell FULL werden keine Log-Sicherungen erstellt. Das ist aber nicht der einzige Grund. Raten Sie nicht; SQL Server sagt in der Spalte log_reuse_wait_desc bereits, warum das Log nicht wiederverwendet werden kann:

Wert Bedeutung Maßnahme
LOG_BACKUP Es wird auf eine Log-Sicherung gewartet Log-Sicherungsjob prüfen
ACTIVE_TRANSACTION Eine lang laufende offene Transaktion existiert Mit DBCC OPENTRAN ermitteln
AVAILABILITY_REPLICA Sekundäres AG-Replikat hängt zurück Replikatstatus und Netzwerk untersuchen
REPLICATION Der Replikationsleser hat das Log nicht verarbeitet Log Reader Agent prüfen
ACTIVE_BACKUP_OR_RESTORE Laufende Sicherung oder Wiederherstellung Ende des Vorgangs abwarten

Die Log-Auslastung sehen Sie mit DBCC SQLPERF(LOGSPACE);, die Anzahl virtueller Protokolldateien (VLF) ab SQL Server 2016 SP2 mit SELECT COUNT(*) FROM sys.dm_db_log_info(DB_ID('Vertrieb'));. Kleine automatische Wachstumsschritte erzeugen Tausende VLFs und verlängern Wiederherstellung und Recovery beim Start. Planen Sie Anfangsgröße und Wachstumsschritt der Protokolldatei mit festen MB-Werten; entfernen Sie prozentuales Wachstum.

Gemeinsamer Einsatz von Image-Sicherung und nativer Sicherung

In vielen Systeminfrastrukturen wird die SQL-Server-VM mit einem Werkzeug wie Veeam anwendungsbewusst (VSS-basiert) als Image gesichert, und das DBA-Team erstellt zusätzlich native SQL-Sicherungen. Beide Methoden funktionieren zusammen, aber wer was verwaltet, muss schriftlich festgehalten sein.

  • Basis der Differenzsicherung: Eine über den VSS-Writer erstellte Image-Sicherung ist für SQL Server eine normale Vollsicherung, sofern sie nicht als copy-only gekennzeichnet ist, und ändert die Basis der Differenzsicherungen. Werden native Differenzsicherungen verwendet, aktivieren Sie im Image-Werkzeug die Option copy-only.
  • Log-Hoheit: Nur ein Werkzeug darf die Log-Sicherungen erstellen. Ist das Image-Werkzeug mit Log-Kürzung (Truncate) oder eigener Log-Sicherung konfiguriert und werden zugleich native Log-Sicherungen erstellt, wird die Kette auf zwei Orte verteilt oder bricht. Entweder verwaltet das Image-Werkzeug die Logs, und es gibt keine native Log-Sicherung, oder das Image-Werkzeug wird so eingestellt, dass es das Log nicht anrührt.
  • Prüfung des Verlaufs: Welches Werkzeug wann gesichert hat, sehen Sie im Verlauf in msdb.
SELECT TOP (50) bs.database_name, bs.type, bs.is_copy_only,
       bs.backup_start_date, bs.first_lsn, bs.last_lsn,
       bmf.device_type, bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bmf
  ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = N'Vertrieb'
ORDER BY bs.backup_start_date DESC;

In der Spalte type steht D für Voll-, I für Differenz- und L für Log-Sicherung. Einträge mit device_type 7 sind Sicherungen über ein virtuelles Gerät, also meist mit VSS oder Software von Drittanbietern erstellt. Nutzen Sie diese Abfrage, um festzustellen, ob die Kette der Differenzsicherungen durch eine unerwartete Vollsicherung gebrochen wurde.

Prüfung und Test

RESTORE VERIFYONLY prüft, dass die Sicherungsdatei lesbar und vollständig ist; zusammen mit CHECKSUM prüft es auch die Seitenprüfsummen:

RESTORE VERIFYONLY
  FROM DISK = N'\\backup01\sql\Vertrieb\Vertrieb_FULL_20261108.bak'
  WITH CHECKSUM;

VERIFYONLY beweist nicht die logische Konsistenz der Datenbank. Die Datei lässt sich lesen, mehr nicht. Die echte Prüfung besteht darin, die Sicherung auf einem separaten Server wiederherzustellen und anschließend DBCC CHECKDB auszuführen. Checkliste:

  • Passen Recovery Model und RPO für jede Produktionsdatenbank zusammen?
  • Läuft der Log-Sicherungsjob alle 15 Minuten erfolgreich und löst er im Fehlerfall einen Alarm aus?
  • Werden die Sicherungen in ein vom SQL-Server getrenntes Repository und an einen zweiten Ort kopiert?
  • Sind Log-Hoheit und Copy-only-Einstellung mit dem Image-Werkzeug geklärt?
  • Wird mindestens einmal im Monat auf einem Testserver die Kette aus Voll-, Differenz- und Log-Sicherung wiederhergestellt und DBCC CHECKDB ausgeführt?
  • Wird die Wiederherstellungsdauer gemessen und mit der RTO verglichen?

Zur technischen Entsprechung der Ziele siehe den Beitrag Technologiewahl nach RPO/RTO-Ziel, zu Kopienanzahl und Testdisziplin den Beitrag 3-2-1-1-0-Backup-Regel. Wie wir Sicherungskette und Betrieb in SQL-Server-Umgebungen angehen, steht auf unseren Seiten Datensicherung und Disaster Recovery sowie Server- und Cloud-Management. Grundlegende Referenz ist das Microsoft-Dokument Sicherung und Wiederherstellung von SQL-Server-Datenbanken.

Häufig gestellte Fragen

Welches Recovery Model sollte für SQL Server gewählt werden?

Ist der RPO der Datenbank kürzer als das Intervall der Voll- oder Differenzsicherung, sind das Modell FULL und regelmäßige Log-Sicherungen nötig. Für Test- und Reporting-Datenbanken, bei denen ein Tag Datenverlust akzeptiert wird, genügt das Modell SIMPLE.

Warum wächst die Transaktionsprotokolldatei ständig?

Im Modell FULL ist der häufigste Grund, dass keine Log-Sicherung erstellt wird. Auch lange offene Transaktionen, ein zurückhängendes AG-Replikat oder Replikation verhindern die Wiederverwendung des Logs. Den tatsächlichen Grund zeigt die Spalte log_reuse_wait_desc in sys.databases.

Braucht man bei vorhandener Veeam-Image-Sicherung zusätzlich eine SQL-Sicherung?

Möglicherweise; entscheidend sind die Rückkehr zu einem bestimmten Zeitpunkt und die Bedürfnisse der DBAs. Werden beide gemeinsam genutzt, muss die Image-Sicherung copy-only sein, und nur ein Werkzeug darf die Log-Sicherungen erstellen. Sonst ändert sich die Differenzbasis oder die Log-Kette wird geteilt.

Beweist RESTORE VERIFYONLY, dass die Sicherung intakt ist?

Nein. Es prüft, dass die Sicherungsdatei lesbar und vollständig ist, und zusammen mit CHECKSUM die Seitenprüfsummen. Für logische Konsistenz stellen Sie die Sicherung auf einem Testserver wieder her und führen DBCC CHECKDB aus.

Support

Kontaktieren Sie uns für Support oder eine Bestandsaufnahme zu diesem Thema.

Kontaktformular
[email protected]

Eretek Systembetreuung

Serverinstallation, Konfiguration, Störungsbehebung und regelmäßige Wartung. Active Directory, Virtualisierung und Datensicherung werden mit derselben Disziplin betrieben.

Wissenszentrum

Weitere Beiträge