Erstellen einer Transact-SQL-Momentaufnahmesicherung

Gilt für: SQL Server 2022 (16.x)

In diesem Artikel wird erläutert, was eine Transact-SQL-Momentaufnahmesicherung ist, und warum und wie Sie sie verwenden. Transact-SQL-Momentaufnahmesicherungen sind in SQL Server 2022 (16.x) neu.

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 beanspruchen Ressourcen (CPU, Speicher, E/A, Netzwerk), die sich für die Dauer der Sicherung auf den Durchsatz der parallelen OLTP-Arbeitsauslastung auswirken. 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 der Hardwareebene erfolgt, handelt es sich nicht um eine reine SQL Server-Lösung. SQL Server muss zunächst die Daten- und Protokolldateien für die Momentaufnahme vorbereiten. So wird gewährleistet, dass sich die Dateien in einem Zustand befinden, der später wiederhergestellt werden kann. Sobald die Vorbereitung abgeschlossen ist, werden Schreibvorgänge auf SQL Server angehalten (Leseanforderungen sind nach wie vor zulässig). Die Kontrolle wird an die Sicherungsanwendung übergeben, um die Momentaufnahme zu erstellen. Sobald die Momentaufnahme erfolgreich erstellt wurde, muss die Anwendung die Kontrolle wieder an SQL Server zurückgeben, und die Schreibvorgänge werden fortgesetzt. Da die Schreibvorgänge für die Dauer der Momentaufnahme eingefroren werden müssen, ist es wichtig, dass die Erstellung der Momentaufnahme schnell erfolgt, damit die Arbeitsauslastung auf dem Server nicht für längere Zeit 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. Dadurch können Benutzer eigene, kleine Sicherungsanwendungen erstellen, die entweder unter Windows oder Linux ausgeführt werden können. Oder sie können sogar Skriptlösungen nutzen, wenn der zugrunde liegende Speicher eine Skriptschnittstelle zur Initiierung einer Momentaufnahme unterstützt.

Hier finden Sie ein PowerShell-Beispielskript, das eine End-to-End-Lösung für die Sicherung und Wiederherstellung einer Datenbank in einer Azure SQL-IaaS-VM unter Verwendung der in SQL Server 2022 (16.x) (und höher) eingeführten Funktion für T-SQL-Momentaufnahmesicherungen 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:

1. 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.
2. Gleichzeitiges Erstellen von Momentaufnahmen mehrerer Datenbanken mit den neuen Befehlen BACKUP GROUP und BACKUP SERVER. Mit dieser Option können Momentaufnahmen mit der Granularität des zugrundeliegenden Speichers erstellt werden, damit nicht mehrmals eine Momentaufnahme desselben Datenträgers erstellt werden muss.
3. Durchführen von vollständigen Sicherungen (FULL) und vollständigen Kopiesicherungen (COPY_ONLY FULL). Diese Sicherungen werden auch in msdb aufgezeichnet.
4. 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 zusammenarbeiten kann, um eine Momentaufnahmesicherung durchzuführen:

Ein Wiederherstellungsskript kann in ähnlicher Weise funktionieren:

Begrenzungen

Mit diesem Feature können maximal 64 Datenbanken gesichert werden. 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 umfasst mit Ausnahme der Kopfzeile und des Dateiinhalts keine Inhalte der Datenbank. Die Shell-Datei, die im Rahmen der Momentaufnahmesicherung erstellt wird, sollte zusammen mit dem eigentlichen Momentaufnahme-URI verwendet werden, um eine vollständige Sicherung zu erhalten. Bei der Wiederherstellung (RESTORE) einer Datenbank aus dieser Datei muss der Benutzer die Datenbankdateien vom Momentaufnahme-URI zum Bereitstellungspunkt kopieren, bevor der RESTORE-Befehl ausgegeben wird. Die Benutzer können für diese Metadatendatei der Momentaufnahmesicherung alle herkömmlichen T-SQL-Befehle, z. B. beispielsweise RESTORE HEADERONLY, RESTORE FILELISTONLY, 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 sich mehrere Datenbanken auf demselben zugrunde liegenden Datenträger befinden, 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 zum Erstellen einer Momentaufnahmesicherung

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 für die Momentaufnahmesicherung: master, model, und msdb.

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 es vorziehen, nur eine Kopiesicherung zu erstellen, verwenden Sie das Schlüsselwort COPY_ONLY (siehe die folgenden Beispiele).

Ausführen von Momentaufnahmesicherungen mit COPY_ONLY

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 BACKUP zu verwenden, da dies bereits beim Anhalten der Datenbank für die Momentaufnahmesicherung festgelegt ist.

Markieren des Sicherungssatzes

Sie können die Optionen MEDIANAME und MEDIADESCRIPTION im Sicherungsbefehl verwenden, um den der Momentaufnahme zugeordneten URI zu markieren. So kann die Sicherungsdatei die zugrunde liegenden Momentaufnahmeinformationen zusammen mit den Datenbankmetadaten enthalten. Sie können auch die Optionen NAME und DESCRIPTION verwenden, um den URI mit der einzelnen Momentaufnahme für den Sicherungssatz zu markieren.

SQL Server interpretiert keine Informationen der BEZEICHNUNG. Mithilfe des Befehls RESTORE LABELONLY können sich Benutzer jedoch den der Momentaufnahmesicherung zugeordneten URI anzeigen lassen.

Anschließend können Sie die Momentaufnahmedatenträger vom URI an die VM anfügen, um die Momentaufnahme wiederherzustellen. Der Momentaufnahme-URI, der in mediaNAME und MEDIADESCRIPTION gespeichert ist, steht auch in der msdb-Datenbanktabelle msdb.dbo.backupmediaset zur späteren Ansicht zur 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;

Filtern der RESTORE FILELISTONLY-Ausgabe auf einen Sicherungssatz

Um mit RESTORE FILELISTONLY gezielt einen bestimmten Sicherungssatz aus mehreren Sicherungssätzen auszuwählen, verwenden Sie die FILE-Klausel, die bereits für RESTORE FILELISTONLY unterstützt wird.

RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;

Filtern der RESTORE FILELISTONLY-Ausgabe auf eine Datenbank

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. Dadurch wird ein Sicherungssatz pro Datenbank erstellt.

Wenn eine Servermomentaufnahme drei Datenbanken in einer Sicherungsdatei enthält, die einen einzigen Sicherungssatz enthält, erzeugt der folgende Befehl drei Sicherungssätze – einen für jede Datenbank. Es wird ein Verzeichnis mit <file_name_prefix>_<unique_time_stamp> für die Ausgabedateien erstellt.

RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db1.bkm'
WITH METADATA_ONLY;

Verwenden von RESTORE BACKUPSETONLY zum Extrahieren einer bestimmten Datenbank aus einem Sicherungssatz mit mehreren Datenbanken

RESTORE BACKUPSETONLY unterstützt den Parameter DBNAME, wenn der Benutzer eine der 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 Details des Sicherungssatzes für T-SQL-Momentaufnahmesicherungen

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 für die T-SQL-Problembehandlung

Das folgende T-SQL-Beispielskript kann verwendet werden, um angehaltene Datenbanken auf dem Server zu erkennen und den angehaltenen Zustand bei Bedarf zu beenden.

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

Weitere Informationen

• backupmediaset (Transact-SQL)
• BACKUP (Transact-SQL)