Erstellen einer vollständigen Datenbanksicherung - SQL Server (2023)

Gilt für:SQL Server

In diesem Thema wird beschrieben, wie Sie mithilfe von SQL Server Management Studio, Transact-SQL oder PowerShell eine vollständige Datenbanksicherung in SQL Server erstellen.

Weitere Informationen finden Sie unter SQL Server Sicherung und Wiederherstellung mit Azure Blob Storage und SQL Server Backup to URL.

Einschränkungen

• Die BACKUP-Anweisung ist in einer expliziten oder impliziten Transaktion nicht zulässig.
• Sicherungen, die mit aktuelleren Versionen von SQL Server erstellt werden, können in früheren Versionen von SQL Server nicht wiederhergestellt werden.

Eine Übersicht über Sicherungskonzepte und -aufgaben und ausführlichere Informationen finden Sie unter Übersicht über sicherungen (SQL Server), bevor Sie fortfahren.

Empfehlungen

• Wenn eine Datenbank größer wird, ist zum Abschließen von vollständigen Datenbanksicherungen mehr Zeit und mehr Speicherplatz erforderlich. Bei einer großen Datenbank bietet es sich an, eine vollständige Datenbanksicherung durch mehrere differenzielle Datenbanksicherungen zu ergänzen.
• Ein Schätzwert der Größe einer vollständigen Datenbanksicherung kann mithilfe der gespeicherten Systemprozedur sp_spaceused ermittelt werden.
• Standardmäßig wird bei jedem erfolgreichen Sicherungsvorgang dem SQL Server -Fehlerprotokoll und dem Systemereignisprotokoll ein Eintrag hinzugefügt. Wenn Sie regelmäßig sichern, kann die Anzahl von Erfolgsmeldungen schnell ansteigen. Dadurch entstehen umfangreiche Fehlerprotokolle, die das Suchen nach anderen Meldungen erschweren können. In solchen Fällen können Sie diese Sicherungsprotokolleinträge mithilfe des Ablaufverfolgungsflags 3226 unterdrücken, wenn keines der Skripts von diesen Einträgen abhängig ist. Weitere Informationen finden Sie unter Ablaufverfolgungsflags (Transact-SQL).

Sicherheit

Bei einer Datenbanksicherung ist TRUSTWORTHY auf „OFF“ festgelegt. Informationen zum Festlegen von TRUSTWORTHY auf ON finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).

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

Berechtigungen

Mitglieder der festen Serverrolle sysadmin und der festen Datenbankrollen db_owner und db_backupoperator verfügen standardmäßig über BACKUP DATABASE- und BACKUP LOG-Berechtigungen.

Besitz- und Berechtigungsprobleme im Zusammenhang mit der physischen Datei des Sicherungsmediums können den Sicherungsvorgang beeinträchtigen. Der Dienst SQL Server muss vom Gerät lesen und auf das Gerät schreiben. Das Konto, unter dem der SQL Server-Dienst ausgeführt wird, muss über Schreibberechtigungen für das Sicherungsgerät verfügen. Allerdings prüft die gespeicherte Prozedur sp_addumpdevice, die den Systemtabellen einen Eintrag für ein Sicherungsmedium hinzufügt, nicht die Dateizugriffsberechtigungen. Probleme in der physischen Datei des Sicherungsgeräts treten möglicherweise erst auf, wenn die Sicherung verwendet oder eine Wiederherstellung versucht wurde.

Verwendung von SQL Server Management Studio

1. Erweitern Sie im Objekt-Explorer nach dem Herstellen einer Verbindung mit der entsprechenden Instanz von Microsoft SQL Server-Datenbank-Engine die Serverstruktur.

2. Erweitern Sie Datenbanken, und wählen Sie eine Benutzerdatenbank aus, oder erweitern Sie Systemdatenbanken , und wählen Sie eine Systemdatenbank aus.

3. Klicken Sie mit der rechten Maustaste auf die Datenbank, die Sie sichern möchten, zeigen Sie auf Aufgaben, und klicken Sie dann auf Sichern.

4. Im Dialogfeld Datenbank sichern wird in der Dropdownliste die von Ihnen ausgewählte Datenbank (die Sie in jede andere Datenbank auf dem Server ändern können) angezeigt.

5. Wählen Sie in der Dropdownliste Sicherungstyp einen Sicherungstyp aus. Der Standardwert lautet Vollständig.

   Wichtig

   Vor einer differenziellen Sicherung oder einer Transaktionsprotokollsicherung müssen Sie mindestens eine vollständige Datenbanksicherung ausführen.

   (Video) Erklärung der Backuparten Vollbackup, differenzielles Backup, inkrementelles Backup

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).

   Über die Dropdownliste Sichern auf können Sie ein anderes Gerät auswählen. Wählen Sie Hinzufügen aus, um Sicherungsobjekte und -ziele hinzuzufügen. Sie können ein dateiübergreifendes Stripeset für den Sicherungssatz erstellen, 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 vorhandenen Sicherungsziels anzuzeigen, wählen Sie es aus, und klicken Sie auf Inhalt.

8. (Optional) Überprüfen Sie die weiteren verfügbaren Einstellungen auf den Seiten Medienoptionen und Sicherungsoptionen.

   See Also

   Backup Overview (SQL Server) - SQL Server

   Weitere Informationen zu den verschiedenen Sicherungsoptionen finden Sie auf der Seite „Allgemein“, der Seite „Medienoptionen“ und der Seite „Sicherungsoptionen“.

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

10. Wenn die Sicherung erfolgreich abgeschlossen wurde, klicken Sie auf OK, um das Dialogfeld „SQL Server Management Studio“ zu schließen.

Zusätzliche Informationen

• Nach dem Erstellen einer vollständigen Datenbanksicherung können Sie eine differenzielle Datenbanksicherung oder eine Transaktionsprotokollsicherung ausführen.

• (Optional) Sie können das Kontrollkästchen Kopiesicherung aktivieren, um eine Kopiesicherung zu erstellen. Eine Kopiesicherung ist eine SQL Server-Sicherung, die unabhängig von der Sequenz von herkömmlichen SQL Server-Sicherungen erstellt wird. Weitere Informationen finden Sie unter Kopiesicherungen (SQL Server). Für den Sicherungstyp Differenziell ist keine Kopiesicherung verfügbar.

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

Beispiele

Erstellen Sie für die folgenden Beispiele mit dem folgenden Transact-SQL-Code eine Testdatenbank:

USE [master]GOCREATE DATABASE [SQLTestDB]GOUSE [SQLTestDB]GOCREATE TABLE SQLTest ( ID INT NOT NULL PRIMARY KEY, c1 VARCHAR(100) NOT NULL, dt1 DATETIME NOT NULL DEFAULT getdate() );GOUSE [SQLTestDB]GOINSERT 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')GOSELECT * FROM SQLTestGO

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

In diesem Beispiel wird die SQLTestDB-Datenbank auf dem Datenträger am standardmäßigen Sicherungsspeicherort gesichert.

1. Erweitern Sie im Objekt-Explorer nach dem Herstellen einer Verbindung mit der entsprechenden Instanz von Microsoft SQL Server-Datenbank-Engine die Serverstruktur.

2. Erweitern Sie Datenbanken, klicken Sie mit der rechten Maustaste auf SQLTestDB, zeigen Sie auf Aufgaben, und klicken Sie anschließend auf Sichern.

3. Klicken Sie auf OK.

4. Wenn die Sicherung erfolgreich abgeschlossen wurde, klicken Sie auf OK, um das Dialogfeld „SQL Server Management Studio“ zu schließen.

B. Vollständige Sicherung auf Datenträger an einem anderen als dem Standardspeicherort

In diesem Beispiel wird die SQLTestDB-Datenbank auf dem Datenträger am Speicherort Ihrer Wahl gesichert.

1. Erweitern Sie im Objekt-Explorer nach dem Herstellen einer Verbindung mit der entsprechenden Instanz von Microsoft SQL Server-Datenbank-Engine die Serverstruktur.

2. Erweitern Sie Datenbanken, klicken Sie mit der rechten Maustaste auf SQLTestDB, zeigen Sie auf Aufgaben, und klicken Sie anschließend auf Sichern.

   (Video) SQL Server 2019 - Grundlagen und Installation

3. Wählen Sie auf der Seite Allgemein im Abschnitt Ziel in der Dropdownliste Sichern auf:Datenträger aus.

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

5. Wählen Sie Hinzufügen aus. Das Dialogfeld Sicherungsziel auswählen wird geöffnet.

6. Geben Sie einen gültigen Pfad und Dateinamen in das Textfeld Dateiname ein, und verwenden Sie als Erweiterung .bak, um die Klassifizierung dieser Datei zu vereinfachen.

7. Wählen Sie OK und anschließend erneut OK aus, um die Sicherung zu starten.

8. Wenn die Sicherung erfolgreich abgeschlossen wurde, klicken Sie auf OK, um das Dialogfeld „SQL Server Management Studio“ zu schließen.

C. Erstellen einer verschlüsselten Sicherung

In diesem Beispiel wird die SQLTestDB-Datenbank mit Verschlüsselung am standardmäßigen Sicherungsspeicherort gesichert.

1. Erweitern Sie im Objekt-Explorer nach dem Herstellen einer Verbindung mit der entsprechenden Instanz von Microsoft SQL Server-Datenbank-Engine die Serverstruktur.

2. Erweitern Sie Datenbanken und dann Systemdatenbanken, klicken Sie mit der rechten Maustaste auf master, und klicken Sie dann auf Neue Abfrage, um ein Abfragefenster mit einer Verbindung mit Ihrer SQLTestDB-Datenbank zu öffnen.

3. Führen Sie die folgenden Befehle aus, um einen Datenbankmasterschlüssel und ein Zertifikat in Ihrer master-Datenbank zu erstellen.

   -- Create the master keyCREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'; -- 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 = '23987hxJ#KL95234nl0zBe'-- Create the certificate encrypted by the master keyCREATE CERTIFICATE MyCertificateWITH 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 Auf neuen Mediensatz sichern und alle vorhandenen Sicherungssätze löschen aus.

6. Aktivieren Sie auf der Seite Sicherungsoptionen im Abschnitt Verschlüsselung das Kontrollkästchen Sicherung verschlüsseln .

7. Wählen Sie in der Dropdownliste „Algorithmus“ den Eintrag AES 256 aus.

8. Wählen Sie in der Dropdownliste Zertifikat oder asymmetrischer SchlüsselMyCertificateaus.

9. Klicken Sie auf OK.

D. Sichern auf Azure Blob Storage

Im Beispiel wird eine vollständige Datenbanksicherung von SQLTestDB in Azure Blob Storage erstellt. Dabei wird im Beispiel davon ausgegangen, dass Sie bereits über ein Speicherkonto mit einem Blobcontainer verfügen. In diesem Beispiel wird eine Shared Access Signature für Sie erstellt. Dies ist nicht möglich, wenn der Container bereits über eine vorhandene Shared Access Signature verfügt.

Wenn Sie nicht über einen Azure Blob Storage Container in einem Speicherkonto verfügen, erstellen Sie eins, bevor Sie fortfahren. Informationen finden Sie unter Erstellen eines universellen Speicherkontos und Erstellen eines Containers.

1. Erweitern Sie im Objekt-Explorer nach dem Herstellen einer Verbindung mit der entsprechenden Instanz von Microsoft SQL Server-Datenbank-Engine die Serverstruktur.

   (Video) SQL Server Backup Vollständige Sicherung (auch Full Backup genannt) einfach erklärt mit SSMS

2. Erweitern Sie Datenbanken, klicken Sie mit der rechten Maustaste auf SQLTestDB, zeigen Sie auf Aufgaben, und klicken Sie anschließend auf Sichern.

3. Wählen Sie Option URL aus der Dropdownliste Back up to: (Sichern auf:) auf der Seite Allgemein im Abschnitt Ziel .

4. Wählen Sie Hinzufügen aus. Das Dialogfeld Sicherungsziel auswählen wird geöffnet.

5. Wenn Sie den Azure-Speichercontainer, den Sie mit SQL Server Management Studio verwenden möchten, zuvor registriert haben, wählen Sie diesen nun aus. Andernfalls klicken Sie auf Neuer Container, um einen neuen Container zu registrieren.

6. Melden Sie sich über das Dialogfeld Verbindung mit einem Microsoft-Abonnement herstellen bei Ihrem Konto an.

7. Wählen Sie im Dropdown-Textfeld Speicherkonto auswählen Ihr Speicherkonto aus.

8. Wählen Sie im Dropdown-Textfeld Blobcontainer auswählen Ihren Blobcontainer aus.

9. Wählen Sie im Dropdown-Kalenderfeld Ablauf der Richtlinie für den gemeinsamen Zugriff ein Ablaufdatum für die SAS-Richtlinie aus, die Sie in diesem Beispiel erstellen.

10. Klicken Sie auf Anmeldeinformationen erstellen, um eine SAS (Shared Access Signature) und Anmeldeinformationen in SQL Server Management Studio zu generieren.

11. Klicken Sie auf OK, um das Dialogfeld Verbindung mit einem Microsoft-Abonnement herstellen zu schließen.

12. Ändern Sie im Textfeld Sicherungsdatei den Namen der Sicherungsdatei (optional).

13. Klicken Sie auf OK, um das Dialogfeld Sicherungsziel auswählen zu schließen.

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

15. Wenn die Sicherung erfolgreich abgeschlossen wurde, klicken Sie auf OK, um das Dialogfeld „SQL Server Management Studio“ zu schließen.

Verwenden von Transact-SQL

Erstellen Sie eine vollständige Datenbanksicherung, indem Sie die BACKUP DATABASE-Anweisung ausführen, und geben Sie dabei Folgendes an:

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

Die grundlegende Transact-SQL-Syntax für eine vollständige Datenbanksicherung lautet:

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

Geben Sie optional eine oder mehrere WITH-Optionen an. Einige der grundlegenden WITH-Optionen werden hier beschrieben. Informationen zu allen WITH-Optionen finden Sie unter BACKUP (Transact-SQL).

Grundlegender Sicherungssatz von WITH-Optionen:

• { COMPRESSION | NO_COMPRESSION } : Nur in SQL Server 2008 Enterprise und höher verfügbar. Gibt an, ob für diese Sicherung eine Sicherungskomprimierung verwendet wird, wodurch die Standardeinstellung auf Serverebene überschrieben wird.
• ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY) : Nur in SQL Server 2014 und höheren Versionen geben Sie den zu verwendenden Verschlüsselungsalgorithmus und das Zertifikat oder den asymmetrischen Schlüssel an, die die Sicherheit bei der Verschlüsselung erhöhen.
• 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 NAME nicht angegeben wird, ist er leer.

Standardmäßig fügt BACKUP die Sicherung einem vorhandenen Mediensatz an, wobei vorhandene Sicherungssätze beibehalten werden. Verwenden Sie die NOINIT-Option für eine explizite Angabe. Informationen zum Anfügen an vorhandene Sicherungssätze finden Sie unter Mediensätze, Medienfamilien und Sicherungssätze (SQL Server).

Verwenden Sie die Option FORMAT, um die Sicherungsmedien zu formatieren:

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

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

Beispiele

Erstellen Sie für die folgenden Beispiele mit dem folgenden Transact-SQL-Code eine Testdatenbank:

USE [master]GOCREATE DATABASE [SQLTestDB]GOUSE [SQLTestDB]GOCREATE TABLE SQLTest ( ID INT NOT NULL PRIMARY KEY, c1 VARCHAR(100) NOT NULL, dt1 DATETIME NOT NULL DEFAULT GETDATE())GOUSE [SQLTestDB]GOINSERT 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')GOSELECT * FROM SQLTestGO

A. Sichern auf ein Datenträgermedium

In diesem Beispiel wird die gesamte SQLTestDB -Datenbank auf dem Datenträger gesichert, wobei mithilfe von FORMAT ein neuer Mediensatz erstellt wird.

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

B. Sichern auf ein Bandmedium

Im folgenden Beispiel wird die gesamte SQLTestDB -Datenbank auf Band gesichert, wobei die Sicherung an vorherige Sicherungen angefügt wird.

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

C. Sichern auf ein logisches Bandmedium

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

-- Create a logical backup device,-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.USE master;GOEXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;GOBACKUP 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 ausdrücklich eine vollständige Datenbanksicherung festzulegen, geben Sie den Parameter -BackupAction mit dem Standardwert Database an. Dieser Parameter ist bei vollständigen Datenbanksicherungen optional.

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 wird im Beispiel -BackupAction Databaseangegeben.

Die vollständigen Syntaxbeispiele finden Sie unter Backup-SqlDatabase.

$credential = Get-CredentialBackup-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> für die Instanz für die <myServer> Azure Blob Storage erstellt. Eine gespeicherte Zugriffsrichtlinie wurde mit Lese-, Schreib-, und Auflistungsrechten erstellt. Die SQL Server Anmeldeinformation https://<myStorageAccount>.blob.core.windows.net/<myContainer>wurde mit einer Shared Access Signature (SAS) erstellt, die der gespeicherten Zugriffsrichtlinie zugeordnet ist. Der PowerShell-Befehl verwendet den BackupFile -Parameter, um den Speicherort (URL) sowie den Namen der Sicherungsdaten anzugeben.

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

• Erstellen einer vollständigen Datenbanksicherung (SQL Server)
• Erstellen einer differenziellen Datenbanksicherung (SQL Server)
• Restore a Database Backup Using SSMS
• Wiederherstellen einer Datenbanksicherung unter dem einfachen Wiederherstellungsmodell (Transact-SQL)
• Wiederherstellen einer Datenbank bis zum Fehlerzeitpunkt im vollständigen Wiederherstellungsmodell (Transact-SQL)
• Wiederherstellen einer Datenbank an einem neuen Speicherort (SQL Server)
• Verwenden des Wartungsplanungs-Assistenten

Weitere Informationen

• Problembehandlung bei der Sicherung von SQL Server und Wiederherstellungsvorgänge
• Backup Overview (SQL Server)
• Transaktionsprotokollsicherungen (SQL Server)
• Mediensätze, Medienfamilien und Sicherungssätze (SQL Server)
• sp_addumpdevice (Transact-SQL)
• BACKUP (Transact-SQL)
• Datenbank sichern (Seite Allgemein)
• Datenbank sichern (Seite 'Sicherungsoptionen')
• Differenzielle Sicherungen (SQL Server)
• Vollständige Datenbanksicherungen (SQL Server)