Planen und Automatisieren von Sicherungen von SQL Server Express-Datenbanken

Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 2019698

Zusammenfassung

SQL Server Express enthält keine SQL Server-Agent, sodass Sie keine Sicherungsaufträge oder Wartungspläne wie in anderen SQL Server Editionen planen können. Um Sicherungen auf SQL Server Express zu automatisieren, kombinieren Sie drei Komponenten: die sp_BackupDatabases gespeicherte Prozedur, das sqlcmd Befehlszeilenprogramm und Windows Taskplaner. In diesem Artikel werden die Schritte erläutert, die geplante vollständige, differenzielle und Transaktionsprotokollsicherungen von SQL Server Express-Datenbanken einrichten. Außerdem wird erläutert, wie Sie bestätigen, dass eine geplante Sicherung ausgeführt wurde.

Dieser Artikel gilt nur für SQL Server Express-Editionen. Sie gilt nicht für SQL Server Express LocalDB.

Warum SQL Server Express keine Sicherungen mit SQL Server-Agent planen kann

SQL Server-Agent ist die Komponente, die geplante Aufträge und Wartungspläne ausführt und nicht in SQL Server Express enthalten ist. Einen vollständigen Vergleich der einzelnen Editionen finden Sie unter Editionen und unterstützte Features von SQL Server.

Ohne SQL Server-Agent können Sie weiterhin eine SQL Server Express-Datenbank bei Bedarf sichern, indem Sie eines der folgenden Tools verwenden:

sqlcmd-Hilfsprogramm
SQL Server Management Studio (SSMS)
MSSQL-Erweiterung für Visual Studio Code
Ein Transact-SQL-Skript, das die BACKUP-Befehlsfamilie (Transact-SQL) verwendet

Wenn Sie diese Sicherungen auf einem Zeitplan anstelle von Bedarf ausführen möchten, verwenden Sie Windows Taskplaner als Planungsmodul, wie in den folgenden Schritten beschrieben. Hintergrundinformationen zu Sicherungstypen und -strategie finden Sie unter Sichern und Wiederherstellen von SQL Server Datenbanken.

Voraussetzungen

Bevor Sie beginnen, stellen Sie sicher, dass Sie folgendes haben:

Eine ausgeführte Instanz von SQL Server Express. In den Beispielen in diesem Artikel wird die standardmäßige benannte Instanz verwendet. .\SQLEXPRESS
Die Mitgliedschaft in der festen Serverrolle "sysadmin " in dieser Instanz, die zum Erstellen einer gespeicherten Prozedur in der master Datenbank erforderlich ist.
Ein lokaler Ordner oder ein Laufwerk mit genügend freiem Speicherplatz, um die Sicherungsdateien zu speichern. Die Beispiele verwenden D:\SQLBackups.
Berechtigung zum Erstellen geplanter Aufgaben auf dem Computer, auf dem SQL Server Express ausgeführt wird.

Schritt 1: Erstellen der sp_BackupDatabases gespeicherten Prozedur

sp_BackupDatabasesist eine Microsoft bereitgestellte gespeicherte Prozedur, die den entsprechenden BACKUP Befehl für eine Datenbank oder jede Onlinedatenbank auf der Instanz ausführt. Sie erstellen es einmal in der master Datenbank.

Laden Sie das Skript aus SQL_Express_Backups.sql herunter, und speichern Sie es lokal, z. B. " C:\Temp\SQL_Express_Backups.sql". Erstellen Sie dann die gespeicherte Prozedur mithilfe von SSMS oder sqlcmd.

Erstellen der gespeicherten Prozedur mithilfe von SSMS

Öffnen Sie SSMS, und stellen Sie eine Verbindung mit Ihrer SQL Server Express-Instanz her. Geben Sie unter .\SQLEXPRESS ein, und wählen Sie dann Verbinden aus.
Wählen Sie Datei>>, wählen Sie die Datei SQL_Express_Backups.sql aus, die Sie gespeichert haben, und wählen Sie dann Öffnen.
Vergewissern Sie sich, dass die Datenbankliste auf der Symbolleiste angezeigt wird master. Das Skript beginnt mit USE [master], sodass es automatisch auf die richtige Datenbank ausgerichtet ist.
Wählen Sie "Ausführen" aus (oder drücken Sie F5). Der Bereich „Nachrichten“ meldet Commands completed successfully.

Erstellen der gespeicherten Prozedur mithilfe von sqlcmd

Führen Sie den folgenden Befehl in einer Eingabeaufforderung aus:

sqlcmd -S .\SQLEXPRESS -E -i "C:\Temp\SQL_Express_Backups.sql"

Bestätigen, dass die gespeicherte Prozedur erstellt wurde

Führen Sie die folgende Abfrage gegen die Instanz aus. Es gibt eine Zeile zurück, wenn die gespeicherte Prozedur vorhanden ist:

SELECT name, create_date
FROM master.sys.procedures
WHERE name = 'sp_BackupDatabases';

sp_BackupDatabases Parameter

Die gespeicherte Prozedur akzeptiert die folgenden Parameter:

Parameter	Required	Description
`@backupLocation`	Yes	Der Ordner, der die Sicherungsdateien empfängt, z. B `D:\SQLBackups\`. . Der Ordner muss bereits vorhanden sein.
`@backupType`	Yes	Der Sicherungstyp: `F` für Vollsicherung, `D` für Differenzialsicherung oder `L` für Transaktionsprotokollsicherung.
`@databaseName`	No	Die zu sichernde Datenbank. Wenn Sie diesen Parameter weglassen, sichert die gespeicherte Prozedur jede Onlinedatenbank in der Instanz mit Ausnahme der Datenbanken, die das Skript ausschließt.

Die gespeicherte Prozedur sichert tempdb nie. Außerdem werden die Beispieldatenbanken Northwind, pubs und AdventureWorks bei allen Sicherungstypen übersprungen, und master wird bei Differenz- und Protokollsicherungen übersprungen, da diese Sicherungstypen für die master-Datenbank nicht unterstützt werden.

Important

Die ausgeschlossenen Datenbanknamen werden im Skript hartcodiert. Wenn Sie über eine Datenbank verfügen, die einen dieser Namen verwendet, überspringt die gespeicherte Prozedur sie, ohne einen Fehler zu melden. Um eine solche Datenbank zu sichern, bearbeiten Sie die DELETE @DBs WHERE DBNAME IN (...) Listen im Skript, bevor Sie die gespeicherte Prozedur erstellen.

Schritt 2: Installieren des sqlcmd-Hilfsprogramms

Mit dem sqlcmd Hilfsprogramm können Sie Transact-SQL Anweisungen, gespeicherte Systemprozeduren und Skriptdateien über die Befehlszeile ausführen. Es wird mit SSMS installiert und ist auch als eigenständiger Download für Computer verfügbar, die nicht über SSMS verfügen. Informationen zum Installieren finden Sie unter "Herunterladen und Installieren des sqlcmd"-Hilfsprogramms.

Um zu bestätigen, dass sqlcmd -? installiert ist und zur Verfügung steht, führen Sie sqlcmd in einer Eingabeaufforderung aus.

Der Ordner, der die ausführbare Datei sqlcmd enthält, wird bei der Installation von SQL Server oder den eigenständigen Tools in der Regel der Umgebungsvariablen Path hinzugefügt. Wenn sqlcmd -? berichte, dass der Befehl nicht erkannt wird, fügen Sie den Ordner entweder der Path Variablen hinzu, oder geben Sie den vollständigen Pfad zum Hilfsprogramm in Der Batchdatei an.

Schritt 3: Erstellen der Sqlbackup.bat Batchdatei

Erstellen Sie in einem Text-Editor eine Batchdatei mit dem Namen Sqlbackup.bat. Kopieren Sie den Text aus einem der folgenden Beispiele je nach Szenario in diese Datei.

Betrachten Sie die folgenden Punkte, bevor Sie ein Beispiel auswählen:

Jedes Beispiel verwendet D:\SQLBackups als Platzhalter. Ändern Sie den Pfad zum Laufwerk- und Sicherungsordner, den Sie in Ihrer Umgebung verwenden möchten, und stellen Sie sicher, dass der Ordner vorhanden ist.
Wenn Sie SQL Server Authentifizierung verwenden, wird das Kennwort in klartext in der Batchdatei gespeichert. Beschränken Sie den Zugriff auf den Ordner, der die Batchdatei enthält, nur auf autorisierte Benutzer.

Beispiel 1: Vollständige Sicherungen aller Datenbanken mithilfe Windows Authentifizierung

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

Beispiel 2: Differenzielle Sicherungen aller Datenbanken mithilfe SQL Server Authentifizierung

REM Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='D'"

Notiz

Zum Ausführen von Sicherungen muss das Login in jeder Datenbank, die Sie sichern, Mitglied der festen Datenbankrolle db_backupoperator oder db_owner oder Mitglied der festen Serverrolle sysadmin sein. Weitere Informationen finden Sie unter Rollen auf Datenbankebene.

Beispiel 3: Transaktionsprotokollsicherungen aller Datenbanken mithilfe Windows Authentifizierung

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='L'"

Transaktionsprotokollsicherungen erfordern das vollständige oder massenprotokollierungsbasierte Wiederherstellungsmodell, und sie erfordern mindestens eine vorherige vollständige Sicherung. Weitere Informationen finden Sie unter Wiederherstellungsmodelle (SQL Server).

Beispiel 4: Vollständige Sicherung einer einzelnen Datenbank mithilfe Windows Authentifizierung

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'"

Um eine differenzielle Sicherung von USERDBvorzunehmen, ändern Sie den @backupType Parameter in D. Um eine Transaktionsprotokollsicherung vorzunehmen, ändern Sie sie in L.

Die Option -b bewirkt, dass sqlcmd beendet wird und einen DOS ERRORLEVEL-Wert von 1 zurückgibt, wenn ein SQL Server-Fehler einen Schweregrad von mehr als 10 aufweist. Ohne -b gibt sqlcmd auch dann 0 zurück, wenn die Sicherung fehlschlägt, und der Taskplaner meldet die Aufgabe als erfolgreich.

Bevor Sie fortfahren, führen Sie Sqlbackup.bat manuell über eine Eingabeaufforderung aus, und bestätigen Sie, dass Sicherungsdateien im Sicherungsordner angezeigt werden. Das Beheben von Fehlern ist jetzt einfacher, als sie später über den Taskplaner zu diagnostizieren.

Schritt 4: Planen der Batchdatei in Windows Taskplaner

Führen Sie die folgenden Schritte aus, um Sqlbackup.bat nach einem Zeitplan auszuführen:

Wählen Sie auf dem Computer, auf dem SQL Server Express ausgeführt wird, Start aus und geben Sie Taskplaner in das Textfeld ein.
Wählen Sie unter "Beste Übereinstimmung" die Option "Taskplaner " aus, um sie zu starten.
Klicken Sie in "Taskplaner" mit der rechten Maustaste auf " Taskplaner (Lokal) ", und wählen Sie " Standardaufgabe erstellen" aus.
Geben Sie einen Namen für die neue Aufgabe ein (z. B. SQLBackup), und wählen Sie dann "Weiter" aus.
Wählen Sie "Täglich " für den Tasktrigger und dann "Weiter" aus.
Legen Sie die Wiederholung auf 1 Tag fest, und wählen Sie dann Weiter aus.
Wählen Sie "Programm starten " als Aktion und dann "Weiter" aus.
Wählen Sie "Durchsuchen" aus, wählen Sie die Sqlbackup.bat Datei aus, die Sie in Schritt 3 erstellt haben: Erstellen Sie die Sqlbackup.bat Batchdatei, und wählen Sie dann "Öffnen" aus.
Aktivieren Sie das Kontrollkästchen „Eigenschaften“-Dialogfeld für diese Aufgabe öffnen, wenn ich auf „Fertig stellen“ klicke, und wählen Sie dann „Fertig stellen“ aus.
Überprüfen Sie auf der Registerkarte „Allgemein“ die Sicherheitsoptionen, und bestätigen Sie Folgendes für das Konto, das unter „Beim Ausführen der Aufgabe folgendes Benutzerkonto verwenden“ aufgeführt ist:
- Das Konto verfügt mindestens über Lese- und Ausführungsberechtigungen, um das sqlcmd Hilfsprogramm auszuführen.
- Wenn die Batchdatei Windows Authentifizierung verwendet, verfügt das Konto über die Berechtigung zum Sichern der SQL Server Datenbanken.
- Wenn die Batchdatei SQL Server Authentifizierung verwendet, verfügt die SQL Server Anmeldung in der Batchdatei über die Berechtigung zum Sichern der Datenbanken.
Passen Sie die verbleibenden Einstellungen an Ihre Anforderungen an, und wählen Sie dann "OK" aus.

Tipp

Führen Sie testweise Sqlbackup.bat in einer Eingabeaufforderung aus, die Sie mit demselben Benutzerkonto gestartet haben, dem die Aufgabe gehört. Dieser Schritt bestätigt, dass das Konto über die erforderlichen Berechtigungen verfügt, bevor der Zeitplan ausgeführt wird.

Weitere Informationen zu den Terminplanungsoptionen finden Sie unter Task Scheduler.

Überprüfen, ob die geplante Sicherung ausgeführt wurde

Vergewissern Sie sich nach der ersten geplanten Ausführung, dass die Sicherung erfolgreich war:

Überprüfen Sie den Sicherungsordner auf neue .bak Dateien (vollständige und differenzielle Sicherungen) oder TRN-Dateien (Transaktionsprotokollsicherungen). Die gespeicherte Prozedur fügt jedem Dateinamen einen Datums- und Zeitstempel hinzu.
Wählen Sie in "Taskplaner" die Option "Taskplanerbibliothek" aus, wählen Sie Ihre Aufgabe aus, und überprüfen Sie die Spalte "Ergebnis der letzten Ausführung ". In dieser Spalte wird der Ausgangscode angezeigt, den die batchdatei zurückgegeben hat. Da die Beispiele die -b Option verwenden, bedeutet der Wert 0x1 , dass der Sicherungsbefehl fehlgeschlagen ist. Weitere Werte finden Sie unter "Vorgangsplanerfehler" und "Erfolgskonstanten".

Fragen Sie den Backup-Verlauf auf der Instanz ab:

SELECT database_name, type, backup_start_date, backup_finish_date, physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;

Diese Tabellen sind Teil des Sicherungsverlaufs, den SQL Server in der Datenbank msdb verwaltet. Weitere Informationen finden Sie unter Sicherungsverlaufs- und Headerinformationen (SQL Server).