In Datenbanken gibt es eine Vielzahl an verschiedenen Wiederherstellungsmöglichkeiten und Backup-Arten. Die Einsatzmöglichkeiten werden in diesem Blogbeitrag beleuchtet.

Jede Datenbank kann einmal in einen Status geraten, in welcher die Daten verloren scheinen. Tritt diese Situation ein, sollte sie den Datenbankadministrator nicht völlig unvorbereitet treffen. Damit es nicht zu genau so einer Situation kommt, versucht der folgende Blogbeitrag sowohl die Arten von Backupstrategien als auch deren Einsatzgebiete zu beleuchten. Hierbei gilt zu bedenken, dass es nicht die eine Lösung gibt, sondern die Entscheidung für jede Datenbank neu abgewogen werden muss

Die Backup-Strategie

Strategieparameter

Bei der Auswahl und Aufstellung einer Backupstrategie gibt es zwei Parameter, welche die Auswahl am meisten beeinflussen: Recovery Time Objective (RTO) und Recovery Point Objective (RPO).

• Recovery Time Objective (RTO):
  Hierbei handelt es sich um die maximale Zeit, welche akzeptiert wird, um ein System nach einem Ausfall wieder in einen Zustand zu versetzen, in welchem es produktiv zur Verfügung steht.

• Recovery Point Objective (RPO):
  Hier handelt es sich um die Menge an Datenverlust (in Zeiteinheit), welche maximal bei einem Systemausfall akzeptiert werden kann.

Wiederherstellungsmodelle

Legt man eine Datenbank neu an, so stehen im SQL Server drei Modelle der Wiederherstellung zur Verfügung.

Abbildung 1: Erzeugen einer Datenbank und Auswahl des Wiederherstellungsmodells

• Wiederherstellungsmodell – Vollständig:
  In diesem Modell werden alle Änderungen an den Daten protokolliert. Wiederherstellungen sind hier bis zu einem bestimmen Punkt in der Zeit möglich. Der Backupaufwand ist erhöht, ebenso der Speicherbedarf.

• Wiederherstellungsmodell – Massenprotokolliert:
  Wird als Erweiterung des vollständigen Backups angesehen und ist dazu gedacht, das Protokoll der Datensicherung kleiner zu halten, wenn ein Import mit einer hohen Anzahl an Datensätzen ansteht.

• Wiederherstellungsmodell – Einfach:
  Im Gegensatz zu den ersten beiden Modellen wird in diesem Modell der Protokollspeicher automatisch wieder freigegeben, wenn die Protokollierung nicht mehr benötigt wird, dies verringert den administrativen Aufwand, sorgt aber auch dafür, dass man immer nur bis zu dem letzten Backupstand Daten wiederherstellen kann.

Welches Modell ist nun das richtige?

Diese Antwort lässt sich, wie eingangs bereits erwähnt, nicht pauschal beantworten. Hier soll trotzdem versucht werden, eine Entscheidungshilfe aufzubauen.

Bei klassischen Reporting-Systemen, die keine eigene Daten erzeugen, welche sich nicht mit Sichten oder Prozeduren z. B. wieder erzeugen lassen, empfiehlt es sich auf das Einfache (simple) Modell zu setzen, da hier der Aufwand gering ist und in der Regel ein Backup von Entwicklungsständen oder nach dem letzten Aufbereitungslauf reicht.

Anders sieht es bei Systemen aus, bei denen User mit Transaktionen Daten erzeugen. Als Beispiel sei hier ein Planungssystem genannt. In diesen Systemen kann eine Situation auftreten, wo ein bestimmter Zeitpunkt wieder verfügbar gemacht werden soll, hierfür bietet sich das Vollständige Modell an.

(Anmerkung des Autors: Vollständig und Einfach bedeutet nicht, dass nur ein Teil oder alle Daten gesichert werden, mit den Backups lässt sich natürlich in beiden Fällen die gesamte Datenbank wiederherstellen.)

Das ausgewählte Modell lässt sich jederzeit als Option der Datenbank ändern.

Abbildung 2: Nachträgliches ändern des Wiederherstellungsmodells

Arten von Backups

Nachdem wir uns in dem vorherigen Abschnitt entschieden haben, welche Art von Wiederherstellungsmodell wir verwenden wollen, stehen uns in diesem Abschnitt unterschiedliche Arten von Backups zur Verfügung.

Abbildung 3: Auswahl des zu erstellenden Backuptyps

In der vorliegenden Datenbank ist das Wiederherstellungsmodell Vollständig gewählt, daher stehen drei Sicherungstypen zur Verfügung.

Bei einem Einfachen Modell stehen nur die ersten beiden Typen zur Verfügung, es hat sich aber bewährt, in diesem Fall immer vollständige Backups zu erstellen.

• Vollständig:
  Bei einem vollständigen Backup werden alle Daten, die Struktur und alle Einstellungen wie etwa auch das Berechtigungskonzept der Datenbank gesichert. Als Ziel lassen sich hier diverse Ziele wählen, als Hinweis sollte man bedenken, nicht für das Backup die gleiche Festplatte zu nutzen wie für die Datenbank, da bei einem Ausfall der Festplatte dann auch das Backup verloren wäre.

• Differenziell:
  In diesem Typen werden alle Änderungen seit dem letzten vollständigen Backup gesichert, entsprechend wächst die Datei mit der Zeit, die zwischen der Erzeugung und dem letzten vollständigen Backup liegen.

• Transaktionsprotokoll:
  Hier werden alle Änderungen durch Transaktionen gesichert, welche seit dem letzten Differential bzw. Vollständigem Backup durchgeführt wurden. Dies sollte sehr regelmäßig durchgeführt werden, da die Datei sonst sehr schnell den verfügbaren Festplattenspeicher füllt.

Wo kann welche Art eingesetzt werden?

Wie bereits oben beschrieben bietet es sich an, das einfache Sicherungsmodell mit vollständigen Sicherungen für Reportingsysteme zu nutzen, welche z. B. einmal pro Nacht aufbereitet werden.

Abhängig von der Aufbereitungszeit kann man z. B. noch entscheiden, ob direkt nach dem Aufbereiten eine Sicherung durchgeführt wird, um im Ausfall den letzten Stand schnell wiederherstellen zu können, oder ob es reicht, eine Sicherung nach strukturellen Änderungen durchzuführen.

Aus Backup-Sicht der spannendere Part sind die Transaktionssysteme, in welchen das Vollständige Modell zum Tragen kommen kann. Als Beispiel für ein solches System soll an dieser Stelle ein hybrides Planungssystem genannt sein. Diese Situation und das Vorgehen sollen im Folgenden noch einmal genauer beleuchtet werden.

Im Gegensatz zu einem Reportingsystem werden in einem Planungssystem durch Benutzer Daten generiert. Wo Daten generiert werden, werden auch Daten verändert. Nicht immer führt eine Änderung oder Benutzeraktion auch zu dem gewünschten Ziel. Um den vorherigen Status wiederherzustellen, hätte man bei einem einfachen Wiederherstellungsmodell nur die Option, die gesamte Datenbank wiederherzustellen, damit wären aber alle Eingaben und Änderungen verloren.

Hier greifen die Stärken des Vollständigen Wiederherstellungsmodells. In diesem stehen Transaktionsprotokolle zur Verfügung, mit welchem zu einem bestimmten Zeitpunkt wiederhergestellt werden kann.

Exemplarischer Ablauf eines Backups mit Zeitpunkt Wiederherstellung

Im Folgenden soll exemplarisch der Sicherungsprozess bei der Erstellung einer neuen V1 aufgezeigt werden. Hiermit soll gesichert sein, dass alle Eingaben, die getätigt wurden, gesichert werden, bevor eine V1 angelegt wird, um das Planungssystem gegen ein unabsichtliches Überschreiben zu sichern.

Voraussetzung

Als Voraussetzung gilt, dass mindestens ein vollständiges Backup der Datenbank existiert. Dieses kann über folgendes T-SQL Skript erzeugt werden.

BACKUP DATABASE [Chair_Planung]
TO  DISK = N'C:\Chair\2019-12-05 Chair Planung Full.bak'
WITH NOFORMAT,
NOINIT, 
NAME = N'Chair_Planung-Vollständig Datenbank Sichern',
SKIP, 
NOREWIND, NOUNLOAD, 
COMPRESSION, STATS = 10

Das “Zwischendurch”-Backup

Hiermit ist das Differential-Backup gemeint, mit welchem alle Änderungen seit dem letzten vollen Backup gesichert werden. Abhängig von der Häufigkeit, mit welcher sich Daten ändern, könnte man als Sicherungskonzept überlegen, einmal am Tag ein volles Backup zu erstellen, ein Differential Backup einmal pro Stunde und die im Folgenden noch beschriebenen Transaktionsbackups alle 5 Minuten beispielsweise.

BACKUP DATABASE [Chair_Planung]
TO  DISK = N'C:\Chair\2019-12-05-14_00 Chair Planung Dif.bak'
WITH  DIFFERENTIAL ,
NOFORMAT, NOINIT, 
NAME = N'Chair_Planung-Vollständig Datenbank Sichern',
SKIP, NOREWIND, NOUNLOAD, 
COMPRESSION,  STATS = 10

Das Transaktions-Backup

Über die Transaktions-Backups lassen sich die einzelnen Änderungen speichern.

BACKUP LOG [Chair_Planung]
TO  DISK = N'C:\Chair\2019-12-05-14_05 Chair Planung Log.bak'
WITH NOFORMAT, NOINIT, 
NAME = N'Chair_Planung-Vollständig Datenbank Sichern',
SKIP, NOREWIND, NOUNLOAD,
COMPRESSION,  STATS = 10

Bestimmte Zeitpunkte markieren

Wie bereits gesagt soll in diesem Beispiel ein bestimmter Zeitpunkt im Planungssystem gesichert werden. Vor dem im Folgenden aufgezeigten Beispielcode müssen zwingend ein vollständiges und empfohlenerweise ein Differential-Backup erfolgt sein.

ALTER PROC P_APP_CreateV1Start as
BEGIN

DECLARE @timestamp NVARCHAR(250)
DECLARE @BackupPath NVARCHAR(500)
DECLARE @TransactionName NVARCHAR(200)

SET @timestamp = FORMAT(GETDATE(),'yyyy-MM-dd-hh-mm')
SET @BackupPath = CONCAT(N'C:\Chair\', @timestamp ,' Chair Planung Log.bak')
SET @TransactionName = 'TX_CreateV1_' + @timestamp

BEGIN TRANSACTION  @TransactionName WITH MARK
       EXEC dbo.P_APP_CreateV1
COMMIT TRANSACTION @TransactionName

BACKUP LOG [Chair_Planung]
TO  DISK = @BackupPath
WITH NOFORMAT, NOINIT, 
SKIP, NOREWIND, NOUNLOAD,
COMPRESSION,  STATS = 10


END

Den Zeitpunkt wiederherstellen

Um den gesicherten Zeitpunkt wiederherzustellen, beginnt man mit dem letzten Vollständigem Backup, spielt das letzte Differential-Backup ein und zuletzt die Transaktions-Backups bis zu dem Punkt, in welchem die Markierung existiert. Dies lässt sich über folgen SQL-Befehl erreichen:

RESTORE LOG [Chair_Planung]
FROM DISK = 'C:\Chair\2019-12-05-09-58-Chair Planung Log.bak'
WITH NORECOVERY, STOPBEFOREMARK = 'TX_CreateV1_2019-12-05-09-58'

Der gesamte Wiederherstellungsprozess

USE [master]
BACKUP LOG [Chair_Planung] -- Erstellen des TAIL Backups
       TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\Chair_Planung_LogBackup_2019-12-16_10-57-24.bak'
       WITH NOFORMAT, NOINIT,  NAME = N'Chair_Planung_LogBackup_2019-12-16_10-57-24', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

RESTORE DATABASE [Chair_Planung] -- Wiederherstellen des Vollständigen Backups
       FROM  DISK = N'C:\Chair\2019-12-05 Chair Planung Full.bak'
       WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE DATABASE [Chair_Planung] -- Wiederherstellen des Differential Backups
       FROM  DISK = N'C:\Chair\2019-12-05-09_00 Chair Planung Dif.bak'
       WITH  FILE = 1,  NORECOVERY,NOUNLOAD,  STATS = 5

RESTORE LOG [Chair_Planung] -- Wiederherstellen bis zum markierten Zeitpunkt
       FROM DISK = 'C:\Chair\2019-12-05-09-58-Chair Planung Log.bak'
       WITH Recovery, STOPBEFOREMARK = 'TX_CreateV1_2019-12-05-09-58'

Die Automatisierung

Die genannten Schritte, lassen sich über den SQL-Agent oder die Wartungspläne steuern.

Auf diese Schritte soll in einem weiteren Blogbeitrag eingegangen werden.