Creare un backup di snapshot Transact-SQL

Si applica a: SQL Server 2022 (16.x)

Questo articolo illustra cosa, perché e come usare i backup di snapshot Transact-SQL. I backup di snapshot Transact-SQL sono nuovi in SQL Server 2022 (16.x).


I database diventano sempre più grandi e grandi ogni giorno. Tradizionalmente, i backup di SQL Server sono backup di streaming. Un backup di streaming dipende dalle dimensioni del database. Le operazioni di backup usano risorse (CPU, memoria, I/O, rete) che influiscono sulla velocità effettiva del carico di lavoro OLTP simultaneo per la durata del backup. Un modo per rendere costante le prestazioni del backup, anziché dipendere dalle dimensioni dei dati, consiste nell'eseguire un backup dello snapshot usando meccanismi forniti dall'hardware o dal servizio di archiviazione sottostante.

Poiché il backup stesso viene eseguito a livello di hardware, non si tratta di una soluzione SQL Server pura. SQL Server deve prima preparare i file di dati e di log per lo snapshot, in modo che i file siano sicuramente in uno stato che può essere ripristinato in un secondo momento. Al termine, l'I/O viene bloccato in SQL Server e il controllo viene passato all'applicazione di backup per completare lo snapshot. Al termine dello snapshot, l'applicazione deve restituire il controllo a SQL Server in cui viene quindi ripreso l'I/O. Poiché è necessario bloccare l'I/O per la durata dell'operazione snapshot, è essenziale che lo snapshot venga eseguito rapidamente, in modo che il carico di lavoro nel server non venga interrotto per un periodo prolungato. In passato, gli utenti si affidavano a soluzioni di terze parti basate sul servizio Writer SQL per completare i backup degli snapshot. Il servizio Writer SQL dipende dal servizio Copia Shadow del volume di Windows insieme a SQL Server VDI (Virtual Device Interface) per eseguire l'orchestrazione tra SQL Server e lo snapshot a livello di disco. I client di backup basati sul servizio writer SQL tendono a essere complessi e funzionano solo in Windows. Con i backup di snapshot T-SQL, il lato SQL Server dell'orchestrazione può essere gestito con una serie di comandi T-SQL. In questo modo gli utenti possono creare applicazioni di backup di piccole dimensioni che possono essere eseguite in Windows o Linux o persino soluzioni con script se l'archiviazione sottostante supporta un'interfaccia di scripting per avviare uno snapshot.

Di seguito è riportato uno script di PowerShell di esempio che illustra una soluzione end-to-end di backup e ripristino di un database in una macchina virtuale IaaS SQL di Azure usando le funzionalità di backup degli snapshot T-SQL introdotte in SQL Server 2022 (16.x) (e versioni successive).

Workflow

La sintassi di backup dello snapshot T-SQL separa il meccanismo di snapshot dipendente dal fornitore dalle operazioni di sospensione e backup. Con questa sintassi, è possibile:

  1. Bloccare un database con il comando ALTER: consente di eseguire lo snapshot dell'archiviazione sottostante. Successivamente, è possibile scongelare il database e registrare lo snapshot con il comando BACKUP.
  2. Eseguire snapshot di più database contemporaneamente con i nuovi comandi BACKUP GROUP e BACKUP SERVER. Con questa opzione, gli snapshot possono essere eseguiti con la granularità dello snapshot dell'archiviazione sottostante, eliminando la necessità di eseguire uno snapshot dello stesso disco più volte.
  3. Eseguire backup COMPLETI e COPY_ONLY backup FULL. Questi backup vengono registrati anche in msdb .
  4. Eseguire il ripristino temporizzato usando i backup del log eseguiti con l'approccio di streaming normale dopo il backup COMPLETO dello snapshot. I backup differenziali di streaming sono supportati anche se necessario.

Nota

Le bitmap differenziali vengono cancellate durante la prima fase durante la sospensione del database con il comando ALTER. Se l'utente decide di scongelare il database senza eseguire un backup perché lo snapshot non è riuscito o per qualsiasi altro motivo, la bitmap differenziale non sarà valida. Pertanto, tutti i backup differenziali successivi saranno più intensi a livello di I/O perché devono analizzare l'intero database per eseguire il backup differenziale. La bitmap differenziale diventerà nuovamente valida dopo un backup di snapshot riuscito.

Il diagramma seguente illustra il flusso di lavoro generale dei backup di snapshot T-SQL:

Diagram that shows process from suspend, to snapshot, to backup.

Il passaggio dello snapshot intermedio richiede di avviare lo snapshot nella risorsa di archiviazione sottostante. Il diagramma seguente illustra un esempio di funzionamento di uno script di backup con SQL Server per completare il processo di backup dello snapshot:

Diagram shows example of how the backup script can work with SQL Server to complete the backup process.

Analogamente, uno script di ripristino potrebbe funzionare come segue:

Diagram shows how the restore script can work with SQL Server to complete the restore task from a snapshot backup.

Limiti

Il numero massimo di database di cui è possibile eseguire il backup con questa funzionalità è 64. Se nel server sono presenti più di 64 database, verrà visualizzato l'errore seguente:

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.

Esempi

Le sezioni seguenti illustrano diversi comandi T-SQL usati per eseguire il backup dello snapshot su disco. Quando un backup di snapshot viene scritto su disco, solo i metadati connessi al backup dello snapshot vengono scritti nel file. L'output non contiene alcun contenuto del database, ad eccezione dell'intestazione e del contenuto del file. Il file della shell creato come parte dell'esecuzione del backup dello snapshot deve essere usato con l'URI dello snapshot effettivo per eseguire un backup completo. RESTORE di un database da questo file richiede all'utente di copiare i file di database dall'URI dello snapshot al punto di montaggio prima di eseguire il comando RESTORE. Gli utenti possono eseguire tutti i comandi T-SQL tradizionali, ad esempio RESTORE HEADERONLY, RESTORE FILELISTONLY in questo file di metadati di backup dello snapshot insieme a RESTORE DATABASE. La sintassi supporta la scrittura dei metadati di backup dello snapshot in DISK o URL. I set di backup dello snapshot possono anche essere aggiunti esattamente come i set di backup di streaming in un singolo file.

Nota

Per il backup nell'URL, i BLOB in blocchi sono preferiti anche se i BLOB di pagine sono supportati per SQL Server in Windows. Per SQL Server in Linux e contenitori, sono supportati solo i BLOB in blocchi.

Sospendere un database utente singolo per il backup dello snapshot e registrare un backup del database

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

Sospendere più database utente per il backup di snapshot

Se più database nello stesso disco sottostante, è possibile sospendere più database con il comando seguente.

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;

Sospendere tutti i database utente nel server per il backup di snapshot

Se tutti i database utente nel server devono essere sospesi, usare il comando seguente.

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

Nota

Nessuno di questi comandi supporta la sospensione dei database di sistema: master, modele msdb per il backup di snapshot.

Sospendere più database utente con un singolo comando

Registrare lo snapshot di tutti i database utente nel server in un singolo set di backup:

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;

Nota

Per impostazione predefinita, la sospensione per i comandi di backup dello snapshot cancella la bitmap differenziale. Se si preferisce eseguire un backup di sola copia, usare la parola chiave COPY_ONLY, come illustrato negli esempi seguenti.

Eseguire backup di snapshot di sola copia

Poiché la bitmap differenziale viene cancellata prima del blocco, SUSPEND_FOR_SNAPSHOT_BACKUP fornisce un'opzione (COPY_ONLY) per non cancellare la bitmap differenziale prima del blocco.

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;

Nota

Non è necessario usare COPY_ONLY nel comando BACKUP, perché è già specificato quando si sospende il database per il backup dello snapshot.

Contrassegna il set di backup

È possibile usare le opzioni MEDIANAME e MEDIADESCRIPTION nel comando di backup per contrassegnare l'URI associato allo snapshot. Questo uso consente al file di backup di portare le informazioni sullo snapshot sottostanti insieme ai metadati del database. È anche possibile usare le opzioni NAME e DESCRIPTION per contrassegnare l'URI con il singolo snapshot del set di backup.

SQL Server non interpreta in alcun modo le informazioni LABEL. Consente tuttavia all'utente di visualizzare l'URI associato al backup dello snapshot con il comando RESTORE LABELONLY.

È quindi possibile collegare i dischi snapshot presenti nell'URI alla macchina virtuale per ripristinare lo snapshot. L'URI dello snapshot archiviato in MEDIANAME e MEDIADESCRIPTION sarà disponibile anche per la visualizzazione successiva nella tabella msdb.dbo.backupmediasetdi msdb database .

Output del backup di snapshot con RESTORE HEADERONLY

L'output con RESTORE HEADERONLY è simile al seguente se il database, il gruppo e il server vengono eseguiti in sequenza e scritti nello stesso file di output:

RESTORE HEADERONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;

Output del backup di snapshot con RESTORE FILELISTONLY

L'output con RESTORE FILELISTONLY visualizza il primo set di backup per impostazione predefinita:

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

Filtrare l'output RESTORE FILELISTONLY in un set di backup

Per selezionare in modo specifico un determinato set di backup da più set di backup con RESTORE FILELISTONLY, usare la clausola FILE già supportata in RESTORE FILELISTONLY.

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

Screenshot of SSMS output to backups set from query.

Filtrare l'output RESTORE FILELISTONLY in un database

Filtro Per selezionare ulteriormente un database singolo da più database all'interno del set di backup selezionato con RESTORE FILELISTONLY usare la clausola FILE con la clausola DBNAME appena introdotta. La clausola DBNAME può essere usata solo nei set di backup di snapshot.

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

Screenshot of results of filtering RESTORE FILELISTONLY output to a database.

Ripristinare un database snapshot

Il ripristino di un database dal backup snapshot è simile al collegamento di un database. Eseguire il comando restore senza l'opzione RECOVERY se il database deve essere collegato senza ripristino. Per impostazione predefinita, RESTORE seleziona il primo database nel set di backup di snapshot. Nell'esempio seguente viene ripristinato testdb1. Se testdb1 esiste già nel server, includere la clausola REPLACE. È necessario montare i file di database prima di eseguire RESTORE.

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

Ripristinare un database snapshot elencato al centro

Se il database che deve essere RIPRISTINATo è al centro, specificare il database da ripristinare con la clausola DBNAME. La sintassi seguente ripristina il database specificato nella clausola DBNAME.

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;

Ripristinare il database con un nome diverso

È possibile ripristinare il database con un nome diverso. Se il database che deve essere RIPRISTINATo è al centro, specificare il database da ripristinare con la clausola DBNAME. La sintassi seguente ripristina il database specificato con la clausola DBNAME e lo rinomina in testdb33.

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;

Usare RESTORE BACKUPSETONLY per estrarre database da un set di backup contenente più database

Un set di backup snapshot contenente più database da un gruppo o uno snapshot del server può essere suddiviso con il comando RESTORE BACKUPSETONLY. In questo modo viene generato un set di backup per ogni database.

Se uno snapshot del server contiene tre database in un file di backup contenente un singolo set di backup, il comando seguente genera tre set di backup, uno per ogni database. Crea una directory con <file_name_prefix>_<unique_time_stamp> per i file di output.

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

Usare RESTORE BACKUPSETONLY per estrarre un database specifico in un set di backup contenente più database

RESTORE BACKUPSETONLY supporta il parametro DBNAME se l'utente vuole restituire un database dai tre database nel set di backup. Supporta anche il parametro FILE per filtrare più set di backup nel file di backup.

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

DmV (Dynamic Management Views) per visualizzare lo stato di sospensione e i blocchi acquisiti

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)

Elencare i dettagli del set di backup per i backup di snapshot T-SQL

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

Proprietà a livello di server e database per verificare se un database è stato sospeso per il backup dello snapshot

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

Script di risoluzione dei problemi T-SQL di esempio

Lo script T-SQL di esempio seguente può essere usato per rilevare i database sospesi nel server e annullarli, se necessario.

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

Vedi anche