News zu Servicewelten

Home → Servicewelten → DBA Tipp: Unterschiedliche SQL Server Wiederherstellungsmodelle und ihre Anwendungsbereiche

DBA Tipp: Unterschiedliche SQL Server Wiederherstellungsmodelle und ihre Anwendungsbereiche

Verfasst von Sascha Mühlmann
09 Januar 2023
10:21
Backup & Recovery, Datenbank, DBA Tipp, Microsoft, SQL Server

Immer wieder gibt es Verwirrung über die verschiedenen Wiederherstellungsmodelle einer Datenbank und in welchem Fall man sie benutzen sollte. Die Wahl des Wiederherstellungsmodells hängt stark von den Anforderungen an den tolerierbaren Verlustzeitraum ab.

Grundsätzlich unterscheidet man im Microsoft SQL Server Bereich drei Modelle:

Das einfache Wiederstellungsmodell
Das vollständige Wiederherstellungsmodell
Das massenprotokollierte Wiederherstellungsmodell

In diesem Artikel werden wir auf die verschiedenen Modelle, ihre Anwendungsbereiche und Besonderheiten sowie die Vor- und Nachteile eingehen.

Einfaches Wiederherstellungsmodell

Wird eine neue Datenbank angelegt, ist standardmäßig das einfache Wiederstellungsmodell (Simple Recovery Model) vorausgewählt. Bei diesem Modell werden fertiggestellte Transaktionen direkt vom Transaktionslog abgeschnitten, sodass die entsprechende Logdatei nicht sehr groß wird. Du musst dich bei diesem Modell nur um die vollständigen Full- und (wenn nötig) um die Differential-Backups der Datenbank kümmern. Ein Backup der Transaktionslogdatei und damit eine Wiederherstellung zu einem bestimmten Zeitpunkt ist nicht möglich.

Für einfache, nicht operationskritische Datenbanken geeignet

Das Modell eignet sich vor allem für einfache Datenbanken, bei denen man sich nur um die vollständigen Backups und evtl. noch um differenzielle Backups kümmern möchte. Wir empfehlen dir aber, das einfache Wiederherstellungsmodell für Datenbanken zu benutzen, die nicht operationskritisch und nicht größer als 10 GB sind.

Falls die Datenbank produktionskritisch ist und eine Wiederherstellung auch zu einem Zeitpunkt zwischen zwei Backups möglich sein soll, ist dieses Modell nicht geeignet.

Vorteile:

Du musst dich nur um die vollständigen und eventuell noch um differenzielle Backups kümmern.
Die Transaktionslogdatei verwaltet sich selbst und wird nicht sehr groß.

Nachteile:

Du kannst immer nur zu einem bestimmten erfolgreichen Backup zurückspringen.
Auf einen Stand zwischen zwei Backups (bspw. zwischen dem Backup von Sonntag Abend und Montag Abend) kannst du nicht wiederherstellen.

Vollständiges Wiederherstellungsmodell

Das vollständige Wiederherstellungsmodell (Full Recovery Model) wird vornehmlich für größere, produktive Datenbanken genutzt. In dieser Konfiguration schreibt der SQL Server alle Transaktionen in ein Transaktionslog, das üblicherweise aus einer Transaktionslogdatei besteht. Das Schreiben aller Transaktionen in eine Logdatei bietet den großen Vorteil, dass die Wiederherstellung (bei vorhandenen Backups der Logdateien) der Datenbank auf nahezu jeden Zeitpunkt möglich ist (point-in-time recovery).

Für große, produktionskritische Datenbanken geeignet

Das vollständige Wiederherstellungsmodell eignet sich für produktionskritische und / oder große Datenbanken, die häufigen Transaktionen ausgesetzt sind. Dadurch gewinnen Sie die optimale Kontrolle über den Wiederherstellungszeitpunkt und sind für Desasterfälle besser gerüstet.

Bei diesem Modell sollte unbedingt darauf geachtet werden, dass neben den vollständigen und möglicherweise differenziellen Backups auch ein Job für die Transaktionslog-Backups eingerichtet ist. Ansonsten wird die Transaktionslogdatei mit der Zeit immer größer. Nur ein Backup der Logdatei sorgt dafür, dass die schon getätigten Transaktionen abgeschnitten und bereinigt werden, sodass die Datei nicht zu groß wird.

Vorteile:

Du kannst auf einen bestimmten Zeitpunkt zwischen dem ältesten und dem neusten Backup und damit ganz granular wiederherstellen.
Im Desasterfall besteht die Chance, noch ein sogenanntes “Tail Backup” der Transaktionslogdatei zu fahren, sodass du die Datenbank bis kurz vor dem Crash wiederherstellen kannst.

Nachteile:

Du solltest unbedingt ein regelmäßiges Backup für das Transaktionslog einrichten. Ansonsten wird diese Datei immer größer und schreibt irgendwann den Speicherplatz voll.

Massenprotokolliertes Wiederherstellungsmodell

Das massenprotokollierte Wiederherstellungsmodell (Bulk-logged Recovery Model) ist eine Sonderform vom vollständigen Wiederherstellungsmodell. Die Besonderheit ist hierbei, dass sogenannte Bulk Transaktionen (BULK INSERT, SELECT INTO, …) nur minimal im Transaktionslog gespeichert werden. Im Full Recovery Modus hingegen werden alle Transaktionen im Log hinterlegt. Dadurch wird die Transaktionslogdatei nicht so groß, wenn zum Beispiel nachts Importe laufen. Ansonsten werden alle normalen Transaktionen genau wie im vollständigen Wiederherstellungsmodell im Transaktionslog gespeichert.

Besonders geeignet für Datawarehouse Umgebungen

Das Modell eignet sich insbesondere für Datawarehouse Umgebungen, bei denen hauptsächlich massenprotokollierte Transaktionen ausgeführt werden oder Datenbanken, die in regelmäßigen Abständen solchen umfangreichen Transaktionen ausgesetzt sind. Ansonsten ist das vollständige Wiederherstellungsmodell zu empfehlen.

Falls du auf die Möglichkeit Wert legst, auf einen bestimmten Zeitpunkt zurück zu gehen, ist dieses Modell nicht geeignet, da es kein “point-in-time recovery” unterstützt.

Vorteile:

Du bekommst einige Vorteile des vollständigen Wiederherstellungsmodells im Bereich Desaster Wiederherstellung (Taillog Backup).
Bulk Operationen werden nur minimal geloggt und nehmen daher nicht so viel Platz im Transaktionslog weg.

Nachteile:

Es besteht keine Möglichkeit für “point-in-time Recovery”.
Du kannst nicht auf den letzten Stand zurücksichern, wenn vor dem Ausfall eine Bulk Operation gelaufen ist. Dann ist nur die Wiederherstellung zum letzten erfolgreichen Log Backup möglich.

Fazit

Welches Wiederherstellungsmodell gewählt wird, hängt stark von den Anforderungen an den tolerierbaren Verlustzeitraum von Datenbankeinträgen und der Art der Befüllung der Datenbank ab. Bei jedem Modell gibt es einige Dinge in der Konfiguration zu beachten. Eine schematische Darstellung findest du übrigens » hier.

Du hast weitere Fragen? Gerne unterstützen wir dich bei der Backupplanung für deine Microsoft SQL Server Datenbanken.

Hier findest du weitere interessante DBA Tipps oder Posts zum Thema SQL Server aus unserem News und Insights Bereich.

Share this article