MS SQL Server Express – Backup mit Windows-Bordmitteln

In diesem Aktikel möchte ich zeigen, wie man einen Microsoft SQL-Server Express mit Windows-Bordmitteln auf ein Netzlaufwerk sichert

Dabei vergesse ich auch das Transaktionsprotokoll nicht und richte einen regelmäßigen Datenbankcheck ein.

Einleitung

Für .NET-Entwickler scheint es ja schon eine Reflexhandlung zu sein, die eigenen Daten in einer Microsoft-Datenbank abzulegen. Von A wie Adressdatenbank bis Z wie Zeiterfassung speichert jedes noch so kleine Programm seine Daten im SQL Server ab.

Da die Express-Variante aber den Agent nicht mitbringt, der ab Standard die Backup-Aufgaben steuert, muss man selber Hand anlegen. Zusätzlich ergreifen wir gleich die Gelegenheit, den Server vom Wiederherstellungsmodell ‚Einfach‘ auf ‚Vollständig‘ umzustellen, um so point-in-time Recovery zu ermöglichen.

Das Transaktionsprotokoll nutzen wir beim Backup dann auch, um den Datenverlust bei Totalausfall auf eine Stunde zu begrenzen. Zusätzlich zum automatisierten Vollbackup (gleich auf eine Netzwerkfreigabe) sichern wir also stündlich das Transaktionsprotokoll, was auch den Server weniger belastet als ein Vollbackup.

Wiederherstellungsmodus

Bei den meisten Installationen vom SQL Server Express die ich gesehen habe lief die jeweilige Datenbank im sogenannten einfachen Wiederherstellungsmodus. Hier wird das Transaktionsprotokoll automatisch abgeschnitten, sobald die Transaktionen sicher im dauerhaften Speicher angekommen sind.

Will man aber das Transaktionsprotokoll für eine mögliches Restore wegsichern, muss die Datenbank angewiesen werden das Protokoll aufzuheben. Dazu geht man in die Einstellungen der Datenbank und setzt dort unter ‚Optionen‘ den Wiederherstellungsmodus auf vollständig.

Von nun an hebt der Server das Protokoll solange auf, bis es durch den entsprechenden SQL-Befehl (SAVE LOG …) gesichert wird. Erst dann gibt der SQL-Server den Speicherplatz wieder frei.

Achtung: Wird die Sicherung des Transaktionsprotokolls im Wiederherstellungsmodus vollständig nicht durchgeführt, dann wächst die Logdatei immer weiter. Irgendwann läuft die Festplatte dann voll und die ganze Datenbank steht. Richtet also unbedingt ein Backup des Logs ein, wenn ihr den Wiederherstellungsmodus  umgestellt habt.

Datenbankcheck

Auch das gehört zum best-practice beim SQL Server: DBCC CHECKDB. Mit dem Befehl wird die Datenbank auf mögliche Inkonsistenzen hin überprüft. Wenn der Check etwas findet sind möglicherweise Daten auf immer weg, je früher man ein Problem bemerkt (und evtl. aus Backup+Transaktionsprotokoll wiederherstellt) desto besser.

Man kann zwar ab und zu das Glück haben die Datenbank erfolgreich zu reparieren, aber (gepatchten SQL-Server vorrausgesetzt) trotzdem sollte man der Ursache auf den Grund gehen. Zwei Ursachen kommen vorwiegend in Frage: Speicherfehler (also ECC-Speicher einsetzen, wenn möglich). Ohne ECC-Speicher sind RAM-Fehler nicht zuverlässig nachzuweisen. Oder Probleme mit dem IO-Subsystem (Controller, Verkabelung o.ä.).

Im Idealfall schickt man das Ergebnis per Mail an einen Administrator, der die Ausgabe (hoffentlich) ernst nimmt.

Backup: die Grundidee

für alle drei Aktionen (Vollbackup, Transaktionsprotokollsicherung und Datenbankcheck) gibt es jeweils zwei Dateien:

  • .sql – der SQL-Befehl, mit dem die Aktion auf dem Server ausgelöst wird
  • .bat – das Batchfile, welches den SQL-Befehl ausführt und die Datei dann auf das Netzwerkshare schiebt

Die SQL-Befehle aus den .sql-Dateien werden in den Batchdateien mit dem Programm SQLCMD.EXE auf dem SQL-Server ausgeführt. Der Pfad zur Exe muss je nach SQL Server Version in den Batchfiles noch angepasst werden.

Die .bat-Files machen vor dem Kopieren auf ein Netzwerkshare ein ‚NET USE‘, damit der Laufwerksbuchstabe (U:) auch im gleichen Benutzerkontext existiert wie das Skript. Wenn man ein AD hat wäre das nicht nötig, da könnte man einfach einen UNC-Pfad nehmen.

Die Skripte gehen davon aus, dass:

  •  alle Dateien in c:\Backupskripte liegen
  • c:\sql_backup\ existiert
  • auf dem Share ein Ordner sql_datenbank existiert
  • U: auf dem Rechner nicht in Verwendung ist

Ihr müsst die Skripte noch durchgehen und alles in spitzen Klammern <> ersetzen.

<DATABASE NAME> Name der Datenbank im SQL Server
<HOSTNAME OF SERVER> Rechnername der SQL-Servers
<NET SHARE> UNC-Pfad, auf dem die Sicherungen gemacht werden
<USERNAME> <PASSWORD> Username und Passwort für das Share

Im Screenshot sieht man noch wie ich das im Aufgabenplaner von Windows eingestellt habe. Das ‚Nach Auslösung alle 1 Stunde für die Dauer von 1 Tag…‘ bei ‚Ascend Backup Log‘, welches natürlich logbackup.bat aufruft, sichert das Transaktionsprotokoll jede Stunde.

Die Skripte

Nicht vergessen: die Dateien müssen alle für eure Zwecke (Datenbankname, Rechnername, Share-Pfad etc.) noch angepasst werden!

vollbackup.sql

vollbackup.bat

logbackup.sql

logbackup.bat

checkdb.sql

checkdb.bat

Das Vollbackup im Detail

Beispielhaft nehme ich die Datei vollbackup.bat hier einmal auseinander, um die Funtkionsweise zu erklären.

 "C:\Program Files\Microsoft SQL Server\100\Tools\binn\SQLCMD.EXE" \
         -S <HOSTNAME OF SERVER> -E -i c:\Backupskripte\vollBackup.sql \
         -o c:\Backupskripte\vollBackupLog.txt

Dieser Teil führt den folgenden SQL-Befehl aus der Datei c:\Backupskript\vollBackup.sql aus:

BACKUP DATABASE <DATABASE NAME> TO DISK='c:\sql_backup\sql_full.bak' WITH INIT, NAME = N'SQL Voll'

<DATABASE NAME> ist entsprechend in der Datei zu ersetzen. Der SQL-Befehl macht schlicht ein Vollbackup der Angegebenen Datenbank und schreibt das Ergebnis nach c:\sql_backup\sql_full.bak

rem Zeitstempel erzeugen
for /f "tokens=1-4 delims=. " %%i in ("%date%") do (
  set day=%%i
  set month=%%j
  set year=%%k
)

for /f "tokens=1-4 delims=.:, " %%i in ("%time%") do (
 set hour=%%i
 set minute=%%j
 set second=%%k
 set hundredth=%%l
)

Schon erstaunlich was ein simples Batch-File doch alles so kann 🙂 Die Variablen %date% und %time%, welche das aktuelle Datum bzw. die aktuelle Uhrzeit enthalten, werden hier in ihre Bestandteile (tokens) zerlegt und die ersten 3 bzw 4 davon in entsprechende Variablen abgelegt.

Gut, in bash wäre das ein Einzeiler gewesen, aber nun…Letztlich wird das benutzt, um die Backup-Dateien umzubenennen und dabei mit Datum und Uhrzeit zu versehen.

ren c:\Backupskripte\vollBackupLog.txt FullBackup-%year%-%month%-%day%-%hour%-%minute%.txt
ren c:\sql_backup\sql_full.bak FullBackup-%year%-%month%-%day%-%hour%-%minute%.bak

Die Dateien werden umbenannt.

net use u: <NET SHARE> /user:<USERNAME> <PASSWORD>
 copy c:\sql_backup\FullBackup-%year%-%month%-%day%-%hour%-%minute%.bak U:\sql_datenbank
 net use U: /delete

Das Netzwerkshare wird verbunden, und die vorher umbenannten Dateien werden dorthin kopiert.

Logfile gewünscht?

Wie ihr vielleicht gesehen habt speichern die Batch-Files die Ausgabe des SQL-Servers in einfacht Textdateien. Diese können benutzt werden, um Sie sich per Email zuschicken zu lassen. Der Windows-Taskplaner hat hierzu eine Funktion eingebaut – ist nicht schwer zu finden.

Doch Moment! Werden die Logfiles nicht auch mit umbenannt? Welchen Dateinamen gebt ihr dann dem Taskplaner an? Ganz einfach: Statt die Dateien per ren umzubenennen könnt Ihr die Batchfiles so abändern, dass sie per copy kopiert werden. Die alte Datei bleibt dann erhalten und wird erst beim nächsten Durchlauf überschrieben. Bei checkdb.bat habe ich das bereits vorgemacht – im Taskplaner kann man jetzt also die Datei c:\Backupskripte\checkdbLog.txt verschicken – fertig!

Nobody wants backup. Everyone wants restore!

Tut Euch selbst den Gefallen und testet ein Restore aus dem Backup einmal nach aufsetzen des Backups und danach in gewissen Abständen immer mal wieder.

Nur nach einem erfolgreichen, verifizierten Restore könnt ihr sicher sein, dass das Backup auch geklappt hat.

Viel Erfolg beim Ausprobieren. Fragen bitte an  nj <at> remoteminds.it

Dieser Beitrag wurde unter SQL, Windows veröffentlicht. Setze ein Lesezeichen auf den Permalink.

4 Kommentare zu MS SQL Server Express – Backup mit Windows-Bordmitteln

  1. Ritschi sagt:

    Simpel und darum so genial!
    Made my day.
    JR

  2. tobiask sagt:

    Guten Tag,

    leider funktionieren bei mir die batchdateien nicht 🙁
    die .sql funktionieren ohne Probleme.

    Wenn ich die einzelnen Befehle aus der Batchdatei in cmd einfüge, dann erhalte ich immer die Meldung Zugriff verweigert.

    Ich habe als Pfad allerdings den standard sql server backup ordner genommen.

    Kann mir hier vlt bitte jemand helfen oder einen Tipp geben, woran das liegen könnte?

    Gruß TK

  3. tobiask sagt:

    Okay, jetzt klappts 🙂

    You Made My Day

  4. Carsten sagt:

    Moin,

    bin gerade auf auf deine Anleitung gestoßen und muss sagen das ich diese sehr gut finde.
    Sie ist hervorragend erklärt und hat auf Anhieb funktioniert!

    Vielleicht eine Doofe Frage, aber wie muss ich das Script anpassen, damit die alten DB-Sicherungsdateien nach 3- Wochen gelöscht werden?

    Ansonsten hätte man ja irgendwann eine riesige Datenmenge

Kommentare sind geschlossen.