📖Datenbanksicherung

Sicherungsvarianten

SQL Server unterstützt die folgenden Sicherungsvarianten:

Vollständige Datenbanksicherung: Enthält alle Daten einer bestimmten Datenbank zum Zeitpunkt der Sicherung. Bei einem Restore wird aus dieser Sicherung die komplette Datenbank wiederhergestellt.

BACKUP DATABASE Northwind
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\NORTHWIND.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'NORTHWIND-Vollständig Datenbank Sichern', SKIP, STATS = 10
GO

Option	Beschreibung
WITH FORMAT	Überschreibt die Backupdatei
WITH NOFORMAT (Standard)	Fügt das Backup zur Datei hinzu
NOINIT (Standard)	Belässt den Header eines existierenden Backups
SKIP	Zusammen mit NOINITbelässt es ein existierendes Backups
STATS = N	Schreibt eine Nachricht auf die Konsole für jede NProzent Fortschritt

Differenzielle Sicherung: Basiert auf der letzten vollständigen Sicherung und enthält nur die Daten, die sich geändert haben. Es werden nur jene Datenblöcke gesichert, die sich seit der letzten vollständigen Sicherung geändert haben. Bei einem Restore wird auch die entsprechende Vollsicherung benötigt. Voraussetzung für die differenzielle Sicherung ist also eine vollständige Datenbanksicherung.

BACKUP DATABASE [NORTHWIND]   
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\NORTHWIND_DIFF.bak'   
WITH DIFFERENTIAL;  
GO 

Transaktionsprotokoll-Sicherung: Das Transaktionsprotokoll beinhaltet alle Veränderungen auf der Datenbank, die seit der letzten vollständigen, differenziellen oder Transaktionsprotokoll-Sicherung abgeschlossen wurden. Mithilfe einer Transaktionsprotokollsicherung kann der Zustand der Datenbank bei einem Ausfall bis zu diesem Zeitpunkt wiederhergestellt werden.

/*Nur im Wiederherstellungsmodell "vollständig" möglich und 
wenn bereits eine Datenbanksicherung vorhanden ist*/
BACKUP LOG [NORTHWIND]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\NORTHWIND_LOG.trn'  
WITH INIT, NAME = N'Northwind_Log'
GO

Sicherungsziele

Die gängigste Methode ist, Datenbanksicherungen auf einer Festplatte anzulegen. Alternativ kann auch ein Band gewählt werden. Aus Sicherheitsgründen sollten Datenbank und Sicherung auf separaten Medien platziert werden.

Sicherungsmedium

Ein Sicherungsmedium ist eine Datei, in welcher ein oder mehrere Sicherungssätze gespeichert werden können. Ein Sicherungssatz ist das Ergebnis einer vollständigen, differenziellen oder einer Transaktionsprotokoll-sicherung und wird als .bak-Datei abgelegt. Wenn der gleiche Dateiname für mehrere Sicherungen der Datenbank verwendet wird, wird die Backup-Datei mit den Standardeinstellungen nicht überschrieben, sondern der Sicherungsvorgang wird eine weitere, zusätzliche Sicherung in dieser Datei (Sicherungsmedium) speichern.

Ein Sicherungsmedium ermöglicht es, einfach auf Sicherungsziele zuzugreifen, ohne sich jedes Mal über Pfade und Speicherorte Gedanken machen zu müssen. Dadurch erhöht sich die Übersicht beim Sichern. Beim Anlegen eines solchen Mediums wird ein Ziel festgelegt, auf das in der Folge über den Namen des Mediums zugegriffen werden kann.

Wiederherstellen der Datenbank

RESTORE DATABASE Northwind
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\NORTHWIND.bak'

Wiederherstellen der Datenbank von einer älteren Version

RESTORE DATABASE Northwind
FROM DISK = N'C:\Pfadzum\Backup.bak'
WITH
    MOVE 'Northwind' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Northwind-backup.mdf',
    MOVE 'Northwind_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Northwind-backup.ldf';

Wiederherstellungsmodelle bei SQL Server

Die Wahl der Sicherungsvariante hängt eng mit dem Wiederherstellungsmodell der Datenbank zusammen. Beim Wiederherstellungsmodell handelt es sich um eine Datenbankeigenschaft, welche festlegt, wie SQL Server die Daten zum Transaktionsprotokoll speichert und verwaltet, wenn die Transaktionen abgeschlossen sind. In SQL stehen drei Modelle zur Auswahl:

Einfach: SQL Server verwaltet das zugehörige Transaktionsprotokoll automatisch, inaktive Teile des Protokolls werden automatisch gelöscht. Das Transaktionsprotokoll wird automatisch abgeschnitten, sobald die Transaktionen sicher im dauerhaften Speicher angekommen sind. Das Transaktionsprotokoll wird somit immer klein gehalten und kann nicht unbegrenzt wachsen. Nachteil: Das Transaktionsprotokoll kann nicht extra gesichert werden. Dieses Modell wird nur bei Test- oder Entwicklungsdatenbanken empfohlen.

Vollständig (point-in-time recovery): Es werden alle Transaktionen im Protokoll gehalten und der inaktive Teil nicht gelöscht. Erst wenn die Transaktionsprotokollsicherung ausgeführt wird, wird der inaktive Teil gelöscht. Im Fall einer Wiederherstellung der Datenbank wird zuerst die DB Sicherung und danach die Transaktionsprotokollsicherung eingespielt. Denn nur mit der Transaktionsprotokollsicherung erreicht man, dass alles bis zur letzten Datenbankänderung restored wurde. Nachteil: Das Transaktionsprotokoll kann schnell ziemlich gross werden, falls nicht laufend eine Transaktionsprotokoll- Sicherung durchgeführt wird und dieses gelöscht wird. In diesem Fall läuft man Gefahr, dass die Festplatte voll und die Datenbank lahmgelegt wird. Dieses Modell wird üblicherweise bei Produktionsdatenbanken verwendet.

Massenprotokolliert: Ähnlich wie beim vollständigen Modell, das Transaktionsprotokoll wächst allerdings nicht so schnell. Diese Option wird vor allem bei Datenbanken mit vielen Massenoperationen (bulk inserts) gewählt.