Criar um backup de instantâneo Transact-SQL

Aplica-se a: SQL Server 2022 (16.x)

Este artigo explica o quê, por que e como usar backups de snapshot Transact-SQL. Transact-SQL Cópias de segurança instantâneas (T-SQL) foram introduzidas 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 backups de 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) que afetam a taxa de transferência da carga de trabalho OLTP simultânea durante o backup. Uma maneira de tornar o desempenho de backup constante, em vez de depender do tamanho dos dados, é executar um backup de instantâneo usando mecanismos fornecidos pelo hardware ou serviço de armazenamento subjacente.

Como o backup em si acontece no nível de hardware, esse recurso não é uma solução pura do SQL Server. O SQL Server deve primeiro preparar os dados e os arquivos de log para o instantâneo, para que os arquivos tenham a garantia de estar em um estado que possa ser restaurado posteriormente. Quando essa etapa for concluída, as operações de gravação serão pausadas no SQL Server (solicitações de leitura ainda são permitidas) e o controle será entregue ao aplicativo de backup para concluir o instantâneo. Quando o instantâneo for concluído com êxito, o aplicativo deverá retornar o controle de volta ao SQL Server, onde as operações de gravação serão retomadas.

Como precisamos suspender as operações de gravação durante a operação de snapshot, é crucial que o snapshot ocorra rapidamente, evitando assim que a carga de trabalho no servidor seja interrompida por um período prolongado. No passado, os usuários dependiam de soluções que não eram da Microsoft criadas com base no serviço SQL Writer para concluir backups de instantâneo. O serviço SQL Writer depende do Windows VSS (Volume Shadow Service) juntamente com o SQL Server VDI (Virtual Device Interface) para realizar a coordenação entre o SQL Server e o instantâneo de nível de disco.

Os clientes de backup baseados no serviço SQL Writer tendem a ser complexos e só funcionam no Windows. Com backups de instantâneo T-SQL, o lado 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 pequenos aplicativos de backup que podem ser executados no Windows ou Linux, ou até mesmo soluções com script, se o armazenamento subjacente suportar uma interface de script para iniciar um snapshot.

Aqui está um exemplo de script do PowerShell, que demonstra uma solução completa de backup e restauração de um banco de dados em uma Máquina Virtual IaaS SQL do Azure. O exemplo usa os recursos de backup de instantâneo T-SQL introduzidos no SQL Server 2022 (16.x).

Fluxo de trabalho

A sintaxe de backup de instantâneo T-SQL separa o mecanismo de instantâneo que depende 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, que oferece a oportunidade de efetuar a captura instantânea do armazenamento subjacente. Depois disso, você pode descongelar o banco de dados e gravar 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, os snapshots podem ser executados na granularidade do snapshot do armazenamento subjacente, eliminando a necessidade de executar um snapshot do mesmo disco várias vezes.

3. Execute cópias de segurança FULL e COPY_ONLY FULL. Esses backups também são registrados em msdb.

4. Execute a recuperação point-in-time usando backups de log feitos com a abordagem de streaming normal após o snapshot FULL backup. Backups diferenciais de streaming também são suportados, se assim o desejarem.

Observação

Os bitmaps diferenciais são limpos no 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. Todos os backups diferenciais subsequentes são mais intensivos em E/S, pois eles devem verificar todo o banco de dados para fazer o backup diferencial. O bitmap diferencial torna-se válido novamente após um backup de snapshot bem-sucedido.

O diagrama a seguir ilustra o fluxo de trabalho geral de alto nível de backups instantâneos do T-SQL:

A etapa de snapshot intermediário requer que você inicie o snapshot no armazenamento subjacente. O diagrama a seguir mostra um exemplo de como um script de backup pode funcionar 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 fazer 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 T-SQL usados para executar backup de instantâneo em disco. Quando um backup de instantâneo é gravado em disco, somente os metadados conectados ao backup de instantâneo são gravados no arquivo. A saída não contém nenhum conteúdo do banco de dados, exceto o cabeçalho e o conteúdo do arquivo. O ficheiro shell criado como parte da realização do backup de snapshot deve ser usado com o URI real do snapshot para fazer um backup completo. Uma RESTORE de um banco de dados a partir deste ficheiro requer que o usuário copie os arquivos de banco de dados do URI de captura instantânea 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 e RESTORE FILELISTONLY, neste arquivo de metadados de backup instantâneo, juntamente com RESTORE DATABASE. A sintaxe suporta a gravação de metadados de backup de instantâneo para DISK ou URL. Os conjuntos de backup de snapshot também podem ser acrescentados assim como os conjuntos de backup de streaming num único ficheiro.

Observação

Para backup para URL, os blobs de bloco são preferidos, embora os blobs de página também sejam suportados para o SQL Server no Windows. Para SQL Server em Linux e contêineres, apenas blobs de bloco são suportados.

Um. Suspender um banco de dados de utilizador único para backup instantâneo e efetuar um registo de 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ários para backup de snapshot

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 de usuários no servidor para backup de instantâneo

Se todos os bancos de dados de usuários no servidor precisarem ser suspensos, use o seguinte comando.

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.

D. Suspender vários bancos de dados de usuários com um único comando

Registre um instantâneo de todos os bancos de dados de usuários 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;

Observação

Por padrão, os comandos SUSPEND_FOR_SNAPSHOT_BACKUP limpam o bitmap diferencial. Se preferir executar um backup somente cópia, use a palavra-chave COPY_ONLY, conforme mostrado nos exemplos a seguir.

E. Executar backups de snapshot somente cópia

Como o bitmap diferencial é limpo antes do congelamento, o SUSPEND_FOR_SNAPSHOT_BACKUP oferece a opção (COPY_ONLY) de não limpar o bitmap diferencial antes do congelamento.

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.

F. Faça 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, poderá executar um backup de instantâneo da seguinte maneira:

1. Suspenda o banco de dados (o que congela a entrada/saída de escrita nos ficheiros de log e de dados).

   ALTER SERVER CONFIGURATION
   SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
   

2. Tire um instantâneo de todos os discos subjacentes onde os dados 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 de backup de instantâneo (.bkm).

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

Para restaurar esta cópia de segurança numa fase posterior, siga estes passos:

1. Monte ou anexe os discos de snapshots na VM onde deseja restaurar.

2. Use o arquivo .bkm (da etapa 3 na lista anterior) ao 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 para colocá-los no destino necessário. Para obter um exemplo, consulte Exemplo N.

G. Marcar o conjunto de backup

Você pode usar as opções MEDIANAME e MEDIADESCRIPTION no comando backup para marcar o URI associado ao snapshot. 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 individual do conjunto de backups.

O SQL Server não interpreta as informações LABEL de forma alguma. 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 snapshot localizados no URI à VM para restaurar o snapshot. O URI de instantâneo armazenado no MEDIANAME e no MEDIADESCRIPTION também é disponibilizado para visualização na tabela dbo.backupmediasetda base de dados msdb.

H. Resultado do backup de snapshot com RESTORE HEADERONLY

A saída com RESTORE HEADERONLY se parece com o exemplo a seguir, 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 da cópia de segurança de snapshot 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 de vários conjuntos de backup com RESTORE FILELISTONLY, use a cláusula FILE que já é suportada no RESTORE FILELISTONLY.

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

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

Para escolher especificamente um único banco de dados entre os vários disponíveis no conjunto de backup selecionado com RESTORE FILELISTONLY, utilize a cláusula FILE juntamente com a cláusula DBNAME. A cláusula DBNAME só pode ser usada em conjuntos de backup de snapshot.

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

L. Restaurar um banco de dados instantâneo

Restaurar um banco de dados a partir do backup de instantâneo é como anexar um banco de dados. Execute o comando restore 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 cópia de segurança instantânea. O exemplo a seguir restaura testdb1. Se 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';

M. Restaurar um banco de dados de cópia instantânea 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 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;

O. Use 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. Este comando produz um conjunto de backup por banco de dados.

Se um instantâneo do servidor contiver três bancos de dados em um arquivo de backup contendo um único 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. Use RESTORE BACKUPSETONLY para extrair um banco de dados específico em um conjunto de backup contendo vários bancos de dados

RESTORE BACKUPSETONLY suporta o parâmetro DBNAME se o usuário quiser produzir um banco de dados dos três bancos de dados no conjunto de backup. Ele também suporta o 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';

Q. Monitorizar o estado de suspensão e bloqueios adquiridos

Você pode usar as seguintes visões de gestão dinâmica (DMVs):

• sys.dm_server_suspend_status (ver o estado de suspensão)
• sys.dm_tran_locks (ver as fechaduras adquiridas)

R. Listar detalhes do conjunto de backup

O script de exemplo a seguir lista as informações do conjunto de backup para backups de instantâneo 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 foi suspenso para backup de snapshot

O script de exemplo a seguir gera propriedades de nível de banco de dados para bancos de dados suspensos para backup de instantâneo.

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

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

O script de exemplo a seguir deteta bancos de dados suspensos no servidor e os dessuspende, 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)