Erstellen einer Transact-SQL-Momentaufnahmesicherung
Gilt für:
SQL Server 2022 (16.x)
In diesem Artikel wird erläutert, warum und wie Transact-SQL-Snapshotsicherungen verwendet werden. Transact-SQL-Snapshotsicherungen sind neu in SQL Server 2022 (16.x).
Datenbanken werden täglich größer und größer. SQL Server-Sicherungen sind in der Regel Streamingsicherungen, und eine Streamingsicherung hängt von der Größe der Datenbank ab. Sicherungsvorgänge verbrauchen Ressourcen (CPU, Arbeitsspeicher, E/A, Netzwerk), die den Durchsatz der gleichzeitigen OLTP-Workload für die Dauer der Sicherung beeinflussen. Eine Möglichkeit, die Sicherungsleistung konstant zu halten und dafür zu sorgen, dass sie nicht von der Größe der Daten abhängt, ist die Durchführung einer Momentaufnahmesicherung unter Verwendung von Mechanismen, die von der zugrunde liegenden Speicherhardware oder dem Dienst bereitgestellt werden.
Da die Sicherung selbst auf Hardwareebene erfolgt, ist dies keine reine SQL Server-Lösung. SQL Server muss zunächst die Daten- und Protokolldateien für die Momentaufnahme vorbereiten, sodass die Dateien garantiert in einem Zustand vorliegen, der später wiederhergestellt werden kann. Sobald dies erfolgt ist, wird E/A auf SQL Server fixiert, und die Steuerung wird an die Sicherungsanwendung übergeben, um die Momentaufnahme abzuschließen. Sobald die Momentaufnahme erfolgreich erstellt wurde, muss die Anwendung die Kontrolle wieder an SQL Server zurückgeben, und die E/A-Vorgänge werden fortgesetzt. Da wir E/A für die Dauer des Momentaufnahmevorgangs fixieren müssen, ist es wichtig, dass die Momentaufnahme schnell erfolgt, damit die Arbeitsauslastung auf dem Server für einen längeren Zeitraum nicht unterbrochen wird. In der Vergangenheit haben Benutzer für die Erstellung von Momentaufnahmesicherungen auf Lösungen von Drittanbietern zurückgegriffen, die auf dem SQL Writer-Dienst aufbauen. Der SQL Writer-Dienst hängt von Windows VSS (Volume Shadow Service) und SQL Server VDI (Virtual Device Interface) ab, um die Orchestrierung zwischen SQL Server und der Momentaufnahme auf Datenträgerebene sicherzustellen. Sicherungsclients, die auf dem SQL Writer-Dienst basieren, sind in der Regel komplex und funktionieren nur unter Windows. Mit T-SQL-Momentaufnahmesicherungen kann die SQL Server-Seite der Orchestrierung über eine Reihe von T-SQL-Befehlen gehandhabt werden. Auf diese Weise können Benutzer eigene kleine Sicherungsanwendungen erstellen, die entweder unter Windows oder Linux oder sogar skriptbasierte Lösungen ausgeführt werden können, wenn der zugrunde liegende Speicher eine Skriptschnittstelle unterstützt, um eine Momentaufnahme zu initiieren.
Hier ist ein PowerShell-Beispielskript , das eine End-to-End-Lösung zum Sichern und Wiederherstellen einer Datenbank in einem virtuellen Azure SQL IaaS-Computer mit den in SQL Server 2022 (16.x) (und höher) eingeführten T-SQL-Snapshot-Sicherungsfunktionen veranschaulicht.
Workflow
Die Syntax für T-SQL-Momentaufnahmesicherungen entkoppelt den herstellerabhängigen Mechanismus für Momentaufnahmen von den Vorgängen zum Anhalten und Sichern. Diese Syntax ermöglicht Folgendes:
- Einfrieren einer Datenbank mit dem ALTER-Befehl – so erhalten Sie die Möglichkeit, eine Momentaufnahme des zugrunde liegenden Speichers zu erstellen. Anschließend können Sie die Datenbank reaktivieren und die Momentaufnahme mit dem Befehl BACKUP aufzeichnen.
- Gleichzeitiges Erstellen von Momentaufnahmen mehrerer Datenbanken mit den neuen Befehlen BACKUP GROUP und BACKUP SERVER. Mit dieser Option können Momentaufnahmen bei der Momentaufnahme-Granularität des zugrunde liegenden Speichers ausgeführt werden, sodass nicht mehrmals eine Momentaufnahme desselben Datenträgers ausgeführt werden muss.
- Führen Sie VOLLSTÄNDIGE Sicherungen und COPY_ONLY VOLLSTÄNDIGE Sicherungen aus. Diese Sicherungen werden ebenfalls aufgezeichnet
msdb. - Durchführen einer Zeitpunktwiederherstellung mithilfe von Protokollsicherungen, die mit dem normalen Streamingansatz nach der vollständigen Momentaufnahmesicherung (FULL) erstellt wurden. Bei Bedarf werden auch differenzielle Streamingsicherungen unterstützt.
Hinweis
Differenzielle Bitmaps werden während der ersten Phase beim Anhalten der Datenbank mit dem ALTER-Befehl gelöscht. Wenn der Benutzer beschließt, die Datenbank ohne das Durchführen einer Sicherung zu reaktivieren, weil beispielsweise die Momentaufnahme nicht erstellt werden konnte, ist die differenzielle Bitmap ungültig. Nachfolgende differenzielle Sicherungen sind folglich E/A-intensiver, da die gesamte Datenbank gescannt werden muss, um die differenzielle Sicherung durchzuführen. Nach einer erfolgreichen Momentaufnahmesicherung ist die differenzielle Bitmap wieder gültig.
Das folgende Diagramm veranschaulicht den allgemeinen Ablauf einer T-SQL-Momentaufnahmesicherung:
Der mittlere Momentaufnahmeschritt erfordert, dass Sie die Momentaufnahme im zugrunde liegenden Speicher initiieren. Das folgende Diagramm zeigt ein Beispiel dafür, wie ein Sicherungsskript mit SQL Server funktioniert, um den Momentaufnahmesicherungsprozess abzuschließen:
Ein Wiederherstellungsskript kann in ähnlicher Weise funktionieren:
Begrenzungen
Die maximale Anzahl von Datenbanken, die Sie mit diesem Feature sichern können, beträgt 64. Wenn mehr als 64 Datenbanken auf dem Server vorhanden sind, wird der folgende Fehler angezeigt:
Error message:
Msg 925, Level 19, State 1, Line 4
Maximum number of databases used for each query has been exceeded. The maximum allowed is 64.
Beispiele
In den folgenden Abschnitten finden Sie verschiedene T-SQL-Befehle, mit denen Sie Momentaufnahmesicherungen auf dem Datenträger durchführen können. Wenn eine Momentaufnahmesicherung auf den Datenträger geschrieben wird, werden nur die mit der Momentaufnahmesicherung verbundenen Metadateien in die Datei geschrieben. Die Ausgabe enthält keinen der Datenbankinhalte, mit Ausnahme der Kopfzeile und des Dateiinhalts. Die shelldatei, die als Teil der Durchführung der Snapshotsicherung erstellt wurde, sollte mit dem tatsächlichen Snapshot-URI verwendet werden, um eine vollständige Sicherung zu erstellen. DIE WIEDERHERSTELLUNG einer Datenbank aus dieser Datei erfordert, dass der Benutzer die Datenbankdateien aus dem Snapshot-URI vor dem Ausgeben des RESTORE-Befehls in den Bereitstellungspunkt kopiert. Benutzer können alle herkömmlichen T-SQL-Befehle wie RESTORE HEADERONLY, RESTORE FILELISTONLY in dieser Snapshot-Sicherungsmetadatendatei zusammen mit RESTORE DATABASE ausführen. Die Syntax unterstützt das Schreiben von Metadaten für Momentaufnahmesicherungen auf einen Datenträger (DISK) oder über eine URL. Die Momentaufnahmesicherungsätze können genauso wie Streamingsicherungssätze in einer einzigen Datei angefügt werden.
Hinweis
Für die Sicherung über eine URL werden Blockblobs bevorzugt, obwohl SQL Server für Windows Seitenblobs unterstützt. Für SQL Server für Linux und Container werden nur Blockblobs unterstützt.
Anhalten einer einzelnen Benutzerdatenbank für ein Momentaufnahmesicherung und Aufzeichnen einer Datenbanksicherung
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Anhalten mehrerer Benutzerdatenbanken für die Momentaufnahmesicherung
Wenn mehrere Datenbanken auf demselben zugrunde liegenden Datenträger vorhanden sind, können Sie mehrere Datenbanken mit dem folgenden Befehl anhalten.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Anhalten aller Benutzerdatenbanken auf dem Server für die Snapshotsicherung
Wenn alle Benutzerdatenbanken auf dem Server angehalten werden müssen, verwenden Sie den folgenden Befehl.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Hinweis
Keiner dieser Befehle unterstützt das Anhalten von Systemdatenbanken: master, model, und msdb für die Momentaufnahmesicherung.
Anhalten mehrerer Benutzerdatenbanken mit einem einzelnen Befehl
Zeichnen Sie die Momentaufnahme aller Benutzerdatenbanken auf dem Server in einem einzigen Sicherungssatz auf:
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Hinweis
Standardmäßig wird beim Anhalten für Momentaufnahmesicherungsbefehle die differenzielle Bitmap gelöscht. Wenn Sie eine Kopie nur sichern möchten, verwenden Sie das schlüsselwort COPY_ONLY wie in den folgenden Beispielen gezeigt.
Ausführen von Nur-Kopier-Snapshotsicherungen
Da die differenzielle Bitmap vor dem Einfrieren gelöscht wird, stellt SUSPEND_FOR_SNAPSHOT_BACKUP eine Option (COPY_ONLY) bereit, um die differenzielle Bitmap vor dem Einfrieren nicht zu löschen.
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2), MODE = COPY_ONLY);
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Hinweis
Es ist nicht erforderlich, COPY_ONLY für den BEFEHL SICHERUNG zu verwenden, da sie bereits beim Anhalten der Datenbank für die Momentaufnahmesicherung angegeben ist.
Markieren des Sicherungssets
Sie können die MEDIANAME- und MEDIADESCRIPTION-Optionen im Sicherungsbefehl verwenden, um den URI zu markieren, der der Momentaufnahme zugeordnet ist. Diese Verwendung ermöglicht es der Sicherungsdatei, die zugrunde liegenden Momentaufnahmeninformationen zusammen mit den Datenbankmetadaten zu tragen. Sie können auch die OPTIONEN NAME und DESCRIPTION verwenden, um den URI mit der einzelnen Backupset-Momentaufnahme zu kennzeichnen.
SQL Server interpretiert die BEZEICHNUNGsinformationen nicht auf irgendeine Weise. Der Benutzer kann jedoch den URI anzeigen, der der Momentaufnahmesicherung mit dem BEFEHL RESTORE LABELONLY zugeordnet ist.
Anschließend können Sie die Snapshotdatenträger, die sich am URI befinden, an die VM anfügen, um die Momentaufnahme wiederherzustellen. Der snapshot-URI, der in mediaNAME und MEDIADESCRIPTION gespeichert ist, steht auch zur Späteren Anzeige in der msdb Datenbanktabelle msdb.dbo.backupmediasetzur Verfügung.
Ausgabe der Momentaufnahmesicherung mit RESTORE HEADERONLY
Die Ausgabe mit RESTORE HEADERONLY sieht wie folgt aus, wenn die Datenbank, die Gruppe und der Server nacheinander ausgeführt und in dieselbe Ausgabedatei geschrieben werden:
RESTORE HEADERONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
Ausgabe der Momentaufnahmesicherung mit RESTORE FILELISTONLY
Die Ausgabe mit RESTORE FILELISTONLY zeigt standardmäßig den ersten Sicherungssatz an:
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
FILTER RESTORE FILELISTONLY output to a backup set
Um einen bestimmten Sicherungssatz aus mehreren Sicherungssätzen mit RESTORE FILELISTONLY auszuwählen, verwenden Sie die FILE-Klausel, die bereits unter RESTORE FILELISTONLY unterstützt wird.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;
FILTER RESTORE FILELISTONLY output to a database
Führen Sie eine Filterung durch, um eine einzelne Datenbank aus mehreren Datenbanken innerhalb des ausgewählten Sicherungssatzes mit RESTORE FILELISTONLY auszuwählen, indem Sie die FILE-Klausel mit der neu eingeführten DBNAME-Klausel verwenden. Die DBNAME-Klausel kann nur für Momentaufnahmesicherungssätze verwendet werden.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';
Wiederherstellen einer Momentaufnahmedatenbank
Die Wiederherstellung einer Datenbank aus einer Momentaufnahmesicherung ähnelt dem Anfügen einer Datenbank. Führen Sie den Wiederherstellungsbefehl ohne die Option RECOVERY aus, wenn die Datenbank ohne Wiederherstellung angefügt werden muss. Standardmäßig wird über RESTORE die erste Datenbank im Momentaufnahmesicherungssatz ausgewählt. Im folgenden Beispiel wird „testdb1“ wiederhergestellt. Wenn „testdb1“ bereits auf dem Server vorhanden ist, schließen Sie die REPLACE-Klausel ein. Sie müssen die Datenbankdateien einbinden, bevor Sie RESTORE ausführen.
RESTORE DATABASE testdb1
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, REPLACE, --> no DBNAME clause - restore first database in backup set
MOVE 'testdb1' TO 'd:\temp\snap\testdb1.mdf',
MOVE 'testdb1_log' TO 'd:\temp\snap\testdb1_log.ldf';
Wiederherstellen einer Momentaufnahmedatenbank, die in der Mitte aufgeführt ist
Wenn sich die Datenbank, die wiederhergestellt werden muss, in der Mitte befindet, geben Sie die wiederherzustellende Datenbank mit der Klausel DBNAME an. Die folgende Syntax stellt die in der DBNAME-Klausel angegebene Datenbank wieder her.
RESTORE DATABASE testdb3
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> restores testdb3 database
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;
Wiederherstellen der Datenbank mit einem anderem Namen
Sie können die Datenbank mit einem anderem Namen wiederherstellen. Wenn sich die Datenbank, die wiederhergestellt werden muss, in der Mitte befindet, geben Sie die wiederherzustellende Datenbank mit der Klausel DBNAME an. Die folgende Syntax stellt die in der DBNAME-Klausel angegebene Datenbank wieder her und benennt sie in „testdb33“ um.
RESTORE DATABASE testdb33 --> renames the specified database testdb3 to testdb33.
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> original name specified here
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;
Verwenden von RESTORE BACKUPSETONLY zum Extrahieren von Datenbanken aus einem Sicherungssatz mit mehreren Datenbanken
Ein Momentaufnahmesicherungssatz mit mehreren Datenbanken aus einer Gruppe oder Servermomentaufnahme kann mit dem Befehl RESTORE BACKUPSETONLY aufgeteilt werden. Dies erzeugt einen Sicherungssatz pro Datenbank.
Wenn eine Servermomentaufnahme drei Datenbanken in einer Sicherungsdatei mit einem einzelnen Sicherungssatz enthält, generiert der folgende Befehl drei Sicherungssätze, eine für jede Datenbank. Es erstellt ein Verzeichnis mit <file_name_prefix>_<unique_time_stamp> den Ausgabedateien.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db1.bkm'
WITH METADATA_ONLY;
Verwenden von RESTORE BACKUPSETONLY zum Extrahieren einer bestimmten Datenbank in einem Sicherungssatz mit mehreren Datenbanken
RESTORE BACKUPSETONLY unterstützt DEN DBNAME-Parameter, wenn der Benutzer eine Datenbank aus den drei Datenbanken im Sicherungssatz ausgeben möchte. Außerdem wird der FILE-Parameter unterstützt, um mehrere Sicherungssätze in der Sicherungsdatei zu filtern.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';
Dynamische Verwaltungssichten (DMVs) zum Anzeigen des angehaltenen Status und der eingerichteten Sperren
sys.dm_server_suspend_status (db_id, db_name, suspend_session_id, suspend_time_ms, is_diffmap_cleared, is_writeio_frozen)
sys.dm_tran_locks (resource_type, resource_database_id, resource_lock_partition, request_mode, request_type, request_status, request_owner_type, request_session_id)
Auflisten von Sicherungssetdetails für T-SQL-Snapshotsicherungen
SELECT database_name,
type,
backup_size,
backup_start_date,
backup_finish_date,
is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;
Eigenschaften auf Server- und Datenbankebene zur Überprüfung, ob eine Datenbank für eine Momentaufnahmesicherung angehalten wurde
SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');
Beispielskript zur T-SQL-Problembehandlung
Das folgende T-SQL-Beispielskript kann verwendet werden, um angehaltene Datenbanken auf dem Server zu erkennen und bei Bedarf nicht einzugeben.
IF (SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') = 1)
BEGIN
--full server suspended, requires server level thaw
PRINT 'Full server is suspended, requires server level thaw'
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF
END
ELSE
BEGIN
IF (SERVERPROPERTY('SuspendedDatabaseCount') > 0)
BEGIN
DECLARE @curdb SYSNAME
DECLARE @sql NVARCHAR(500)
DECLARE mycursor CURSOR FAST_FORWARD
FOR
SELECT db_name
FROM sys.dm_server_suspend_status;
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @curdb
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'unfreezing DB ' + @curdb
SET @sql = 'ALTER DATABASE ' + @curdb + ' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF'
EXEC sp_executesql @SQL
FETCH NEXT
FROM mycursor
INTO @curdb
END
PRINT 'All DB unfrozen'
CLOSE mycursor;
DEALLOCATE mycursor;
END
ELSE
-- no suspended database, thus no user action needed.
PRINT 'No database/server is suspended for snapshot backup'
END
Siehe auch
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für