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 (T-SQL)-Momentaufnahmesicherungen wurden in SQL Server 2022 (16.x) eingeführt.

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 während 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 bei diesem Feature 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 dieser Schritt 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 während 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 Nicht-Microsoft-Lösungen 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 veranschaulicht. Im Beispiel werden die Funktionen der T-SQL-Momentaufnahmesicherung verwendet, die in SQL Server 2022 (16.x) eingeführt wurden.

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: Dies bietet 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 FULL-Sicherungen und COPY_ONLY FULL-Sicherungen. 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.

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. RESTORE HEADERONLY und RESTORE FILELISTONLY, zusammen mit RESTORE DATABASE ausführen. Die Syntax unterstützt das Schreiben von Metadaten für Momentaufnahmesicherungen auf DISK oder URL. Die Momentaufnahmesicherungsätze können genauso wie Streamingsicherungssätze in einer einzigen Datei angefügt werden.

A. 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;

B. 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;

C. 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;

D: Anhalten mehrerer Benutzerdatenbanken mit einem einzelnen Befehl

Zeichnen Sie eine 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;

E. Ausführen von Momentaufnahmesicherungen mit COPY_ONLY

Da die differenzielle Bitmap vor dem Fixieren gelöscht wird, bietet SUSPEND_FOR_SNAPSHOT_BACKUP eine Option (COPY_ONLY), um die differenzielle Bitmap vor dem Fixieren 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;

F. Sichern einer Datenbank mit Daten und Protokolldateien auf verschiedenen Laufwerken

Wenn Sie über eine Datenbank mit Datendateien (.mdf und .ndf) auf mehreren Laufwerken verfügen und sich die Transaktionsprotokolldatei (.ldf) auf einem anderen Laufwerk befindet, können Sie eine Momentaufnahmesicherung wie folgt ausführen:

1. Halten Sie die Datenbank an (dadurch wird der Schreib-E/A in Daten- und Protokolldateien fixiert).

   ALTER SERVER CONFIGURATION
   SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
   

2. Erstellen Sie eine Momentaufnahme aller zugrunde liegenden Datenträger, auf denen die Datenbankdaten und Protokolldateien vorhanden sind. Dieser Schritt ist hardwareabhängig.

3. Führen Sie die Sicherung mithilfe der Option METADATA_ONLY aus, die die Ausgabe erstellt, die die Sicherungsmetadaten der Momentaufnahme (.bkm) enthält.

   BACKUP DATABASE testdb1
   TO DISK = 'D:\Temp\db.bkm'
   WITH METADATA_ONLY;
   

Führen Sie die folgenden Schritte aus, um diese Sicherung zu einem späteren Zeitpunkt wiederherzustellen:

1. Stellen Sie die Datenträger der Momentaufnahme auf der VM bereit, auf der Sie wiederherstellen möchten, oder fügen Sie sie an.

2. Verwenden Sie die .bkm-Datei (aus Schritt 3 in der vorherigen Liste), wenn Sie eine Datenbankwiederherstellung ausführen.

3. Wenn sich die Laufwerke während der Wiederherstellung unterscheiden, verwenden Sie die Option MOVE für die logischen Dateien, um sie im erforderlichen Ziel zu platzieren. Ein Beispiel finden Sie unter Beispiel N.

G. 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 von LABEL. 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 URI der Momentaufnahme, der in MEDIANAME und MEDIADESCRIPTION gespeichert ist, ist dann auch für die Anzeige in der msdb-Datenbanktabelle dbo.backupmediaset verfügbar.

H. 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;

I. 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;

J. 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;

K. Filtern der RESTORE FILELISTONLY-Ausgabe auf eine Datenbank

Um eine einzelne Datenbank aus mehreren Datenbanken innerhalb des ausgewählten Sicherungssatzes mit RESTORE FILELISTONLY auszuwählen, verwenden Sie die FILE-Klausel mit der DBNAME-Klausel. 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';

L. 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';

M. Wiederherstellen einer Momentaufnahmedatenbank, die in der Mitte aufgeführt ist

Wenn sich die Datenbank, die RESTORED 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;

N. Wiederherstellen der Datenbank mit einem anderem Namen

Sie können die Datenbank mit einem anderem Namen wiederherstellen. Wenn sich die Datenbank, die RESTORED 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;

O. 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. Durch diesen Befehl 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;

P. 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';

F: Überwachen des angehaltenen Status und der eingerichteten Sperren

Sie können die folgenden dynamische Verwaltungssichten verwenden:

• sys.dm_server_suspend_status (Anzeigen des angehaltenen Status)
• sys.dm_tran_locks (Anzeigen der eingerichteten Sperren)

R. Auflisten von Details des Sicherungssatzes

Das folgende Beispielskript listet Informationen des Sicherungssatzes für Transact-SQL-Momentaufnahmensicherungen auf.

SELECT database_name,
    type,
    backup_size,
    backup_start_date,
    backup_finish_date,
    is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;

S. Überprüfen, ob eine Datenbank für die Momentaufnahmesicherung angehalten wurde

Das folgende Beispielskript gibt Eigenschaften auf Datenbankebene für Datenbanken aus, die für die Momentaufnahmesicherung angehalten werden.

SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');

T. Beispielskript für die T-SQL-Problembehandlung

Das folgende Beispielskript erkennt angehaltene Datenbanken auf dem Server und beendet den angehaltenen Zustand bei Bedarf.

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

Zugehöriger Inhalt

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