Zielgruppe

Dieser Artikel wendet sich an Einsteiger in das Thema Backup und Recovery beim SQLServer. Behandelt werden hier die Grundlagen in einfachen Szenarien. Alle weitergehenden Informationen sind in der Online-Hilfe zu finden, wo auch verschiedene Backup-Szenarien ausführlich besprochen werden.

...

Inspiration

Immer wieder kann man in Newsgroups oder Foren Fragen wie diese lesen:
Wir erstellen Sicherungen mit Software XYZ. Jetzt ist unsere Datenbank kaputt. Wie geht der Restore? Wie lange dauert das?

Die Fragesteller erwarten dann in der Regel Antworten, die sie bereits vor dem produktiven Einsatz der Software selbst beantworten können sollten. Jedem Leser ist also der Test von verschiedenen Backup- und Restore-Szenarien empfohlen und eine regelmässige Wiederholung dieser Versuchsreihen sollte zur Sicherstellung des Serverbetriebs gehören.

Welche unterschiedlichen Backups gibt es?

Hierzu sollte man sich noch einmal verdeutlichen, dass eine Datenbank aus zwei logischen Teilen gehört. Zum einen sind dies die Daten (das mdf-File), zum anderen das Transaktionslog (das ldf-File). Alle Aktionen werden auf den Daten ausgeführt und im Transaktionslog protokolliert. So erzeugt z. b. ein
Create Table T(Feld integer)
eine leere Datenbank-Tabelle und gleichzeitig wird die Information im Transaktionslog abgelegt.

Daraus folgt, dass es unterschiedliche Arten von Backups gibt, die man sinnvoll kombinieren kann. Alle Sicherungen enthalten auch die Änderungen die während der Sicherung gemacht (committed) werden.

Vollständig

• Sichert die komplette Datenbank

Inkrementell

• Sichert die Änderungen seit der letzten inkrementellen Sicherung

Differenziell

• Sichert die Änderungen seit der letzten vollständigen Sicherung

Datei- oder Dateigruppe

• Sollte nur verwendet werden, wenn die vollständige Datenbanksicherung aufgrund von Leistungsanforderungen nicht möglich ist.
• Wenn man mehrere Dateien für eine Datenbank verwendet, kann man diese einzeln sichern und wiederherstellen.

Spezialfall Copy Only

Falls eine Kopie einer Datenbank z. B. für Testzwecke angelegt werden soll, ohne die Kette von vollständigen und differenziellen Sicherungen zu unterbrechen, kann eine vollständige Sicherung mit der Option COPY ONLY erstellt werden. Die nächste differenzielle Sicherung wird sich dann wieder auf die letzte ordentliche vollständige Sicherung beziehen und alle Änderungen seit dem Zeitpunkt beinhalten.

Was bedeutet das Wiederherstellungsmodell?

Zum Thema Wiederherstellungsmodell gehört auch immer die Frage: Woher kommt die Einstellung bei neuen Datenbanken? Auf jedem SQLServer gibt es auch eine Modell-Datenbank, die als Vorlage für neue Datenbanken verwendet wird. Will man bestimmte Optionen für alle neuen Datenbanken einstellen, macht man dies am besten hier. Bei bestehenden Datenbanken ändert man dies über die GUI unter Optionen oder mit ALTER DATABASE.
Man unterscheidet folgende Wiederherstellungsmodelle:

Einfach (Simple)

• Protokollspeicherplatz kann automatisch erneut verwendet werden, wenn er für die Wiederherstellung nach einem Serverfehler nicht mehr benötigt wird.
• Bietet den geringsten Schutz vor Datenverlust
• Keine Transaktionslogsicherungen möglich

Massenprotokolliert (Bulk_Logged)

• Protokollspeicher wird für Massenvorgänge (bcp, Bulk Insert, Select Into, Create Index, Alter Index) nur geringfügig verwendet.
• Nachteil: Falls während solcher Massenvorgänge ein Fehler auftritt, kann die Datenbank nicht bis zum Zeitpunkt des Fehlers wiederhergestellt werden, sondern nur bis zur letzten Transaktion vor dem Start des Massenvorgangs.
• Vorteil: Das Transaktionslog wächst bei grossen Massenvorgängen nicht so stark an. Es wird weniger Plattenplatz benötigt, die Sicherungen sind kleiner, LogShipping geht schneller.
• Transaktionslogsicherungen notwendig

Vollständig (Full)

• Protokollspeicher wird belegt, bis eine Transaktionssicherung erfolgt ist.
• Bietet in Verbindung mit Transaktionslog-Sicherungen den besten Schutz gegen Datenverlust. Die Datenbank kann bis zum Zeitpunkt des Fehlers wiederhergestellt werden.
• Transaktionslogsicherungen notwendig

Wie kann man dies automatisieren?

Beim SQLServer gibt es eine recht einfache Möglichkeit der Automatisierung durch Wartungspläne. Die hier gewählten Schritte werden durch einen SQLServer-Agent Auftrag regelmässig ausgeführt. So könnte man sich folgende Kombination vorstellen:

• Sonntag - Vollständige Sicherung
• Montag bis Samstag (abends) - Differenzielle Sicherung
• alle 30 Minuten - Transaktionsprotokoll-Sicherung

Im Management Studio findet man unter Verwaltung die Wartungspläne. Mit der rechten Maustaste ruft man das Kontextmenü auf und wählt dort den Wartungsplan-Assistenten.

Plan definieren

Nachdem man dem Plan einen sprechenden Namen und eine Beschreibung mitgegeben hat, kann man entweder einen Zeitplan wählen, oder die Standard-Einstellung wie in diesem Beispiel beibehalten.

Im nächten Schritt wählt man die drei Optionen für die verschiedenen Sicherungen.

Die Taskreihenfolge läßt man wie vorgeschlagen und fährt mit der Definition der vollständigen Sicherung fort.

Sicherungen definieren

Als erstes definiert man logischerweise welche Datenbank gesichert werden soll. Es stehen verschiedene Möglichkeiten zur Auswahl:

• Alle Datenbanken
• Systemdatenbanken
• Alle Benutzerdatenbanken
• Diese Datenbanken

Falls man (wie in diesem Beispiel) nur eine Datenbank sichern möchte, wählt man den Namen der Datenbank aus der Liste aus und bestätigt die Auswahl mit Ok. Die Auswahl der Datenbank wird im ersten Fenster durch den Text "Bestimmte Datenbanken" bestätigt.

Für die Grundlagen beim Backup beschränken wir uns zunächst auf das Ziel des Backups. In der Regel wird dies eine Festplatte sein. Zur einfacheren Unterscheidung der Backups läßt man den SQL Server ein Unterverzeichnis pro Datenbank anlegen. Falls man auf dem lokalen Server nicht mehr genug Plattenkapazität hat, kann man auch den Sicherungspfad als UNC-Pfad angeben. Die Auswahl eines Netzwerklaufwerks wird allerdings nicht über die Auswahl mit den drei Punkten unterstützt. Ganz wichtig ist es sicherzustellen, dass das Konto, unter dem der SQL Server-Dienst läuft ausreichende Rechte auf dem Sicherungspfad hat. Damit im Falle eines Hardware-Fehlers nicht die Datenbank und die Sicherungen gleichzeitig beschädigt werden, sollte die Sicherung immer auf einem anderen Laufwerk als die Datenbank liegen.

Die Sicherungsintegrität sollte nach der Sicherung möglichst überprüft werden, damit man nicht durch unentdeckte Plattenfehler Fehler im Backup erhält. Ab der Version SQLServer 2008 gibt es in der Enterprise Edition auch die Möglichkeit komprimierte Backups zu schreiben. Die Auswahl dazu findet sich allerdings auch in der GUI der Standard Edition wieder. Wählt man bei einer Standard Edition aber "Sicherung komprimieren" erhält man später bei der Ausführung den folgenden Fehler:

Code: 0xC002F210
Source: Datenbank sichern (vollständig)
Execute SQL Task
Description: Executing the query "BACKUP DATABASE [PASS_DB] TO  DISK = N'D:\MSSQL10...." failed with the following error:
"BACKUP DATABASE WITH COMPRESSION is not supported on Standard Edition.  BACKUP DATABASE is terminating abnormally.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or
connection not established correctly.
End Error  

Wählt man die für die Edition passende Komprimierung aus, sollten sich aber keine Probleme ergeben.
Nun sollte man sich etwas mit der Zeitplanung befassen. Auch wenn Sicherungen im Tagesbetrieb kein Problem darstellen, wird man vollständige oder differenzielle Sicherungen gerne zum Ende des Tages durchführen. Ich wähle hier jeweils 20:00 Uhr für diese Aufgaben.
Da wir im ersten Schritt gewählt haben, dass es für jede Aufgabe einen getrennten Zeitplan geben soll, sollten wir hier nun auch die Definition des Zeitplans durchführen. Im Beispiel wird die vollständige Sicherung Sonntags um 20:00 Uhr durchgeführt.

Danach kommen die Einstellungen für differenzielle Backups. Diese unterscheiden sich nicht von denen für vollständige Backups. In meinem Beispiel wird die differenzielle Sicherung Montags bis Samstags um 20:00 Uhr durchgeführt. Gehen wir nun gleich weiter zu den Transaktionsprotokoll-Sicherungen.

Hier unterscheidet sich die Standard-Dateiendung von denen bisher, da es nun nicht mehr bak, sondern trn heißt. In meinem Beispiel wird die Transaktionsprotoll-Sicherung täglich ab 0:00 Uhr alle 30 Minuten durchgeführt.

Protokollierung

Im nächsten Schritt werden die Berichtsoptionen ausgewählt. Leider hat der Verlauf im SQLServer-Agent bei Sicherungsproblemen in der Regel keine ausführlichen Kommentare, schaltet man aber die Berichtsoptionen ein, so erhält man ggf. eine recht sprechende Fehlermeldung in der dort definierten Log-Datei.

Unterpläne

Im Beispiel haben wir einen Wartungsplan mit drei Unterplänen angelegt. Im SQLServer-Agent gibt es jetzt drei Aufträge, deren Name mit Subplan_1 bis 3 aufhört. Um dies zu ändern, öffnet man den Wartungsplan per Doppelklick und öffnet danach den Unterplan per Doppelklick. Nun kann man auch den Namen des Unterplans ändern. Nachdem der Wartungsplan abgespeichert ist, sind auch die Namen der Aufträge korrigiert.

Automatisierung bei der Express Edition

Leider gibt es für die Express Edition nicht diese komfortable Möglichkeit zur Generierung von Wartungsplänen. Mittlerweile gibt es aber zumindest eine Alternative im Netz. Dort werden neben den Sicherungen auch die anderen Wartungsplan-Aufgaben wie Reorganisation von Indizes angesprochen.

Fehlersuche beim Backup

Läßt man jetzt die oben definierten Sicherungen für eine neue Datenbank ausführen, erhält man bereits bei der ersten Transaktionsprotokoll-Sicherung einen Fehler. Im Serverprotokoll erscheinen dazu zwei Meldungen:

Datum                02.02.2009 14:20:03
Protokoll            SQL Server (Aktuell - 02.02.2009 14:20:00)
Quelle               Backup
Meldung
Error: 3041, Severity: 16, State: 1.

Datum                02.02.2009 14:20:03
Protokoll            SQL Server (Aktuell - 02.02.2009 14:20:00)
Quelle               Backup
Meldung
BACKUP failed to complete the command BACKUP LOG PASS_DB. Check the backup application log for detailed messages.

Diese Informationen sind erst mal nicht sehr vielsagend. In diesem Falle erhält man aber wenigstens im Verlauf des Auftrags einen Hinweis auf die Ursache:

Execute SQL Task
Description: Executing the query "BACKUP LOG [PASS_DB] TO  DISK = N'D:\MSSQL10.MSSQL..." failed with the following error:
"BACKUP LOG cannot be performed because there is no current database backup.  BACKUP LOG is terminating abnormally."

Falls noch nie eine vollständige Sicherung erstellt wurde, kann auch keine differenzielle oder Transaktionsprotokoll-Sicherung erstellt werden.

In anderen Fällen kann es bei Sicherungen oder Wartungsplänen generell zu Fehlern kommen, die nicht im Auftragsverlauf ersichtlich sind. In diesem Falle sollte der oben eingestellte Bericht konsultiert werden, der sich in der Regel auf dem Server im Log-Verzeichnis befindet.

Zeitgenaue Wiederherstellung einer Datenbank nach "Drop Table"

Falls man die oben beschriebenen Sicherungen regelmässig durchführt, sollte man auch immer mal wieder den Ernstfall proben. Was ist zu tun, wenn ein Anwender oder Hardware-Fehler die Datenbank beschädigt?

Vorgehensweise

Die Vorgehensweise ist einfach:

• Erstelle noch eine letzte Transaktionsprotokoll-Sicherung, in der auch noch die letzten Änderungen enthalten sind. Falls die Datendatei (mdf) wegen Hardware-Fehlern ausgefallen ist, kann durchaus noch eine Sicherung des Transaktionsprotokolls durchgeführt werden. Dies ist einer der Gründe, warum dringend empfohlen wird die beiden Dateien nicht auf demselben Laufwerk abzulegen. Wer die Screenshots dieses Beispiels genau liest, wird vielleicht merken, dass hier beide Dateien auf demselben Laufwerk liegen. Dies hat aber nur den Hintergrund, dass die Screenshots auf einem reinen Test-System entstanden sind. Auch sollte die Sicherung wie bereits erwähnt auf einem anderen Laufwerk als die Datenbank liegen.
• Zurücksicherung der vollständigen Sicherung (ohne die Datenbank wiederherzustellen)
• Zurücksicherung der differenziellen Sicherung (ohne die Datenbank wiederherzustellen)
• Zurücksicherung aller Transaktionsprotokoll-Sicherungen, die nach der differenziellen Sicherung erstellt wurden, bis zu dem Zeitpunkt, an dem der Fehler aufgetreten ist. Falls es ein Hardware-Fehler bei der Daten-Datei war, kann die eben abschliessend erstellte Transaktionsprotokoll-Sicherung vollständig angewendet werden. Falls es aus anderen Gründen (wie z. B. Anwender-Aktionen) zu Datenverlusten gekommen ist, sollte nur bis kurz vor den Zeitpunkt des Datenverlusts zurückgesichert werden.

ACHTUNG: Falls die vollständige Sicherung verloren gegangen ist, sind die differenzielle Sicherung und die Transaktionsprotokoll-Sicherungen wertlos geworden!

Zeitpunkt auswählen

Der Zeitpunkt bis zu dem wiederhergestellt werden soll, wird im TSQL mit der Klausel STOPAT angegeben. Die GUI erleicht hier die Handhabung etwas.

Wiederherstellungsstatus

Ganz wichtig ist, dass die Datenbank solange ohne Wiederherstellung zurückgesichert wird, bis auch die letzte Transaktionsprotokoll-Sicherung angewendet wurde. Der TSQL Restore-Befehl hat hierzu die Option WITH NORECOVERY. Nach der letzten Rücksicherung wird die Datenbank im TSQL mit der OPTION RECOVERY wieder zugänglich gemacht.
Auch in der GUI findet sich diese Option wieder. Hier wird allerdings nur die Option angegeben, die nach der Rücksicherung der letzten Sicherungsdatei angewendet werden soll. Alle Zwischenschritte erledigt die GUI selbstverständlich auch mit NORECOVERY, da ansonsten weitere Backups nicht angewendet werden könnten.

Nach Abschluss dieser Aktion steht die Datenbank wieder für die Anwender zur Verfügung.

Platte voll?

Wer die Lektüre dieses Artikels an dieser Stelle beendet und sich über die erfolgten Sicherungen freut, wird in einiger Zeit merken, dass sein Plattenplatz knapp wird. Der Wartungsplan-Assistent sieht zwar bereits bei der Erstellung eines Wartungsplans vor entsprechende Aufräumarbeiten zu definieren, ich habe es mir allerdings zur Gewohnheit gemacht, diese Aufräumarbeiten mit den Sicherungen zu kombinieren um die Abhängigkeit klar zu definieren. Auch benötigt man zwei verschiedene Aufräumjobs, wenn man mit verschiedenen Dateiendungen gearbeitet hat und unterschiedliche Aufbewahrungsfristen realisieren will.

Es sollen folgende Regeln gelten:

• Wenn eine Transaktionsprotokoll-Sicherung erstellt wurde, werden Transaktionsprotokoll-Sicherungen älter als 5 Tage gelöscht.
• Wenn eine vollständige Sicherung erstellt wurde, werden vollständige Sicherungen und differenzielle Sicherungen älter als 3 Wochen gelöscht.
• Wenn eine vollständige Sicherung erstellt wurde, werden der Wartungsplanverlauf und die Sicherungs- und Wiederherstellungs-Historie älter als 4 Wochen gelöscht.

Transaktionsprotokoll-Sicherungen löschen

Man öffnet den Wartungsplan und wählt den entsprechenden Unterplan für die Transaktionsprotokoll-Sicherungen aus. Dann zieht man aus der Toolbox den Task 'Wartungscleanup' unter den dort vorhandenen Task. Wenn man den vorher vorhandenen Task markiert, erscheint ein grüner Pfeil für den Fortgang der Aktion im Erfolgsfall. Diesen Pfeil zieht man auf den neuen Task 'Wartungscleanup'.

Durch einen Doppelklick lässt sich jetzt dieser Task öffnen und wie folgt einstellen:

• Sicherungsdateien löschen
• Im Pfad, der vorher für die Erstellung der Sicherungen verwendet wurde
• Bei Transaktionsprotokoll-Sicherungen haben wir die Endung trn verwendet
• Falls wir mehrere Datenbanken im Wartungsplan haben, sollen auch die Sicherungen aus den Unterverzeichnissen gelöscht werden
• Die Dateien werden anhand ihres Datums gelöscht, wobei die Grenze bei 5 Tagen liegen soll

Vollständige und Differenzielle Sicherungen löschen

Analog ist das Vorgehen für den Task mit den vollständigen Sicherungen.

Man öffnet den Wartungsplan und wählt den entsprechenden Unterplan für die vollständige Sicherung aus. Dann zieht man aus der Toolbox den Task 'Wartungscleanup' unter den dort vorhandenen Task. Wenn man den vorher vorhandenen Task markiert, erscheint ein grüner Pfeil für den Fortgang der Aktion im Erfolgsfall. Diesen Pfeil zieht man auf den neuen Task 'Wartungscleanup'. Durch einen Doppelklick lässt sich jetzt dieser Task öffnen und wie folgt einstellen:

• Sicherungsdateien löschen
• Im Pfad, der vorher für die Erstellung der Sicherungen verwendet wurde
• Bei vollständigen und differenziellen Sicherungen haben wir die Endung bak verwendet
• Falls wir mehrere Datenbanken im Wartungsplan haben, sollen auch die Sicherungen aus den Unterverzeichnissen gelöscht werden
• Die Dateien werden anhand ihres Datums gelöscht, wobei die Grenze bei 3 Wochen liegen soll

Verlauf bereinigen

Auch der Verlauf dieser Aktionen, der in Tabellen der msdb abgespeichert wird, sollte gelegentlich bereinigt werden.

Man öffnet den Wartungsplan und wählt den entsprechenden Unterplan für die vollständige-Sicherung aus. Dann zieht man aus der Toolbox den Task 'Verlaufscleanup' unter den dort vorhandenen Task. Wenn man den vorher vorhandenen Task markiert, erscheint ein grüner Pfeil für den Fortgang der Aktion im Erfolgsfall. Diesen Pfeil zieht man auf den neuen Task 'Verlaufscleanup'. Durch einen Doppelklick lässt sich jetzt dieser Task öffnen und wie folgt einstellen:

• Sicherungs- und Wiederherstellungsverlauf löschen
• Wartungsplanverlauf löschen
• Daten, die älter als 4 Wochen sind, sollen gelöscht werden

Den Auftragsverlauf des SQLServer-Agents bereinige ich nie, da hier bereits bei der Definition des SQLServer-Agents eine Obergrenze an Zeilen definiert wurde. Wenn diese überschritten wird, werden die ältesten Zeilen automatisch gelöscht. Je nach Intervall der Aufträge, kann es also sein, dass diese Daten nur für einige Tage oder aber auch für einige Wochen zur Verfügung stehen.

Schlussbemerkung

Dieser Artikel bietet einen ersten Einstieg in die Thematik Sichern und Wiederherstellen von SQLServer-Datenbanken. Darüber hinaus gibt es noch viele Optionen und Szenarien, die noch mehr Möglichkeiten eröffnen. Es wird aber dringend empfohlen sich mit diesen Grundlagen vertraut zu machen und sie regelmäßig zu üben. Damit erspart man sich im Ernstfall hektische Betriebsamkeit, die zu Folgefehlern führen kann.