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 (T-SQL) foram introduzidos 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. As operações de backup consomem recursos (CPU, memória, E/S, rede), o que afeta a produtividade da carga de trabalho OLTP simultânea durante o 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, esse recurso 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. Após a conclusão dessas etapas, as operações de gravação são pausadas no SQL Server (solicitações de leitura continuam permitidas), e o controle é entregue ao aplicativo de backup para concluir o instantâneo. Depois que o instantâneo for concluído, o aplicativo precisará retornar o controle de volta para o SQL Server, onde as operações de gravação são então retomadas.

Como as operações de gravação durante a operação de instantâneo devem ser congeladas, é essencial que isso ocorra rapidamente, de forma que a carga de trabalho no servidor não seja interrompida por um período longo. No passado, os usuários contavam com soluções não produzidas pela Microsoft, que eram criadas com base no serviço Gravador do SQL para concluir os backups de instantâneos. 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. Essa funcionalidade 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.

Aqui está 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. O exemplo usa os recursos de backup de instantâneos do T-SQL introduzidos no SQL Server 2022 (16.x).

Workflow

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:

1. Congele um banco de dados com o comando ALTER, o que oferece 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.

2. Execute 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.

3. Execute backups FULL e backups COPY_ONLY FULL. Esses backups também são registrados no msdb.

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

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 diagrama abaixo 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âneos:

Da mesma forma, um script de restauração poderia 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, o seguinte erro será exibido:

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. Um 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 no ponto de montagem antes de emitir o comando RESTORE. Os usuários podem executar todos os comandos T-SQL tradicionais, como RESTORE HEADERONLY e RESTORE FILELISTONLY nesse arquivo de metadados de backup de instantâneos, juntamente com RESTORE DATABASE. A sintaxe dá suporte à gravação de metadados de backup de instantâneos em DISK 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.

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

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

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

D. 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 único 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;

E. Executar backups de instantâneo somente cópia

Como o bitmap diferencial é limpo antes do congelamento, o SUSPEND_FOR_SNAPSHOT_BACKUP fornece uma opção (COPY_ONLY) para que essa limpeza não aconteça.

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. Fazer backup de um banco de dados com dados e arquivos de log em unidades diferentes

Se você tiver um banco de dados com arquivos de dados (.mdf e .ndf) em várias unidades e o arquivo de log de transações (.ldf) em uma unidade diferente, será possível executar um backup de instantâneos da seguinte maneira:

1. Suspenda o banco de dados (que congela a E/S de gravações nos arquivos de dados e de log).

   ALTER SERVER CONFIGURATION
   SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
   

2. Tire um instantâneo de todos os discos subjacentes em que os dados do banco de dados e os arquivos de log estão presentes. Esta etapa depende do hardware.

3. Execute o backup usando a opção METADATA_ONLY que cria a saída que contém os metadados do backup de instantâneos (.bkm).

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

Para restaurar esse backup em um estágio posterior, siga estas etapas:

1. Monte ou anexe os discos de instantâneos na VM na qual você deseja restaurar.

2. Use o arquivo .bkm (da etapa 3 na lista anterior) quando executar uma restauração de banco de dados.

3. Se as unidades forem diferentes durante a restauração, use a opção MOVE para os arquivos lógicos a fim de colocá-los no destino necessário. Para obter um exemplo, confira Exemplo N.

G. 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 backups individual.

O SQL Server não interpreta as informações LABEL de nenhuma forma. No entanto, ele ajuda o usuário a exibir o URI associado ao backup de instantâneos 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 está disponível para exibição na msdb tabela do banco de dados dbo.backupmediaset.

H. Saída do backup de instantâneo com RESTORE HEADERONLY

A saída com RESTORE HEADERONLY será semelhante ao exemplo abaixo, 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;

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

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

K. Filtrar a saída RESTORE FILELISTONLY para um banco de dados

Para selecionar outro banco de dados individual entre vários bancos de dados no conjunto de backup selecionado com RESTORE FILELISTONLY, use a cláusula FILE com a cláusula DBNAME. A cláusula DBNAME só pode ser usada em conjuntos de backup de instantâneos.

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

L. 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âneos. 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 o 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. 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;

N. 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 como 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. 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âneos contendo vários bancos de dados de um grupo ou instantâneos do servidor pode ser dividido com o comando RESTORE BACKUPSETONLY. Esse comando produz um conjunto de backups 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;

P. Usar RESTORE BACKUPSETONLY para extrair um banco de dados específico em um conjunto de backup contendo vários bancos de dados

O RESTORE BACKUPSETONLY é compatível com o parâmetro DBNAME se o usuário quiser gerar um banco de dados dos três presentes no conjunto de backups. Ele também é compatível com o parâmetro FILE para filtrar vários conjuntos de backups no arquivo de backup.

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

P. Monitorar o status de suspensão e os bloqueios adquiridos

Use as exibições de gerenciamento dinâmico a seguir (DMVs):

• sys.dm_server_suspend_status (exibir o status de suspensão)
• sys.dm_tran_locks (exibir os bloqueios adquiridos)

R. Listar detalhes do conjunto de backups

O script de exemplo a seguir lista as informações do conjunto de backups para backups de instantâneos 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. Verificar se um banco de dados suspendeu o backup de instantâneos

O script de exemplo a seguir gera propriedades no nível do banco de dados para os bancos de dados que tiverem o backup de instantâneos suspensos.

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

T. Exemplo de script de solução de problemas T-SQL

O script de exemplo a seguir detecta bancos de dados suspensos no servidor e cancela 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

Conteúdo relacionado

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