Criar um backup de instantâneo do Transact-SQL
Aplica-se a:
SQL Server 2022 (16.x)
Este artigo explica o que, por que e como usar backups de instantâneos Transact-SQL. Os backups de instantâneos Transact-SQL são um novo recurso no SQL Server 2022 (16.x).
Os bancos de dados estão ficando cada vez maiores a cada dia. Tradicionalmente, os backups do SQL Server são streaming. Um backup de streaming depende do tamanho do banco de dados. Operações de backup consomem recursos (CPU, memória, E/S, rede), o que afeta a taxa de transferência da carga de trabalho OLTP simultânea pela duração do backup. Uma forma de tornar constante o desempenho de backup, em vez de depender do tamanho dos dados, é executando um backup de instantâneo usando mecanismos fornecidos pelo hardware ou pelo serviço de armazenamento subjacente.
Como o backup em si acontece no nível do hardware, essa não é uma solução SQL Server pura. O SQL Server deve primeiro preparar os dados e arquivos de log para o instantâneo, de forma que haja garantia de que os arquivos fiquem em um estado que possa ser restaurado posteriormente. Feito isso, a E/S é congelada no SQL Server, e o controle é entregue ao aplicativo de backup para concluir o instantâneo. Depois que o instantâneo for concluído com êxito, o aplicativo precisará retornar o controle de volta para o SQL Server em que a E/S é então retomada. Como devemos congelar a E/S durante a operação de instantâneo, é essencial que este ocorra rapidamente, de forma que a carga de trabalho no servidor não seja interrompida por um período estendido. No passado, os usuários contavam com soluções de terceiros criadas com base no serviço do Gravador do SQL para concluir backups de instantâneo. O serviço do Gravador do SQL depende do Windows VSS (Serviço de Sombra de Volume) juntamente com o SQL Server VDI (Interface do Dispositivo Virtual) para executar a orquestração entre o SQL Server e o instantâneo no nível do disco. Clientes de backup baseados no serviço do Gravador do SQL tendem a ser complexos e só funcionam no Windows. Com os backups de instantâneo do T-SQL, o lado do SQL Server da orquestração pode ser tratado com uma série de comandos T-SQL. Isso permite que os usuários criem seus próprios aplicativos de backup pequenos que podem ser executados no Windows ou no Linux ou até mesmo em soluções com script se o armazenamento subjacente oferecer suporte a uma interface de script para iniciar um instantâneo.
Confira um exemplo de script do PowerShell que demonstra uma solução de ponta a ponta de backup e restauração de um banco de dados em uma máquina virtual IaaS do SQL do Azure usando os recursos de backup de instantâneo T-SQL introduzidos no SQL Server 2022 (16.x) (e superior).
Fluxo de trabalho
A sintaxe de backup de instantâneo do T-SQL desacopla o mecanismo de instantâneo dependente do fornecedor das operações de suspensão e backup. Com essa sintaxe, você pode:
- Congelar um banco de dados com o comando ALTER, fornecendo uma oportunidade para você executar o instantâneo do armazenamento subjacente. Depois disso, você poderá descongelar o banco de dados e registrar o instantâneo com o comando BACKUP.
- Executar instantâneos de vários bancos de dados simultaneamente com os novos comandos BACKUP GROUP e BACKUP SERVER. Com essa opção, instantâneos podem ser feitos na granularidade do instantâneo do armazenamento subjacente, eliminando a necessidade de fazer um instantâneo do mesmo disco várias vezes.
- Execute backups COMPLETOS, bem como backups COMPLETOS COPY_ONLY. Esses backups também são registrados no
msdb. - Execute a recuperação pontual usando backups de log feitos com a abordagem de streaming normal após o backup FULL do instantâneo. Também há suporte para backups diferenciais de streaming se desejado.
Observação
Bitmaps diferenciais são limpos durante o primeiro estágio ao suspender o banco de dados com o comando ALTER. Se o usuário decidir descongelar o banco de dados sem executar um backup porque o instantâneo falhou ou por qualquer outro motivo, o bitmap diferencial será inválido. Portanto, todos os backups diferenciais subsequentes serão mais intensivos em E/S, pois eles devem examinar todo o banco de dados para fazer o backup diferencial. O bitmap diferencial se tornará válido novamente após um backup de instantâneo bem-sucedido.
O seguinte diagrama ilustra o fluxo de trabalho de alto nível dos backups de instantâneo T-SQL:
A etapa de instantâneo do meio exige que você inicie o instantâneo no armazenamento subjacente. O seguinte diagrama mostra um exemplo de como um script de backup pode funcionar em conjunto com o SQL Server para concluir o processo de backup de instantâneo:
Da mesma forma, um script de restauração pode funcionar da seguinte maneira:
Limitações
O número máximo de bancos de dados que você pode copiar para backup com esse recurso é 64. Se houver mais de 64 bancos de dados no servidor, você verá o seguinte erro:
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.
Exemplos
As seções a seguir mostram diferentes comandos do T-SQL usados para executar o backup de instantâneo em disco. Quando um backup de instantâneo é gravado em disco, apenas os metadados conectados ao backup de instantâneo são gravados no arquivo. A saída não incluirá conteúdo do banco de dados, exceto o cabeçalho e o conteúdo do arquivo. O arquivo de shell criado como parte da execução do backup de instantâneo deve ser usado com o URI do instantâneo real para fazer um backup completo. A operação RESTORE de um banco de dados desse arquivo exige que o usuário copie os arquivos de banco de dados do URI do instantâneo para o ponto de montagem antes de emitir o comando RESTORE. Os usuários podem executar todos os comandos T-SQL tradicionais, como RESTORE HEADERONLY, RESTORE FILELISTONLY, nesse arquivo de metadados de backup de instantâneo junto com RESTORE DATABASE. A sintaxe dá suporte à gravação de metadados de backup de instantâneo em DISCO ou URL. Os conjuntos de backup de instantâneo também podem ser acrescentados, assim como os conjuntos de backup de streaming em um só arquivo.
Observação
Para backup em URL, os blobs de blocos são preferenciais, embora os blobs de página tenham suporte para SQL Server no Windows. Para SQL Server em Linux e contêineres, há suporte apenas para blobs de blocos.
Suspender um banco de dados de usuário individual para backup de instantâneo e registrar um backup de banco de dados
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Suspender vários bancos de dados de usuário para backup de instantâneo
Se vários bancos de dados estiverem no mesmo disco subjacente, você poderá suspender vários bancos de dados com o seguinte comando.
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;
Suspender todos os bancos de dados do usuário no servidor para backup de instantâneo
Se todos os bancos de dados de usuários no servidor precisarem ser suspensos, use o comando a seguir.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Observação
Nenhum desses comandos oferece suporte à suspensão de bancos de dados do sistema: master, modele msdb para backup de instantâneo.
Suspenda vários bancos de dados de usuário com um só comando
Registre o instantâneo de todos os bancos de dados de usuário no servidor em um só conjunto de 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;
Observação
Por padrão, a suspensão para comandos de backup de instantâneo limpará o bitmap diferencial. Se preferir executar um backup somente cópia, use a palavra-chave COPY_ONLY, como mostra os exemplos a seguir.
Executar backups de instantâneo somente cópia
Como o bitmap diferencial é limpo antes de ser congelado, SUSPEND_FOR_SNAPSHOT_BACKUP fornece uma opção (COPY_ONLY) para não limpar o bitmap diferencial antes de congelá-lo.
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;
Observação
Não é necessário usar COPY_ONLY no comando BACKUP, pois ele já está especificado ao suspender o banco de dados para backup de instantâneo.
Marcar o conjunto de backup
Você pode usar as opções MEDIANAME e MEDIADESCRIPTION no comando de backup para marcar o URI associado ao instantâneo. Esse uso permite que o arquivo de backup carregue as informações de instantâneo subjacentes junto com os metadados do banco de dados. Você também pode usar as opções NAME e DESCRIPTION para marcar o URI com o instantâneo do conjunto de backup individual.
O SQL Server não interpreta as informações de LABEL. No entanto, ele ajuda o usuário a exibir o URI associado ao backup de instantâneo com o comando RESTORE LABELONLY.
Em seguida, você pode anexar os discos de instantâneo localizados no URI à VM para restaurar o instantâneo. O URI do instantâneo armazenado em MEDIANAME e MEDIADESCRIPTION também estará disponível para exibição subsequente na tabela do banco de dados msdbmsdb.dbo.backupmediaset.
Saída do backup de instantâneo com RESTORE HEADERONLY
A saída com RESTORE HEADERONLY será semelhante à seguinte se o banco de dados, o grupo e o servidor forem executados em sequência e gravados no mesmo arquivo de saída:
RESTORE HEADERONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
Saída do backup de instantâneo com RESTORE FILELISTONLY
A saída com RESTORE FILELISTONLY exibe o primeiro conjunto de backup por padrão:
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
Filtrar a saída RESTORE FILELISTONLY para um conjunto de backup
Para selecionar especificamente um determinado conjunto de backup entre vários conjuntos de backup com RESTORE FILELISTONLY, use a cláusula FILE que já tem suporte em RESTORE FILELISTONLY.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;
Filtrar a saída RESTORE FILELISTONLY para um banco de dados
Filtrar: para selecionar ainda mais um banco de dados individual de vários bancos de dados dentro do conjunto de backup selecionado com RESTORE FILELISTONLY, use a cláusula FILE com a cláusula DBNAME recém-introduzida. A cláusula DBNAME só pode ser usada em conjuntos de backup de instantâneo.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';
Restaurar um banco de dados de instantâneo
Restaurar um banco de dados do backup de instantâneo é como anexar um banco de dados. Execute o comando de restauração sem a opção RECOVERY se o banco de dados precisar ser anexado sem recuperação. Por padrão, RESTORE seleciona o primeiro banco de dados no conjunto de backup de instantâneo. O exemplo a seguir restaura o testdb1. Se o testdb1 já existir no servidor, inclua a cláusula REPLACE. Você precisa montar os arquivos de banco de dados antes de executar 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';
Restaurar um banco de dados de instantâneo listado no meio
Se o banco de dados que precisa ser RESTORED estiver no meio, especifique o banco de dados a ser restaurado com a cláusula DBNAME. A sintaxe a seguir restaura o banco de dados especificado na cláusula 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;
Restaurar o banco de dados da coleção com um nome diferente
Você pode restaurar o banco de dados com um nome diferente. Se o banco de dados que precisa ser RESTORED estiver no meio, especifique o banco de dados a ser restaurado com a cláusula DBNAME. A sintaxe a seguir restaura o banco de dados especificado com a cláusula DBNAME e o renomeia para 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;
Usar RESTORE BACKUPSETONLY para extrair bancos de dados de um conjunto de backup contendo vários bancos de dados
Um conjunto de backup de instantâneo contendo vários bancos de dados de um grupo ou instantâneo de servidor pode ser dividido com o comando RESTORE BACKUPSETONLY. Isso produzirá um conjunto de backup por banco de dados.
Se um instantâneo de servidor contiver três bancos de dados em um arquivo de backup com um só conjunto de backup, o comando a seguir gerará três conjuntos de backup, um para cada banco de dados. Ele cria um diretório com <file_name_prefix>_<unique_time_stamp> para os arquivos de saída.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db1.bkm'
WITH METADATA_ONLY;
Usar RESTORE BACKUPSETONLY para extrair um banco de dados específico em um conjunto de backup contendo vários bancos de dados
RESTORE BACKUPSETONLY dará suporte ao parâmetro DBNAME se o usuário quiser gerar um banco de dados dos três no conjunto de backup. Ele também dá suporte ao parâmetro FILE para filtrar vários conjuntos de backup no arquivo de backup.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';
DMVs (exibições de gerenciamento dinâmico) para ver o status de suspensão e bloqueios adquiridos
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)
Listar detalhes do conjunto de backup para backups de instantâneo T-SQL
SELECT database_name,
type,
backup_size,
backup_start_date,
backup_finish_date,
is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;
Propriedades de nível do servidor e do banco de dados para verificar se um banco de dados foi suspenso para backup de instantâneo
SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');
Exemplo de script de solução de problemas T-SQL
O script T-SQL de exemplo a seguir pode ser usado para detectar bancos de dados suspensos no servidor e cancelar a suspensão, se necessário.
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
Confira também
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de