Full or simple Recovery - Sind Ihnen Ihre Daten wirklich nichts wert?

Überblick

Falls Sie eine neue Datenbank anlegen, wird diese, sofern Sie keine Änderungen an der ‘model’-Systemdatenbank vorgenommen haben, im Full-Recovery-Modell betrieben. Und das ist auch gut so.
Bei unseren Assessments machen wir aber oft die Erfahrung, dass die Datenbanken der analysierten MSSQL Server Instanz im Simple Recovery Modell betrieben werden. Diese Betriebsart muss der Administrator explizit einrichten, so dass sich uns die Frage stellt, ob dem Kunden diese Situation bekannt ist und warum er diesen unsicheren Betriebsmodus verwendet.

Datenbankdateien und Recovery Modell

Sofern Sie sich schon einmal die Dateien angesehen haben, die eine Datenbank ausmachen, werden Sie festgestellt haben, dass diese immer zumindest aus einer Datei mit der Endung ‘.mdf’ und einer Datei mit der Endung ‘.ldf’ bestehen. Die mdf-Datei (“Datendatei”) wird dabei für die Speicherung Ihrer Tabellen verwendet und die ldf-Datei (das “Transaktions-Log” oder kurz “Tlog”) zur Protokollierung Ihrer Daten- und DB-Objekt-Änderungen. Die ldf-Datei besteht intern aus mehreren sogenannten virtuellen Logfiles (VLF), deren Anzahl und Größe variieren. Die VLFs werden zyklisch mit Transaktions-Protokollinformationen beschrieben, wobei ältere bereits beschriebene VLFs erst überschrieben werden können, wenn bestimmte Voraussetzungen erfüllt sind. Diese Voraussetzungen werden durch das konfigurierte Recovery-Modell bestimmt. In jeder Datenbank kann eines der drei Recovery-Modelle verwendet werden:

Full Recovery Modell (Default)
Simple Recovery Modell
Bulk-Logged Modell (hier nicht betrachtet)

Checkpoints

Ein Checkpoint stellt eine DB-interne Aktion dar, bei der alle durch bereits abgeschlossene Transaktionen veränderten Datenblöcke auf die Platte, also in das Datenfile, geschrieben werden.

Das Full Recovery Modell

Im Full Recovery Modell können VLFs wiederverwendet werden wenn folgende Bedingungen erfüllt sind

Die Transaktion die in das VLF geschrieben hat ist beendet
Es ist ein Checkpoint erfolgt
Das Transaktionsprotokoll wurde gesichert

Sind nicht alle drei Bedingungen erfüllt, so führt dies entweder zum Anhalten der Datenbank oder zu einem unkontrollierten Anwachsen des TLog. Insbesondere Bedingung 3 bedeutet, dass regelmäßig Sicherungen der Transaktionsprotokolle erfolgen müssen.

Existieren nun

eine vollständige Sicherung der Datenbank
eventuelle differentielle Sicherungen
eine lückenlose Anzahl regelmäßiger Sicherungen der TLogs

so kann im Problemfall eine Wiederherstellung der Datenbank zu jedem beliebigen Zeitpunkt zwischen der letzten vollständigen Sicherung und dem Erkennen des Problemfalls erfolgen. Diese Möglichkeit wird auch als Point-In-Time Recovery bezeichnet.

Das Simple Recovery Modell

Im Simple Recovery Modell entfällt die dritte Bedingung und es ist nicht möglich, eine TLog-Sicherung durchzuführen. Die VLFs können also schneller wiederverwendet werden, was die Gefahr des unkontrollierten TLog-Wachstum verringert, aber den Nachteil hat, dass eine Wiederherstellung nur genau zu dem Zeitpunkt erfolgen kann, an dem die letzte vollständige oder differentielle Sicherung erfolgt ist. Erfolgt also täglich nach Geschäftsschluss eine vollständige DB-Sicherung und tritt am folgenden Tag kurz vor Geschäftsschluss ein Problem auf, so ist die gesamte Tagesarbeit verloren und das kann nur in sehr seltenen Fällen, nämlich wenn man die entsprechenden Daten jederzeit aus anderen Quellen wiederherstellen kann, vermieden werden.

Wie ermittle ich das Recovery Modell meiner Datenbanken?

Um zu ermitteln, wie die Datenbanken in Ihrer MSSQL Instanz betrieben werden, können Sie das folgende Statement verwenden:

-- Die Systemdatenbanken master, model, msdb und tempdb haben die
-- Datenbank IDs 1, ..., 4 und werden, mit Ausnahme der model Datenbank,
-- im Simple-Recovery-Modus betrieben.
-- 
select name, recovery_model, recovery_model_desc from sys.databases where database_id > 4

Die folgende Abfrage zeigt das Ergebnis für den hier behandelten Testfall mit zwei Datenbanken in einer MSSQL Instanz. Hierbei handelt es sich um die von Microsoft bereitgestellte Testdatenbank AdventureWorksLT2022, die im einen Fall im Full- (Suffix _FR) und im anderen Fall im Simple-Recovery-Modus (Suffix _SR) betrieben wird.

Ergebnis der Abfrage des Recovery-Modus der beiden AdventureWorksLT2022 Testdatenbanken

In der Spalte recovery_model sehen Sie die instanz-interne Codierung, in der Spalte recovery_model_desc die Bedeutung der jeweiligen Codierung (recovery_model_description).

Situation bei Entwicklungs- und Testdatenbanken

Im Fall, dass im Rahmen eines MSSQL Assessments festgestellt wurde, dass eine Datenbank im Simple Recovery Modell betrieben wird und der Kunde darauf hingewiesen wurde, erhalten wir in einigen Fällen die Antwort, dass es sich dabei ja “nur” um Entwicklungs- oder Testdaten handele. Aber auch solche Daten sind aus unserer Sicht wichtig. Oder möchten Sie Ihrem Mitarbeiter erklären, dass er einen komplexen Testfall, den er zuvor aufwändig eingerichtet hat, erneut aufbauen muss, weil auf regelmäßige, automatische TLog-Sicherungen verzichtet wurde?

Datenbankwiederherstellung

Wie bereits weiter oben erwähnt, wurden zur Demonstration zwei identische Datenbanken AdventureWorksLT2022_FR (Full Recovery) und AdventureWorksLT2022_SR (Simple Recovery) angelegt. Diese werden über DB-Jobs der MaintenanceSolution von Ola Hallengren regelmäßig gesichert. Sicherungen sind folgendermaßen eingerichtet (Bezeichnungen entsprechen der Funktionalität und der Standard-Benennung aus dem “Hallengren-Script”):

Freitags um 11:05 - DatabaseBackup - USER_DATABASES - FULL
Montags bis Donnerstags um 15:10 - DatabaseBackup - USER_DATABASES - DIFF
Montags bis Freitags zwischen 8:00 und 16:00 im 5-Minuten Rhythmus

Ausgehend von den Daten der Tabelle [customer_load] wurde in beiden Datenbanken eine Tabelle [Kundenauswertung] mit ca. 3000 Datensätzen erstellt.

Über einen Datenbankjob wird minütlich die datetime-Spalte [modifiedDate] auf das aktuelle Datum / die aktuelle Uhrzeit aktualisiert über:

update [AdventureWorksLT2022_FR].[dbo].[Kundenauswertung] set [modifiedDate] = SYSDATETIME()
update [AdventureWorksLT2022_SR].[dbo].[Kundenauswertung] set [modifiedDate] = SYSDATETIME()

Recovery in AdventureWorksLT2022_SR

Führt man im SQL Server Management Studio (SSMS) das Recovery für die Datenbank AdventureWorksLT2022_SR aus, so erhält man das folgende Bild:

Zeitbasiertes Recovery in der Datenbank AdventureWorksLT2022_SR

Als Zeitpunkte für das Recovery am 27.07.2024 13:00 (die heutige differentielle Sicherung erfolgt erst in ca. 2 Stunden) sind lediglich der 21.06.2024 (Vollständiges Backup) und die jeweiligen Zeitpunkte des differentiellen Backups am 24., 25. und 26.06.2024 verfügbar und wenn beispielsweise der 26.06.2024 15:00 (das heutige differentielle Backup ist noch nicht gelaufen) eingestellt wird, springt der rote Marker auf die spätestmögliche differentielle Sicherung (am 25.06.2024 15:10).

Recovery in AdventureWorksLT2022_FR

Analog die Recovery-Möglichkeiten in der Datenbank mit dem Full Recovery Modell:

Zeitbasiertes Recovery in der Datenbank AdventureWorksLT2022_FR

Hier ist jeder beliebige Zeitpunk vom ersten vollständigen Backup bis zum aktuellen Zeitpunkt möglich. Eine (kleine) Besonderheit tritt auf, wenn man tatsächlich den aktuellen Zeitpunkt für das Recovery wählt. Hier ist in der Regel noch eine letzte TLog-Sicherung, (“Tail-Log-Sicherung”), erforderlich, die die Instanz aber im Rahmen des Recovery-Prozess automatisch durchführt. Diese Situation wird am oberen Bereich des Backup-Fensters angezeigt und ist in der folgenden Abbildung dargestellt. In der Abbildung wurde zur Verdeutlichung der Zeitspanne zwischen dem letzten TLog-Backup und dem aktuellen Transaktionslog-Inhalt, der noch über die Tail-Log-Sicherung erfolgt, das “Timeline Interval” von “Week” auf “Hour” geändert:

Recovery-Fenster mit Hinweis auf erforderliche Tail-log Sicherung

Dateigrößenbetrachtung der beiden Datenbanken

Für die hier angestellte Größenbetrachtung der Datendateien wurde der Job zur regelmäßigen TLog-Sicherung für einige Zeit deaktiviert, und durch vorherige Kontrolle sichergestellt, dass die TLog-Datei bei Bedarf erweitert werden kann. Anschließend wurden die o.a. Update-Statements zur Aktualisierung des [modifiedDate] mit Hilfe des Query Stress Simulators 100 mal mit 100 parallelen Threads, also 10.000 mal ausgeführt. Die folgenden beiden Tabellen zeigen

die seit dem Einrichten der jeweiligen Datenbanken stabilen Dateigrößen
die Dateigrößen nach Durchführung der 10.000 Updates

RecoveryModel	name	type_desc
Full	AdventureWorksLT2022_Data	2.808
Full	AdventureWorksLT2022_Log	8.448
Simple	AdventureWorksLT2022_Data	2.808
Simple	AdventureWorksLT2022_Log	8.256

RecoveryModel	name	type_desc
Full	AdventureWorksLT2022_Data	2.808
Full	AdventureWorksLT2022_Log	434.432
Simple	AdventureWorksLT2022_Data	2.808
Simple	AdventureWorksLT2022_Log	8.256

Fazit

Datenverlust schmerzt, nicht nur in der Produktion. Wählen Sie grundsätzlich das Full Recovery Modell für Ihre Datenbanken und weichen Sie von dieser Regel nur in den extrem seltenen Fällen ab, in denen eine Point-In-Time Restaurierung Ihrer Datenbank aus anderen Quellen als den Backups schneller möglich ist.

Wenn Sie mehr über den beschriebenen Fall erfahren möchten, stehen Ihnen unsere Expert:innen gerne zur Seite. Vereinbaren Sie einfach ein unverbindliches Beratungsgespräch über unser Kontaktformular. Wir freuen uns von Ihnen zu hören!