Estratégia de backup, automação e restore no SQL Server

Configura os backups de uma instância do SQL Server já instalada (Linux ou Windows; instância única, FCI ou réplicas de AG) e faz a recuperação a partir do backup. Backup: escolha do recovery model, full semanal + differential diário + backup de log a cada n minutos, COMPRESSION, CHECKSUM, criptografia com certificado, retenção e agendamento (jobs T-SQL do SQL Server Agent ou Ola Hallengren; no Express, Task Scheduler / cron), backup COPY_ONLY avulso, verificação do backup e checagem da cadeia. Recuperação: restore da cadeia full / differential / log, volta a um momento específico com STOPAT (PITR), transação marcada, restore com outro nome ou em outro servidor com WITH MOVE, tail-log + restore em caso de perda de arquivo de dados, page restore, restore de master / msdb / model, mapeamento de logins (orphaned user) e carga do certificado para backup criptografado.

Escolha o que vai fazer no cartão de cenários; digitando o código de erro (ex.: 824, 4305, 33111, 18456) na busca do catálogo, você chega direto ao cenário correspondente. Para exportar uma tabela ou o resultado de uma consulta para arquivo (não é backup, é movimentação de dados), veja exportação com bcp.

Topologia: 1 servidor, Failover Cluster Instance ou réplicas de Always On AG (restore em outro servidor: origem + destino)

Cenários

• Montar a estratégia de backup (backups agendados + retenção) — Servidor novo ou banco de dados sem backup regular: configuram-se os jobs de full / differential / log, a retenção e o monitoramento.
• Fazer um backup avulso (ex.: COPY_ONLY antes de uma mudança) — Um único backup antes de uma mudança, patch ou upgrade, ou para copiar para um ambiente de teste; COPY_ONLY não quebra a cadeia.
• Verificar os backups (histórico, VERIFYONLY, restore de teste) — Para provar que os backups realmente podem ser restaurados: histórico de backup, VERIFYONLY, HEADERONLY / FILELISTONLY e restore de teste em um banco temporário + CHECKDB.
• A cadeia de backup está íntegra? (lacuna de LSN, arquivo ausente) — Antes de um restore ou em auditoria periódica: monta a cadeia a partir do histórico do msdb; encontra lacunas de LSN, arquivos que não estão no disco e backups intercalados. Não altera nada.
• Restaurar o banco de dados a partir do backup (cadeia, STOPAT / STOPATMARK, MOVE / REPLACE, outro servidor) — Dados foram apagados ou atualizados por engano, é preciso uma cópia do banco ou ele será movido para outro servidor; a instância está no ar. Cadeia full + differential + log, um momento específico ou uma transação marcada, outro nome / diretório, até o último instante com tail-log.
• O banco de dados não abre: arquivo de dados ausente ou corrompido (tail-log + restore) — O banco está em RECOVERY_PENDING ou SUSPECT, o .mdf/.ndf não pode ser lido, mas a instância está no ar: primeiro faz-se o tail-log com NO_TRUNCATE e depois o restore até o último instante. O banco fica indisponível até o fim do processo.
• Algumas páginas corrompidas (Msg 823 / 824): page restore — Consultas ou o CHECKDB dão erro em páginas específicas (msdb.dbo.suspect_pages): apenas as páginas corrompidas são restauradas do backup. No Enterprise/Developer o banco continua aberto; no Standard/Express fica indisponível por pouco tempo.
• Restaurar o banco master (modo de usuário único) — master corrompido ou perda de logins / linked servers / configurações do servidor: a instância é iniciada em modo de usuário único (-m), o master é restaurado e o SQL Server se desliga sozinho. Exige downtime; se a instância não sobe de jeito nenhum, primeiro recriam-se os bancos do sistema.
• Restaurar o banco msdb ou model — Jobs, histórico de backup, Database Mail (msdb) ou o modelo para novos bancos (model) corrompidos / perdidos: restaura-se com REPLACE a partir de um backup feito no mesmo build; o Agent para por pouco tempo.
• Usuários não conseguem conectar após o restore (orphaned user, mapeamento de logins) — O banco foi movido ou restaurado em outro servidor e a aplicação recebe 'Login failed': os logins são transferidos da origem com SID e hash de senha, e os usuários restantes são vinculados aos logins. O banco continua aberto.
• Backup criptografado não abre (sem certificado, Msg 33111) — O backup (ou o backup de um banco com TDE) está criptografado com certificado e o certificado não existe neste servidor: localiza-se o thumbprint, carrega-se o certificado com a private key e verifica-se o backup. Os bancos existentes não são tocados.

Antes de começar

• O RPO (perda de dados aceitável) e o RTO (indisponibilidade aceitável) foram definidos por escrito com a área de negócio. Plano escolhido: …perda de dados de no máximo … minutos…volta ao último backup full/differential (SIMPLE: até um dia de perda de dados)…; o tempo de restore será medido com um restore de teste do maior banco.
• O SQL Server está no ar; o mssql-tools18 (sqlcmd) está instalado e … é sysadmin. Antes de executar o script, leia a senha para uma variável sem exibi-la na tela: read -rsp 'Senha: ' SQLCMDPASSWORD; export SQLCMDPASSWORD e sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh.
• O PowerShell foi aberto como administrador (Iniciar → "PowerShell" → botão direito → Run as administrator; Administrator: na barra de título) com uma conta do Windows que é sysadmin no SQL Server; o sqlcmd está no PATH.
• O diretório de backup (…) fica em um disco separado do disco de dados e tem espaço livre para pelo menos uma semana de backups (aproximadamente: tamanho dos dados usados × 2 + alteração diária × número de dias; com compactação, geralmente de 3 a 5 vezes menos).
• O diretório de backup (…) fica em um disco separado do disco de dados e tem espaço livre para pelo menos uma semana de backups (aproximadamente: tamanho dos dados usados × 2 + alteração diária × número de dias; com compactação, geralmente de 3 a 5 vezes menos).
• O tamanho dos bancos é conhecido; a janela de backup (o tempo que o full leva) não coincide com o horário comercial.
• Foi definido como os backups serão copiados para fora do servidor (outro servidor, NAS, object storage, software de backup). Um backup que fica no mesmo disco não serve para nada em caso de perda do disco ou do servidor.
• A versão do SQL Server em que o restore será feito é igual ou mais nova que a versão em que o backup foi feito. Um backup feito em versão mais nova não pode ser restaurado em versão mais antiga (ex.: 2022 → 2019 não funciona).
• A equipe da aplicação foi avisada e a aplicação foi parada: todas as conexões com o banco sobrescrito pelo restore são derrubadas e o banco fica indisponível durante o restore.
• No destino existem os diretórios de dados (…) e de log (…), com dono mssql e espaço livre igual ao tamanho dos arquivos do banco a restaurar (não o tamanho do backup; os valores de Size do FILELISTONLY).
• No destino existem os diretórios de dados (…) e de log (…), a conta de serviço do SQL Server tem permissão de escrita e há espaço livre igual ao tamanho dos arquivos do banco a restaurar.
• O servidor de destino (…) acessa a origem (…) …via SSH (root ou usuário com sudo)…pelo compartilhamento administrativo SMB (…)…; os arquivos de backup podem ser copiados.

Fases do guia

• Situação atual — Listar os bancos de dados e os últimos backups
• Estratégia de backup — Preparar o diretório de backup · Preparar o diretório de backup · Definir o recovery model (…) · Configurar a preferência de backup do AG (…) · Verificar a réplica preferida · Criar o certificado e fazer o backup dele (criptografia) …
• Backup avulso — …Fazer backup full COPY_ONLY…Fazer backup full…Fazer backup differential…Fazer backup de log… (…) · Conferir o histórico de backup (msdb.dbo.backupset)
• Restore — Encontrar a cadeia de restore no histórico de backup · Copiar os arquivos de backup para o servidor de destino · Copiar os arquivos de backup para o servidor de destino · Carregar o certificado no servidor de destino · Carregar o certificado no servidor de destino · Examinar os arquivos de backup (VERIFYONLY, HEADERONLY, FILELISTONLY) …
• Verificação de backup — Conferir o histórico de backup (msdb.dbo.backupset) · Examinar o arquivo de backup (VERIFYONLY, HEADERONLY, FILELISTONLY) · Fazer o restore de teste e executar o DBCC CHECKDB
• Verificação da cadeia de backup — Diagnóstico: estado da cadeia (banco + histórico do msdb) · Os arquivos de backup estão no disco? · Os arquivos de backup estão no disco? · Verificar cada backup da cadeia (VERIFYONLY) · Depois: se a cadeia estiver quebrada, fazer um backup full novo
• Arquivo de dados ausente / corrompido: tail-log + restore — Diagnóstico: estado do banco e dos arquivos · Diagnóstico: os arquivos estão no disco, o arquivo de log está íntegro? · Diagnóstico: os arquivos estão no disco, o arquivo de log está íntegro? · Rede de segurança: copiar os arquivos que restaram · Rede de segurança: copiar os arquivos que restaram · Fazer o backup tail-log (NO_TRUNCATE) …
• Page restore (páginas corrompidas) — Diagnóstico: encontrar as páginas corrompidas (suspect_pages, CHECKDB) · Diagnóstico: a página pertence a qual tabela / índice? · Rede de segurança: fazer backup de log e parar os jobs de backup de log · Montar a sequência de restore a partir do histórico do msdb (PAGE) · Fazer o backup tail-log (NORECOVERY): o banco fica indisponível · Restaurar as páginas corrompidas e aplicar os backups de log (NORECOVERY) …
• restore do master — Diagnóstico: arquivo de backup, versão e o que será perdido · Diagnóstico: por que o SQL Server não sobe? · Diagnóstico: por que o SQL Server não sobe? · Recriar os bancos do sistema (o master não sobe) · Recriar os bancos do sistema (o master não sobe) · Iniciar o SQL Server em modo de usuário único …
• Restore do msdb / model — Diagnóstico: estado, versão e arquivo de backup · Rede de segurança: backup do … atual · Parar o SQL Server Agent · Restaurar o banco … (REPLACE) · Iniciar o SQL Server Agent · Verificar
• Mapeamento de logins (orphaned user) — Diagnóstico: encontrar os usuários órfãos (destino) · Gerar o script de logins na origem (SID + hash de senha) · Gerar o script de logins na origem (SID + hash de senha) · Copiar o script de logins para o destino e executá-lo · Copiar o script de logins para o destino e executá-lo · Vincular os usuários restantes aos logins (ALTER USER WITH LOGIN) …
• Backup criptografado: carregar o certificado — Diagnóstico: certificados e master key deste servidor · Diagnóstico: thumbprint do arquivo do certificado · Diagnóstico: thumbprint do arquivo do certificado · Carregar o certificado com a private key · Carregar o certificado com a private key · Verificar: o backup agora pode ser lido …