SQL Server Express Backups automatisieren

von Philipp Stiefel, ursprünglich veröffentlicht am 9. Februar 2017

Basierend auf einem Foto von Cz_Miki, hier verwendet unter CC0 Lizensierung

Dies ist der zweite Teil einer dreiteiligen Serie. Wenn du bereits mit SQL-Server-Backups vertraut bist und nur wissen möchtest, wie du ein automatisches Backup deiner Datenbank auf einem SQL Server Express, ohne den SQL Agent, implementieren kannst, dann lies hier weiter.

Wenn du bisher noch nie mit SQL Server Datensicherung zu tun hattest, dann empfehle ich dir dringend, bei dem ersten Artikel der Serie anzufangen und dort erstmal zu den theoretischen Grundlagen der SQL Server Datensicherung nachzulesen.

SQL Server Express

Microsoft SQL Server Express ist die kostenlose Edition von Microsofts SQL Server. SQL Server und besonders die Express Edition sind der natürliche Upgrade-Pfad von einer Access Anwendung mit reinem Access Backend, die in Bezug auf die Größe, die Anzahl der Benutzer und/oder die Anforderungen an die Sicherheit aus Access herausgewachsen ist.

SQL Server fehlt eine wichtige Komponente, die du brauchst um darauf eine produktive Datenbank für eine Abteilung oder ein kleines Unternehmen zu betreiben. Es fehlt der SQL Agent, die Komponente um vordefinierte Aufgaben, einschließlich Backups, zu planen und automatisch ausführen zu lassen.

Da SQL Express die Backup Funktionalität des SQL Servers voll unterstützt, kannst du manuelle Backups im SQL Server Management Studio genauso machen, wie du es mit eine „Vollversion“ des SQL Servers machen würdest. Was jedoch nicht möglich ist, weder über das UI, noch über SQL-Scripte, ist die Backups zeitplangesteuert automatisch erstellen zu lassen.

Manuelle Backups für produktive Daten ist keine echte Option. Eines Tages wirst du vergessen ein Backup zu machen. Und, Murphy’s Law zufolge, wird am folgenden Tag etwas Katastrophales mit deiner Datenbank passieren. Also, wenn deine SQL Express Datenbank nicht nur ein Replikat einer Datenbank ist, die auf einem großen Server beheimatet ist und dort regelmäßig gesichert wird, musst du vom ersten Tag an darüber nachdenken, deine Datenbank zu sichern.

Windows Aufgabenplaner

Die naheliegende Lösung für beliebige Planungsaufgaben auf einem Windows Computer ist die Windows Aufgabenplanung, die mit jeder Edition von Microsoft Windows seit Windows 98 ausgeliefert wurde.

Meine erste Idee war, das praktische Befehlszeilen-Wartungstools des SQL Servers (sqlmaint.exe) zu verwenden. Dies hätte es uns ermöglicht, Datenbank-Backups direkt von der Befehlszeile der Aufgabenplanung zu erstellen. - Leider funktioniert dieses Tool nicht mit einer reinen SQL Express Installation.

Das universelle Tool um von der Befehlszeile auf eine SQL Server Instanz zuzugreifen ist der sehr nützliche Befehlszeilen-Client SQLCMD. SQLCMD kann sich zu einem SQL Server verbinden und ermöglicht es dir, beliebige SQL-Befehle oder auch ein komplettes Script von der Befehlszeile aus auszuführen.

Der BACKUP Befehl in T-SQL

Um die integrierte Backup-Funktionalität des SQL Servers ohne UI-Interaktion zu verwenden, führst du einfach den BACKUP Befehl in Transact SQL aus.

Der Backup Befehl hat genauso viele Optionen, wie der Backup Dialog im SSMS, aber zum Sichern einer SQL Express Datenbank wirst du wahrscheinlich nur die einfachsten Optionen verwenden.

Die minimale Anweisung für ein Backup ist diese:

Dies sichert einfach die Datenbank deineDatenbank in eine Datei deineDatenbank.bak im Dateisystem. Du kannst entweder einen vollständigen Dateipfad oder einen Pfad relativ zum vorkonfigurierten Backup Verzeichnis der SQL Server Installation übergeben.

Der Sinn und Zweck diese Artikel ist, den Backup Prozess zu automatisieren. Daher müssen wir ein wenig zusätzliche Logik um diesen einfachen Aufruf herumbauen, um jedes Mal einen neuen, einmaligen Dateinamen zu verwenden, wenn wir ein Backup erstellen.

Der Dateiname, den wir hier an den BACKUP Befehl übergeben, ist nicht die unmittelbare Repräsentation unserer Datenbank. Es ist ein Sicherungsmedium (Backup Device), wie im vorigen Artikel beschrieben.

SQLCMD Befehlszeilenoptionen

SQLCMD hat einen ganzen Haufen Befehlszeilenoptionen. Ich werde hier nur die Optionen beschreiben, die wir hier auch tatsächlich verwenden werden.

Achtung, Groß-/Kleinschreibung macht einen Unterschied! Die Kleinbuchstaben haben eine gänzlich andere Bedeutung als die entsprechenden Großbuchstaben.

Ein einfacher Sicherungsbefehl

Wie wir in dem vorigen Artikel der Serie gelernt haben, kannst du entweder eine vollständige Sicherung oder eine Differentialsicherung einer SQL Datenbank erstellen. Beides wird mit dem BACKUP Befehl erledigt.

Für die vollständige Sicherung einer Datenbank musst du nur den Datenbanknamen, den Ausgabe-Gerätetyp (DISK oder TAPE) und den Namen bzw. Pfad zu der Sicherungsdatei übergeben.

Hier ist ein Beispiel:

(dasselbe wie oben)

Wenn du bereits eine vollständige Sicherung deiner Datenbank hast und du nur eine zusätzliche Differenzielle Sicherung durchführen willst um Zeit und Speicherplatz zu sparen, dann gibt’s du zusätzlich die Option WITH DIFFERENTIAL in dem Befehl an.

Um diesen Backup Befehl mit SQLCMD auszuführen, kannst du diese Befehlszeile verwenden:

"C:\pfad\zu\SQLCMD.exe" ^
-Q "BACKUP DATABASE DeineDatenbank TO DISK = 'DeineSicherungsdatei.bak' WITH DIFFERENTIAL;" ^
-E -S DeinServer -b

(Beachte: ^ ist das Zeilenfortsetzungszeichen in DOS. Dies ist eine Befehlszeile auf mehrere Zeilen verteilt.)

Du kannst einen neuen Task im Windows Tasks Scheduler anlegen und dort eine Programm Starten -Aktion definieren und dort die obige Befehlszeile als Programm/Skript eintragen.  

Mein Backup Script

Der soeben erstellte Task würde ein einfaches Backup erstellen. Ich bin allerdings noch nicht zufrieden. Ich würde gern etwas mehr Logik in mein Script integrieren.

Bevor ich dir das Script zeige, das ich letztendlich geschrieben habe, möchte ich nochmal die Anforderungen an mein Datenbank-Sicherungsscript zusammenfassen.

1. Ich möchte einmal täglich eine vollständige Sicherung meiner Datenbank erstellen und dann alle zwei Stunden eine Differenzielle Sicherung.
2. Alle Sicherungen eines Tages sollen in derselben Datei (Backup Device) gespeichert werden, um ein einfaches Management der Aufbewahrungszeiten und ggfls. eine einfache Wiederherstellung zu ermöglichen.
3. Die Integrität der Backups soll automatisch nach ihrer Erstellung überprüft werden.
4. Wenn ein Backup fehlschlägt, soll eine Email an mich gesendet werden.

Ich könnte die Logik, um das zu erreichen, in einem Batch- oder PowerShell-Script unterbringen, aber ich fühle mich mit T-SQL wesentlich wohler. Also schreibe ich all dies, abgesehen von der Email-Funktionalität, in ein T-SQL-Script.

Hier ist nun mein Script:

Ich denke, ich sollte ein paar Details des Scriptes erklären.

Die SET @VariableName  = $(VariableName); Befehle zu Beginn des Scriptes dienen dazu das Script flexibel zu halten. Ich kann diese Variablen von SQLCMD an das SQL-Script übergeben indem ich sie in dem -v Argument der Befehlszeile angebe. Somit kann ich ein und dasselbe Script verwenden um verschiedene Backup-Arten für verschiedene Datenbanken zu erstellen. Die Ausdrücke $(VariableName) werden von SQLCMD durch die übergebenen Werte ersetzt, bevor das Script ausgeführt wird.

Wenn kein expliziter Name für die Backupdatei übergeben wird, generiere ich den Dateinamen aus dem Datenbanknamen, dem aktuellen Datum und dem Backup-Typ in der Set @OutputFileName = … Anweisung. Der Backup-Typ AUTO bewirkt, dass ein vollständiges Backup und anschließend Differenzielle Backups für jeden Tag in ein und dieselbe Backupdatei (Backup Device) geschrieben werden.

Um zu entscheiden, ob das Script ein vollständiges Backup oder ein differenzielles Backup erstellen muss, rufe ich RESTORE LABELONLY auf. Die RESTORE LABELONLY-Anweisung versucht die Inhalte einer bestehenden Backupdatei zu lesen. Wenn die Datei bereits existiert, muss ja bereits ein vollständiges Backup existieren, daher wird dann ein Differenzielles Backup erstellt. Wenn die Datei nicht existiert, wird ein Fehler auftreten, der in dem CATCH-Block behandelt wird. Das Script erstellt dann ein vollständiges Backup.

Nachdem ein neues Backup in die Datei geschrieben wurde, verwende ich die RESTORE VERIFYONLY-Anweisung um die Integrität der Backupdatei zu überprüfen. Diese wird einen Fehler auslösen, wenn es ein Problem mit der Backupdatei gibt.

Die Befehlszeile, um das Script auszuführen, könnte so aussehen:

"C:\path\to\SQLCMD.exe" -E -S DeinServer -b ^
-i "C:\path\to\BackupScript.sql" ^
-v BackupDB="'DeineDB'" BackupType="'AUTO'" OuputDir="NULL" OutputFileName="NULL"

Um diese Befehlszeile in deiner Umgebung auszuführen, musst du meine Dummy-Variablenwerte so ändern, dass sie auf deinem Computer gültig sind. Kopiere diese Befehlszeile in das Programm/Skript Eingabefeld einer neuen Aufgabe in der Windows Aufgabenplanung. Dann stelle ein sinnvolles Ausführungsintervall (in meinem Fall alle zwei Stunden) ein und speichere die Aufgabe. – Voila, du hast ein regelmäßiges, automatisches Backup deiner SQL Express Datenbank geplant.

Aber Moment! Meine letzte Anforderung fehlt noch.

Email beim Fehlschlag des Backups

Ich möchte eine Email gesendet bekommen, wenn die Sicherung fehlschlägt. Normalerweise wäre das eine Kleinigkeit. Du würdest einfach die msdb.dbo.sp_send_dbmail-Prozedur des SQL Servers aufrufen und die Email würde mit Database Mail gesendet werden. – Unglücklicherweise ist Database Mail ein weiteres Feature, das in der SQL Express Edition nicht vorhanden ist.

Du kannst im Internet ein paar grobe Hacks finden, wie man Database Mail in der Express Edition zum Laufen bekommt. Allerdings ist Database Mail in der Editionen-Feature-Matrix explizit für die Express Edition als nicht verfügbar aufgeführt. Daher würde ich es als Lizenzverletzung betrachten, es dennoch zu aktivieren.

In meinem Szenario war es die einfachste Alternative, die Email mit dem Powershell Cmdlet Send-MailMessage zu senden. Ich habe zu diesem Zweck den Aufruf der Backup Anweisung in ein kleines (2 Zeilen!) cmd-Script verlagert. Die erste Zeile startet einfach nur unser Backup auf SQL Express, mit der gleichen Befehlszeile, die wir in der Windows Task Scheduler Aufgaben verwendet haben, die zweite Zeile sendet eine Email, wenn das error level aus der vorigen Anweisung 1 oder höher ist.

@ECHO OFF

"C:\path\to\SQLCMD.exe" -E -S YourServer -b ^
-i "C:\path\to\BackupScript.sql" ^
-v BackupDB="'YourDb'" BackupType="'AUTO'" OuputDir="NULL" OutputFileName="NULL"

IF ERRORLEVEL 1 "%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe" ^
-command "& {Send-MailMessage ^
-SmtpServer 'mail.server.invalid' ^
-From 'backup@server.invalid' ^
-To 'admin@server.invalid' ^
-Subject 'Database backup failed' ^
-Priority 'High'}"

(Erinnerung: ^ ist das Zeilenfortsetzungszeichen in DOS. Es funktioniert auch in einem Batch-Script.)

So, nun haben wir endlich eine funktionierende Lösung, die alle meine Anforderungen erfüllt.

Aber warte, es geht noch weiter…

Eine Gespeicherte Prozedur anstelle eines Scripts verwenden

Gut, die obige Lösung funktioniert. Es ist nicht technisch falsch daran. – Außer…  Ich hasse sie! Wenn ich die SQLCMD-Variablen in dem Script ansehe, die dieses Script mit ihren verflochtenen Abhängigkeiten exklusiv an einen Aufruf mit SQLCMD binden, wird mir übel.

Ich bevorzuge eine etwas andere Lösung. Alle Anweisungen in dem Script, die das eigentliche Backup steuern bleiben dieselben. Aber, anstatt sie in einem einfachen Script zu belassen, erstelle ich lieber eine Stored Procedure (Gespeicherte Prozedur) die das Backup ausführt.

Nun, wo speichern wir diese Stored Procedure? Genauso wie das Script, kann diese Store Procedure verwendet werden um eine beliebige Datenbank auf dem Server zu sichern. Wo sind üblicherweise administrative, server-weit gültige Prozeduren zu finden? – Ja, richtig, in der master Datenbank.

Mach dir keine Sorgen. Es ist absolut in Ordnung deine eigenen Stored Procedures in der master Datenbank zu erstellen. – Wenn es sich um administrative Hilfsprozeduren handelt. Übertreib es nicht und erstelle nicht einen Haufen Kram in der master Datenbank, aber in klar definierten Fällen, wie diesem ist das OK. Behalte den Überblick darüber, welche eigenen Prozeduren du in der master Datenbank angelegt hast. Wenn du einen anderen SQL-Server auf einem anderen Rechner installierst, musst du diese Dinge dort erneut manuell erstellen. – Versichere dich aber immer, dass der verantwortliche Datenbank Administrator des jeweiligen Servers damit einverstanden ist. Manche DBAs könnten eine sehr entgegengesetzte Meinung dazu haben. Wenn du ohne ihr Einverständnis etwas in „ihrer“ master Datenbank ablegst, könnten sie mit Fackeln und Mistgabeln hinter dir her sein.

Wenn du keine Erlaubnis dafür hast oder die master Datenbank nicht verwenden willst, kannst du eine eigene Tools-Datenbank anlegen, die solche benutzerdefinierten Wartungsprozeduren, wie diese enthält.

Nun gut, zurück zur technischen Implementierung. Um eine Prozedur aus unserem Script zu machen fügen wird die Prozedur-Definition hinzu und bauen darin die Variablen, die wir zuvor über SQLCMD gesetzt haben, als reguläre Parameter für die Gespeicherte Prozedur ein. Dann fügen wir noch zwei Return-Statements an den Ausstiegspunkten der Prozedur hinzu. Das war’s schon.

Hier ist meine Stored Procedure:

Die Befehlszeile um das Backup zu starten können wir jetzt deutlich vereinfachen. Wir müssen nur die Backup-Prozedur aufrufen.

"C:\path\to\SQLCMD.exe" -E -S YourServer -b ^
-Q "EXEC sp_BackupSQLExpress 'YourDB', 'AUTO'"

Dir sollte auffallen, dass wir die Prozedur nur mit ihrem Namen aufrufen, ohne einen Datenbank- oder Schema-Bezeichner. Wenn wir eine Prozedur beginnend mit sp_... in der master Datenbank erstellen, hat das einen schönen Effekt. Wir können diese Prozedur von überall in der SQL-Server-Instanz aufrufen ohne die Datenbank oder das Schema anzugeben. Die Namensauflösung des Servers wird automatisch als erstes in der master Datenbank nach einer Prozedur suchen, die mit sp_... beginnt.

Wenn du deine Fassung dieser Prozedur in einer anderen Datenbank erstellt hast, dann solltest du sie mit ihrem vollen Namen, inklusive Datenbank- und Schema-Namen, ansprechen.

Kopiere das Backup an einen sicheren Ort

Sei dir darüber bewusst, dass hier in diesem Artikel ein wichtiger Schritt fehlt. Du musst eine Kopie deiner Backups anderswo (außer Haus) speichern. Andernfalls wird dein kompletter Backup-Prozess umsonst gewesen sein, wenn ein Katastrophales Ereignis (z.B. das Gebäude brennt ab) eintritt.

Du solltest also eine Copy-Anweisung ergänzen, die das Backup automatisch auf einen externen Netzwerk-Share oder einen FTP-Server kopiert. In meiner eigenen Implementierung verwende ich ein Powershell-Script, um die Backups auf Azure-Speicherplatz zu kopieren. Es gibt eine Vielzahl an möglichen Lösungen, und diese würden den Rahmen dieses Artikels sprengen.

Drittanwendungen

Nachdem du diesen Artikel bis hierher gelesen hast, weißt du, wie du automatische Backups nur mit den Tools die mit SQL Express (und Windows) bei jeder Installation verfügbar sind, erstellen kannst. Dies ist aus meiner Sicht die bevorzugte Lösung für das Problem.

Dennoch ist es vielleicht gut zu wissen, dass es auch verschiedene Tools von Drittanbietern gibt, die eine grafische Oberfläche und eingebaute Zeitplanungsfeatures enthalten um Backups mit SQL Express zu erstellen. Es gibt eine Frage auf DBA-Stack-Exchange, wo einige dieser alternativen Backup-Tools für SQL-Express in den Antworten angeführt werden.

Wiederherstellung - Kommt als nächstes…

Ursprünglich war es geplant, diese Mini-Serie an dieser Stelle mit ein paar generellen Hinweisen zur Wiederherstellung von Datenbank zu beenden. Allerdings hat es sich dann irgendwie nicht richtig angefühlt, dich einfach, nach ein paar leicht rechthaberischen Hinweisen, mit den Details der Wiederherstellung alleinzulassen.

Also habe ich mich entschieden die Serie noch auf einen weiteren Artikel über die Wiederherstellung von Datenbanken (einschließlich der rechthaberischen Hinweise) auszudehnen.

Share this article: