Erstellen einer vollständigen Datenbanksicherung

Gilt für:SQL Server

Dieser Artikel beschreibt, wie man ein vollständiges Datenbank-Backup in SQL Server mit SQL Server Management Studio (SSMS), Transact-SQL oder PowerShell erstellt.

Weitere Informationen finden Sie unter SQL Server-Sicherung und -Wiederherstellung mit Azure Blob Storage und SQL Server-Sicherung zu URL für Azure Blob Storage. Für einen Überblick über Backup-Konzepte und Aufgaben siehe Backup-Überblick (SQL Server).

Empfehlungen

• Mit wachsender Datenbank benötigen vollständige Datenbank-Backups länger und benötigen mehr Speicherplatz. Für große Datenbanken ergänzen Sie vollständige Backups mit einer Reihe differenzierter Datenbank-Backups.

• Schätzen Sie die Größe eines vollständigen Datenbank-Backups mit dem sp_spaceused systemgespeicherten Verfahren.

• Standardmäßig fügt jedes erfolgreiche Backup einen Eintrag zum SQL Server-Fehlerprotokoll und zum Systemereignisprotokoll hinzu. Häufige Backups können diese Protokolle füllen und andere Nachrichten schwer auffindbar machen.

  Um Backup-Protokolleinträge zu unterdrücken, verwende Trace-Flag 3226, vorausgesetzt, deine Skripte sind nicht darauf angewiesen.

Einschränkungen

Du kannst die BACKUP Aussage nicht in einer expliziten oder impliziten Transaktion verwenden.

Man kann Backups von neueren Versionen von SQL Server in früheren Versionen nicht wiederherstellen. Zum Beispiel kann man ein SQL Server 2025 (17.x)-Backup auf SQL Server 2022 (16.x) nicht wiederherstellen.

Sicherheit

TRUSTWORTHY ist bei einer Datenbanksicherung auf OFF festgelegt. Um ON auf TRUSTWORTHY festzulegen, siehe ALTER DATABASE SET optionen.

Ab SQL Server 2012 (11.x) sind die Optionen MEDIAPASSWORD und PASSWORD zum Erstellen von Sicherungen nicht verfügbar. Sie können jedoch immer noch mit Kennwörtern erstellte Sicherungen wiederherstellen.

Berechtigungen

Die Berechtigungen BACKUP DATABASE und BACKUP LOG sind standardmäßig Mitgliedern der festen Serverrolle sysadmin sowie den festen Datenbankrollen db_owner und db_backupoperator zugewiesen.

Das SQL Server-Dienstkonto muss Lese- und Schreibberechtigungen für die physische Datei des Backup-Geräts haben. Eigentums- oder Berechtigungsprobleme an dieser Datei verhindern Sicherungs- und Wiederherstellungsoperationen und treten nur auf, wenn die Sicherungs- oder Wiederherstellungsoperation ausgeführt wird.

Zum Beispiel validiert das sp_addumpdevice System Stored Procedure, das einen Backup-Geräteeintrag zu den Systemtabellen hinzufügt, den Dateizugriff nicht .

Verwenden von SQL Server Management Studio

1. Im Objekt-Explorer verbinden Sie sich mit der Datenbank-Engine und erweitern Sie dann den Serverbaum.

2. Erweitern Sie Datenbanken und wählen Sie dann eine Benutzerdatenbank aus. Oder erweitern Sie Systemdatenbanken und wählen Sie eine Systemdatenbank aus.

3. Rechtsklicke auf die Datenbank, die du sichern möchtest, zeige auf Aufgaben und wähle dann Sichern aus...

4. Im Dialogfeld Backup Database erscheint die ausgewählte Datenbank in der Dropdown-Liste. Du kannst die Datenbank auf jede andere Datenbank auf dem Server umstellen.

5. Wählen Sie in der Liste " Sicherungstyp " einen Sicherungstyp aus. Der Standardwert ist "Vollständig".

   Wichtig

   Sie müssen mindestens eine vollständige Datenbanksicherung durchführen, bevor Sie eine differenzielle oder Transaktionsprotokollsicherung durchführen können.

6. Wählen Sie unter Sicherungskomponente die Option Datenbank aus.

7. Überprüfen Sie im Abschnitt Ziel den Standardspeicherort für die Sicherungsdatei (im Ordner ../mssql/data).

   Benutze die Liste Backup to, um ein anderes Gerät auszuwählen. Wählen Sie Hinzufügen , um Backup-Objekte oder Ziele hinzuzufügen. Du kannst das Backup-Set auf mehrere Dateien aufteilen, um die Sicherungsgeschwindigkeit zu erhöhen.

   Um ein Sicherungsziel zu entfernen, wählen Sie es aus, und klicken Sie auf Entfernen. Um den Inhalt eines bestehenden Backup-Ziels anzuzeigen, wählen Sie es aus und wählen Sie Inhalt.

8. Optional können Sie die anderen Einstellungen auf den Seiten Medienoptionen und Backup-Optionen überprüfen.

   Weitere Informationen zu Backup-Optionen finden Sie unter Backup-Datenbank (allgemeine Seite),Back-up-Datenbank (Medienoptionen-Seite) und Back-up-Datenbank (Backup-Optionen-Seite).

9. Wählen Sie OK aus, um die Sicherung zu starten.

10. Wenn das Backup erfolgreich abgeschlossen ist, wähle OK , um den Dialog zu schließen.

Hinweise

• Wenn Sie in SQL Server Management Studio eine Backup-Aufgabe angeben, können Sie das entsprechende Transact-SQL BACKUP Skript generieren, indem Sie die Schaltfläche "Skript" auswählen und dann ein Skriptziel auswählen.

• Nachdem Sie ein vollständiges Datenbank-Backup erstellt haben, können Sie ein differenzielles Datenbank-Backup oder ein Transaktionsprotokoll-Backup erstellen.

• Optional wählen Sie das Kontrollkästchen Kopier-nur-Backup aus, um ein reines Kopier-Backup zu erstellen. Eine kopiegeschützte Sicherung ist eine SQL Server-Sicherung , die unabhängig von der Sequenz herkömmlicher SQL Server-Sicherungen ist. Weitere Informationen finden Sie unter Copy-Only-Sicherungen. Kopierbare Backups sind für den Differential-Backup-Typ nicht verfügbar.

• Die Option Medien überschreiben ist auf der Seite Medienoptionen deaktiviert, wenn Sie in eine URL sichern.

Beispiele

Für die folgenden Beispiele erstellen Sie eine Testdatenbank mit dem folgenden Transact-SQL Code:

USE master;
GO

CREATE DATABASE [SQLTestDB];
GO

USE [SQLTestDB];
GO

CREATE TABLE SQLTest
(
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR (100) NOT NULL,
    dt1 DATETIME DEFAULT getdate() NOT NULL
);
GO

USE [SQLTestDB];
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1');
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2');
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3');
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4');
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5');
GO

SELECT *
FROM SQLTest;
GO

A. Vollständige Sicherung auf Datenträger am Standardspeicherort

Dieses Beispiel sichert die SQLTestDB Datenbank auf der Festplatte am Standard-Backup-Standort.

1. Im Objekt-Explorer verbinden Sie sich mit der Datenbank-Engine und erweitern Sie dann den Serverbaum.

2. Erweitern Sie Datenbanken, klicken Sie mit der rechten Maustaste auf SQLTestDB, zeigen Sie auf Aufgaben und wählen Sie dann Sichern... aus.

3. Wählen Sie OK aus.

4. Wenn das Backup erfolgreich abgeschlossen ist, wähle OK , um den Dialog zu schließen.

B. Vollständige Sicherung auf Datenträger an einem nicht standardmäßigen Speicherort

Dieses Beispiel sichert die SQLTestDB Datenbank auf die Festplatte an einem von dir gewählten Ort.

1. Im Objekt-Explorer verbinden Sie sich mit der Datenbank-Engine und erweitern Sie dann den Serverbaum.

2. Erweitern Sie Datenbanken, klicken Sie mit der rechten Maustaste auf SQLTestDB, zeigen Sie auf Aufgaben und wählen Sie dann Sichern... aus.

3. Auf der Seite Allgemein wählen Sie im Abschnitt Ziel in der Liste Sichern auf die Option Festplatte aus.

4. Wählen Sie "Entfernen" aus, bis alle vorhandenen Sicherungsdateien entfernt werden.

5. Wählen Sie Hinzufügen aus. Das Dialogfeld "Backup Destination" auswählen öffnet sich.

6. Geben Sie einen gültigen Pfad und Dateinamen in das Feld "Dateiname " ein. Verwenden Sie .bak als Erweiterung, um die Dateiklassifizierung zu vereinfachen.

7. Wähle OK und dann nochmal OK , um das Backup zu starten.

8. Wenn das Backup erfolgreich abgeschlossen ist, wähle OK , um den Dialog zu schließen.

C. Erstellen einer verschlüsselten Sicherung

Dieses Beispiel sichert die SQLTestDB Datenbank verschlüsselt am Standard-Backup-Speicherort.

1. Im Objekt-Explorer verbinden Sie sich mit der Datenbank-Engine und erweitern Sie dann den Serverbaum.

2. Erweitern Sie Datenbanken, erweitern Sie Systemdatenbanken, klicken Sie mit der rechten Maustaste master, und wählen Sie dann "Neue Abfrage " aus, um ein Abfragefenster mit einer Verbindung zu Ihrer SQLTestDB Datenbank zu öffnen.

3. Führe die folgenden Befehle aus, um einen Datenbank-Hauptschlüssel und ein Zertifikat in der master Datenbank zu erstellen.

   -- Create the master key.
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
   
   -- If the master key already exists, open it in the same session that you create the certificate. (See next step.)
   OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>';
   
   -- Create the certificate encrypted by the master key.
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';
   

4. Klicken Sie im Objekt-Explorer im Knoten Datenbanken mit der rechten Maustaste auf SQLTestDB, zeigen Sie auf Aufgaben, und klicken Sie anschließend auf Sichern.

5. Wählen Sie auf der Seite "Medienoptionen " im Abschnitt " Medien überschreiben " die Option "Zurück zu einem neuen Mediensatz" aus, und löschen Sie alle vorhandenen Sicherungssätze.

6. Wählen Sie auf der Seite " Sicherungsoptionen " im Abschnitt "Verschlüsselung " die Option "Sicherung verschlüsseln" aus.

7. Wählen Sie in der Liste "Algorithmus " AES 256 aus.

8. Wählen Sie in der Liste " Zertifikat" oder "Asymmetrischer Schlüssel " die Option MyCertificateaus.

9. Wählen Sie OK aus.

D: Sichern von Azure Blob Storage

In diesem Beispiel wird eine vollständige Sicherung der Datenbank SQLTestDB in Azure Blob Storage erstellt. Das Beispiel geht davon aus, dass du bereits ein Speicherkonto mit einem Blob-Container hast. Das Beispiel erzeugt eine Shared-Access-Signatur und schlägt fehl, wenn der Container bereits eine Shared-Access-Signatur hat.

Wenn du keinen Blob Storage-Container in einem Speicherkonto hast, erstelle einen, bevor du weitermachst. Informationen finden Sie unter Erstellen eines universellen Speicherkontos und Erstellen eines Containers.

1. Im Objekt-Explorer verbinden Sie sich mit der Datenbank-Engine und erweitern Sie dann den Serverbaum.

2. Erweitern Sie Datenbanken, klicken Sie mit der rechten Maustaste auf SQLTestDB, zeigen Sie auf Aufgaben und wählen Sie dann Sichern... aus.

3. Wählen Sie auf der Seite Allgemein im Abschnitt ZielURL in der Liste sicher auf aus.

4. Wählen Sie Hinzufügen aus. Das Dialogfeld "Backup Destination" auswählen öffnet sich.

5. Wenn du den Azure-Speichercontainer, den du verwenden möchtest, zuvor mit SSMS registriert hast, wähle ihn aus. Andernfalls klicken Sie auf Neuer Container, um einen neuen Container zu registrieren.

6. Im Dialogfeld "Verbinden zu einem Microsoft-Abonnement melden Sie sich mit Ihrem Konto an.

7. Wählen Sie im Feld " Speicherkonto auswählen " Ihr Speicherkonto aus.

8. Wählen Sie im Feld "Blobcontainer auswählen" Ihren BLOB-Container aus.

9. Wählen Sie im Kalenderfeld Ablaufdatum der Shared Access Policy ein Ablaufdatum für die Shared-Access-Richtlinie aus, die in diesem Beispiel erstellt wird.

10. Wählen Sie "Zugangsdaten erstellen ", um eine Shared-Access-Signatur und Zugangsdaten in SSMS zu generieren.

11. Wählen Sie OK aus, um das Dialogfeld "Verbinden mit einem Microsoft-Abonnement" zu schließen.

12. Ändern Sie im Feld "Sicherungsdatei " den Namen der Sicherungsdatei, wenn Sie möchten.

13. Wählen Sie OK, um das Dialogfeld "Wählen Sie ein Backup-Ziel" zu schließen.

14. Wählen Sie OK aus, um die Sicherung zu starten.

15. Wenn das Backup erfolgreich abgeschlossen ist, wähle OK , um den Dialog zu schließen.

Verwenden von Transact-SQL

Erstellen Sie ein vollständiges Datenbank-Backup, indem Sie die Anweisung BACKUP DATABASE ausführen. Angeben:

• Den Namen der zu sichernden Datenbank.
• Das Sicherungsmedium, auf das die vollständige Datenbanksicherung geschrieben wird.

Die grundlegende Transact-SQL-Syntax zum Erstellen eines vollständigen Datenbankbackups lautet:

BACKUP DATABASE <database>
TO <backup_device> [ , ...n ]
[ WITH <with_options> [ , ...o ] ];

Geben Sie optional eine oder mehrere WITH Optionen an. Hier werden einige grundlegende WITH Optionen beschrieben. Informationen zu allen WITH Optionen finden Sie unter BACKUP.

Grundlegende Sicherungssatzoptionen WITH :

• { KOMPRIMIERUNG | NO_COMPRESSION }. Der SQL Server legt fest, ob die Backup-Kompression auf dem Backup durchgeführt wird und damit die Server-Standardeinstellung überschrieben wird.

• VERSCHLÜSSELUNG (ALGORITHMUS, SERVER CERTIFICATE | ASYMMETRIC KEY). In SQL Server 2014 oder später wird der zu verwendende Verschlüsselungsalgorithmus sowie das Zertifikat oder der asymmetrische Schlüssel zur Absicherung der Verschlüsselung festgelegt.

• BESCHREIBUNG = { 'text' | @text_variable }. Gibt den Freiformtext an, der den Sicherungssatz beschreibt. Die Zeichenfolge kann maximal 255 Zeichen haben.

• NAME = { backup_set_name | @backup_set_name_var }. Gibt den Namen des Sicherungssatzes an. Namen können maximal 128 Zeichen haben. Wenn du nicht spezifizierst NAME, ist es leer.

Standardmäßig fügt BACKUP die Sicherung einem vorhandenen Mediensatz an, wobei vorhandene Sicherungssätze beibehalten werden. Verwenden Sie die NOINIT Option, um diese Konfiguration explizit anzugeben. Informationen zum Anfügen an vorhandene Sicherungssätze finden Sie unter Mediensätze, Medienfamilien und Sicherungssätze (SQL Server).For information about appending to existing backup sets, see Media sets, media families, and backup sets (SQL Server).

Verwenden Sie zum Formatieren des Sicherungsmediums die FORMAT Option:

FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

Verwenden Sie die FORMAT Klausel, wenn Sie Medien zum ersten Mal verwenden oder alle bestehenden Daten überschreiben möchten. Weisen Sie den neuen Medien optional einen Mediennamen und eine Beschreibung zu.

Beispiele

Für die folgenden Beispiele erstellen Sie eine Testdatenbank mit dem folgenden Transact-SQL Code:

USE master;
GO

CREATE DATABASE [SQLTestDB];
GO

USE [SQLTestDB];
GO

CREATE TABLE SQLTest
(
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR (100) NOT NULL,
    dt1 DATETIME DEFAULT GETDATE() NOT NULL
);
GO

USE [SQLTestDB];
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1');
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2');
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3');
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4');
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5');
GO

SELECT *
FROM SQLTest;
GO

A. Sichern auf einem Datenträgermedium

Im folgenden Beispiel wird die vollständige SQLTestDB Datenbank auf dem Datenträger gesichert. Es verwendet FORMAT, um einen neuen Mediensatz zu erstellen.

USE SQLTestDB;
GO

BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
WITH FORMAT,
     MEDIANAME = 'SQLServerBackups',
     NAME = 'Full Backup of SQLTestDB';
GO

B. Sichern auf einem Bandmedium

Im folgenden Beispiel wird die vollständige SQLTestDB Datenbank auf Band gesichert. Sie fügt die Sicherung an die vorherigen Sicherungen an.

USE SQLTestDB;
GO

BACKUP DATABASE SQLTestDB
TO TAPE = '\\.\Tape0'
WITH NOINIT,
     NAME = 'Full Backup of SQLTestDB';
GO

C. Sichern auf einem logischen Bandmedium

Im folgenden Beispiel wird ein logisches Sicherungsmedium für ein Bandlaufwerk erstellt. Im Beispiel wird dann die vollständige SQLTestDB Datenbank auf diesem Gerät gesichert.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO

EXECUTE sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0';

USE SQLTestDB;
GO

BACKUP DATABASE SQLTestDB
TO SQLTestDB_Bak_Tape
WITH FORMAT,
     MEDIANAME = 'SQLTestDB_Bak_Tape',
     MEDIADESCRIPTION = '\\.\tape0',
     NAME = 'Full Backup of SQLTestDB';
GO

PowerShell

Verwenden Sie das Backup-SqlDatabase Cmdlet. Um explizit eine vollständige Datenbanksicherung anzugeben, geben Sie den -BackupAction Parameter mit dem Standardwert an. Database Dieser Parameter ist bei vollständigen Datenbanksicherungen optional.

Wenn Sie ein PowerShell-Fenster innerhalb von SSMS öffnen, um sich mit der SQL Server-Datenbank-Engine zu verbinden, können Sie den Teil mit den Zugangsdaten weglassen, da Ihre Zugangsdaten in SSMS automatisch die Verbindung zwischen PowerShell und Datenbank-Engine herstellen.

Beispiele

A. Vollständige Sicherung (lokal)

Im folgenden Beispiel wird eine vollständige Datenbanksicherung der <myDatabase> -Datenbank am standardmäßigen Sicherungsspeicherort der Serverinstanz Computer\Instanceerstellt. Optional gibt dieses Beispiel an -BackupAction Database.

Vollständige Syntaxbeispiele finden Sie unter Backup-SqlDatabase.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Vollständige Sicherung in Azure

Im folgenden Beispiel wird eine vollständige Sicherung der Datenbank <myDatabase> auf der <myServer> Instanz in Blob Storage erstellt. Eine gespeicherte Zugriffsrichtlinie wird mit Les-, Schreib- und Listenrechten erstellt. Die SQL Server-Credential, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, wird durch Verwendung einer mit der gespeicherten Zugriffsrichtlinie verknüpften Shared-Access-Signatur erstellt. Der Befehl verwendet den $backupFile Parameter, um den Speicherort (URL) und den Namen der Sicherungsdatei anzugeben.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

Zugehörige Aufgaben

• Erstellen einer differenziellen Datenbanksicherung (SQL Server)
• Wiederherstellen einer Datenbanksicherung mithilfe von SSMS
• Wiederherstellen Sie ein Datenbank-Backup nach dem einfachen Wiederherstellungsmodell
• Wiederherstellen der Datenbank zum Fehlerpunkt – vollständige Wiederherstellung
• Wiederherstellen einer Datenbank an einem neuen Speicherort (SQL Server)
• Verwenden des Wartungsplanungs-Assistenten

Zugehöriger Inhalt

• Behandeln von Problemen mit SQL Server-Sicherungs- und Wiederherstellungsvorgängen
• Übersicht über die Sicherung (SQL Server)
• Transaktionsprotokollsicherungen (SQL Server)
• Mediensätze, Medienfamilien und Sicherungssätze (SQL Server)
• sys.sp_addumpdevice (Transact-SQL)
• BACKUP (Transact-SQL)
• Datenbank sichern (Seite „Allgemein“)
• Datenbank sichern (Seite „Sicherungsoptionen“)
• Differenzielle Sicherungen (SQL Server)
• Vollständige Datenbanksicherungen (SQL Server)