Creare un backup di snapshot Transact-SQL

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

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

I database diventano ogni giorno sempre più grandi. 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 produttività del carico di lavoro OLTP simultaneo durante il backup. Un modo per rendere costante le prestazioni del backup, anziché dipendere dalle dimensioni dei dati, consiste nell'eseguire un backup di snapshot usando meccanismi forniti dall'hardware o dal servizio di archiviazione sottostante.

Poiché il backup stesso viene eseguito a livello di hardware, questa funzione non è una soluzione SQL Server pura. SQL Server deve prima preparare i file di dati e di log per lo snapshot, in modo da garantire che i file siano in uno stato che può essere ripristinato in un secondo momento. Al termine di questo passaggio, le operazioni di scrittura vengono messe in pausa in SQL Server (le richieste di lettura sono ancora consentite) e il controllo viene passato all'applicazione di backup, che completa lo snapshot. Una volta completato lo snapshot, l'applicazione deve restituire il controllo a SQL Server, su cui vengono quindi riprese le operazioni di scrittura.

Poiché è necessario bloccare le operazioni di scrittura nel corso dell'operazione snapshot, è essenziale che lo snapshot venga eseguito rapidamente, in modo che il carico di lavoro sul server non venga interrotto per un periodo prolungato. In passato, gli utenti si affidavano a soluzioni non Microsoft basate sul servizio writer SQL per completare i backup degli snapshot. Il servizio writer SQL dipende dal servizio Copia Shadow del volume di Windows e dall’interfaccia del dispositivo virtuale (VDI) di SQL Server, nell’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. Questa funzionalità permette agli utenti di 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. L'esempio usa le funzionalità di backup dello snapshot T-SQL introdotte in SQL Server 2022 (16.x).

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, che consente di eseguire lo snapshot dell'archiviazione sottostante. Successivamente, è possibile sbloccare 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 i backup FULL e i backup COPY_ONLY FULL. Questi backup vengono registrati anche in msdb.

4. Eseguire il recupero temporizzato usando i backup del log eseguiti con l'approccio di streaming normale dopo il backup FULL dello snapshot. Sono supportati anche i backup differenziali di streaming, se necessario.

Nota

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

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

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, con cui viene completato il processo di backup dello snapshot:

Analogamente, uno script di ripristino potrebbe funzionare come segue:

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, viene 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. Un RESTORE di un database da questo file richiede che l’utente copi i file di database dall'URI dello snapshot nel punto di montaggio prima di eseguire il comando RESTORE. Gli utenti possono eseguire tutti i comandi T-SQL tradizionali, ad esempio RESTORE HEADERONLY, e RESTORE FILELISTONLY in questo file di metadati di backup dello snapshot, oltre a RESTORE DATABASE. La sintassi supporta la scrittura dei metadati di backup dello snapshot su 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, si preferiscono i BLOB in blocchi, 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.

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

B. Sospendere database multiutente per il backup di snapshot

Se sono presenti 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;

C. 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, model e msdb) per il backup di snapshot.

D. Sospendere più database utente con un singolo comando

Registrare uno snapshot di tutti i database utente sul 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, i comandi SUSPEND_FOR_SNAPSHOT_BACKUP cancellano la bitmap differenziale. Se si preferisce eseguire un backup di sola copia, usare la parola chiave COPY_ONLY, come illustrato negli esempi seguenti.

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

F. Eseguire il backup di un database con file di dati e di log in unità diverse

Se si dispone di un database con file di dati (.mdf e .ndf) tra più unità e il file registro transazioni (.ldf) in un'unità diversa, è possibile eseguire un backup dello snapshot come indicato di seguito:

1. Sospendere il database (che blocca l'I/O di scrittura sia nei file di dati che nei file di log).

   ALTER SERVER CONFIGURATION
   SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
   

2. Creare uno snapshot di tutti i dischi sottostanti in cui sono presenti i file di dati e di log del database. Questo passaggio dipende dall'hardware.

3. Eseguire il backup usando l'opzione METADATA_ONLY, che crea l'output contenente i metadati di backup dello snapshot (.bkm).

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

Per ripristinare il backup in un secondo momento, seguire questa procedura:

1. Montare o collegare i dischi snapshot nella VM in cui si vuole eseguire il ripristino.

2. Usare il file .bkm (dal passaggio 3 nell'elenco precedente) quando si esegue un ripristino database.

3. Se le unità sono diverse durante il ripristino, usare l'opzione MOVE per i file logici per inserirle nella destinazione richiesta. Per un esempio, vedere Esempio N.

G. Contrassegna il set di backup

È possibile usare le opzioni MEDIANAME e MEDIADESCRIPTION del comando di backup per contrassegnare l'URI associato allo snapshot. Questo uso consente al file di backup di includere le informazioni sullo snapshot sottostanti insieme ai metadati del database. È inoltre 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 è disponibile anche per la visualizzazione nella msdb tabella di database dbo.backupmediaset.

H. Output del backup di snapshot con RESTORE HEADERONLY

L'output con RESTORE HEADERONLY è simile al seguente esempio, 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;

I. Output del backup di snapshot con RESTORE FILELISTONLY

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

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

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

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

.L Ripristinare un database snapshot

Il ripristino di un database dal backup snapshot è simile al collegamento di un database. Eseguire il comando di ripristino 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';

M. Ripristinare un database snapshot elencato al centro

Se il database che deve essere RESTORED è 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;

N. Ripristinare il database con un nome diverso

È possibile ripristinare il database con un nome diverso. Se il database che deve essere RESTORED è 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;

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

Un set di backup snapshot contenente più database di un gruppo o uno snapshot del server può essere suddiviso con il comando RESTORE BACKUPSETONLY. Questo comando genera 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;

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

D. Monitorare lo stato di sospensione e i blocchi acquisiti

È possibile usare le seguenti visualizzazioni a gestione dinamica (DMV):

• sys.dm_server_suspend_status (visualizzare lo stato di sospensione)
• sys.dm_tran_locks (visualizzare i blocchi acquisiti)

.R Elencare i dettagli del set di backup

Lo script di esempio seguente elenca le informazioni del set di backup per i backup di snapshot Transact-SQL.

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

S. Controllare se un database è stato sospeso per il backup dello snapshot

Lo script di esempio seguente restituisce le proprietà a livello di database per i database sospesi per il backup dello snapshot.

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

T. Script di risoluzione dei problemi T-SQL di esempio

Il seguente script di esempio rileva i database sospesi nel server e annulla la sospensione, 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

Contenuto correlato

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