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 di snapshot Transact-SQL sono una novità di 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 alla durata del 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, 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 da garantire che i file siano 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, che completa lo snapshot. Al termine dello snapshot, l'applicazione deve restituire il controllo a SQL Server, su 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 sul 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 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. 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 di Azure SQL usando le funzionalità di backup degli snapshot T-SQL introdotte in SQL Server 2022 (16.x) (e versioni successive).
Flusso di lavoro
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:
- Bloccare un database con il comando ALTER: consente di eseguire lo snapshot dell'archiviazione sottostante. Successivamente, è possibile sbloccare il database e registrare lo snapshot con il comando BACKUP.
- 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.
- Eseguire backup COMPLETI e backup COMPLETI COPY_ONLY. Questi backup vengono registrati anche in
msdb. - Eseguire il recupero temporizzato usando i backup del log eseguiti con l'approccio di streaming normale dopo il backup COMPLETO 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 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:
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, 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. Per il RIPRISTINO di un database da questo file, è necessario 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, 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 DISCO 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.
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 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;
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.
Sospendere più database utente con un singolo comando
Registrare lo 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, 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 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 sarà disponibile anche per la visualizzazione successiva nella tabella di database msdbmsdb.dbo.backupmediaset.
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 mostra 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;
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';
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 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. 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 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';
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
Il seguente script T-SQL di esempio può essere usato per rilevare i database sospesi nel server e annullarne 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
Vedi anche
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per