Faça backup e restaure bancos de dados do SQL Server

Aplica-se a:SQL Server

Este artigo descreve os benefícios de fazer backup de bancos de dados do SQL Server, introduz termos básicos de backup e restauração, e aborda estratégias de backup e restauração e considerações de segurança para o SQL Server.

O componente de backup e restauração do SQL Server oferece uma proteção essencial para dados críticos armazenados em seus bancos de dados do SQL Server. Para minimizar o risco de perda catastrófica de dados, faça backup regular dos seus bancos de dados para preservar as modificações nos seus dados. Uma estratégia bem planejada de backup e restauração ajuda a proteger bancos de dados contra perda de dados causada por diversos tipos de falhas. Teste sua estratégia restaurando um conjunto de backups e depois recuperando seu banco de dados, para estar pronto para responder a um desastre.

Além do armazenamento local, o SQL Server também suporta backup e restauração a partir do Armazenamento de Blobs do Azure. Para obter mais informações, consulte Backup e restauração do SQL Server com o Armazenamento de Blobs do Azure. Para arquivos de banco de dados armazenados usando o Armazenamento de Blobs do Azure, o SQL Server 2016 (13.x) oferece a opção de usar instantâneos do Azure para backups quase instantâneos e restaurações mais rápidas. Para obter mais informações, consulte Backups de instantâneos de arquivos para arquivos de banco de dados no Azure. O Azure também fornece uma solução de backup de classe empresarial para SQL Server em execução em VMs do Azure. Uma solução de backup totalmente gerenciada, compatível com grupos de disponibilidade Always On, retenção de longo prazo, recuperação pontual e gerenciamento e monitoramento central. Para obter mais informações, consulte Sobre o backup do SQL Server em VMs do Azure.

Por que fazer backup?

• Fazer backup dos seus bancos de dados do SQL Server, executar procedimentos de restauração de teste nos seus backups e armazenar cópias dos backups em um local seguro e fora do local protege você contra uma possível perda catastrófica de dados. Realizar backup é a única maneira de proteger seus dados.

  Com backups válidos de um banco de dados, você pode recuperar seus dados de muitas falhas, como:

  • Falha de mídia.

  • Por exemplo, erros de usuário, que removem uma tabela por engano.

  • Por exemplo, problemas de hardware, uma unidade de disco danificada ou perda permanente de um servidor.

  • Desastres naturais. Ao usar o SQL Server Backup para Armazenamento de Blobs do Azure, você pode criar um backup fora do local em uma região diferente da sua localização local, para usar caso um desastre natural afete sua localização local.

• Além disso, os backups de um banco de dados são úteis para fins administrativos rotineiros, como copiar um banco de dados de um servidor para outro, configurar o espelhamento do banco de dados ou grupos de disponibilidade Always On e fazer arquivamento.

Glossário de termos de backup

Estratégias de backup e restauração

Você deve personalizar estratégias de backup e restauração para seu ambiente e recursos disponíveis. A recuperação confiável requer uma estratégia de backup e restauração. Uma estratégia bem elaborada equilibra os requisitos do negócio para máxima disponibilidade e perda mínima de dados contra o custo de manutenção e armazenamento de backups.

Uma estratégia de backup e restauração contém uma parte de backup e uma parte de restauração. A parte de backup define o tipo e a frequência dos backups, o tipo e a velocidade do hardware necessário, como testar backups e onde e como armazenar a mídia de backup (incluindo considerações de segurança). A parte de restauração define quem é responsável por realizar restaurações, como realizar restaurações para atender aos seus objetivos de disponibilidade de banco de dados e perda mínima de dados, e como testar restaurações.

Uma estratégia eficaz de backup e restauração exige planejamento, implementação e testes cuidadosos. É necessário realizar testes. Você não tem uma estratégia de backup até restaurar com sucesso backups em todas as combinações incluídas na sua estratégia e testar cada banco de dados restaurado para verificar consistência física. Considere vários fatores, incluindo:

• As metas da sua organização em relação aos bancos de dados de produção, especialmente os requisitos de disponibilidade e proteção de dados contra perdas ou danos.

• A natureza de cada banco de dados: o tamanho, os padrões de uso, a natureza de seu conteúdo, os requisitos dos dados e assim por diante.

• Restrições em recursos, como hardware, pessoal, espaço para armazenar mídia de backup, segurança física da mídia armazenada e assim por diante.

Recomendações de melhores práticas

Não conceda mais privilégios do que o necessário às contas que realizam operações de backup ou restauração. Para mais informações, veja backup e restauração para detalhes específicos de permissões. Criptografe backups de banco de dados e, se possível, comprima-os .

Use extensões de arquivo consistentes para facilitar a identificação e gestão dos backups. O SQL Server não exige nem aplica essas extensões, mas a consistência ajuda em tarefas operacionais, como configurar exclusões de antivírus para arquivos de backup. Para obter mais informações, consulte Configurar software antivírus para trabalhar com o SQL Server.

• Os arquivos de backup do banco de dados devem ter essa .BAK extensão.
• Os arquivos de backup de log devem ter a extensão .TRN.

Use armazenamento separado

Coloque seus backups do banco de dados em um local físico separado ou em um dispositivo separado dos arquivos do banco de dados. Quando o drive físico que armazena seus bancos de dados falha ou trava, a recuperação depende da sua capacidade de acessar o disco separado ou dispositivo remoto que armazenou os backups. Você pode criar vários volumes ou partições lógicas a partir do mesmo disco físico. Revise cuidadosamente a partição do disco e os layouts dos volumes lógicos antes de escolher um local de armazenamento para os backups.

Escolher o modelo de recuperação apropriado

As operações de backup e restauração ocorrem dentro do contexto de um modelo de recuperação. Um modelo de recuperação é uma propriedade de banco de dados que controla a forma de gerenciamento do log de transações. Portanto, o modelo de recuperação de um banco de dados determina quais tipos de cenários de backup e restauração o banco suporta, e o tamanho dos backups de seus registros de transações. Em geral, um banco de dados usa o modelo de recuperação simples ou o modelo de recuperação completa. Você pode complementar o modelo de recuperação completa alternando para o modelo de recuperação bulk-logged antes das operações em massa. Para obter uma introdução a esses modelos de recuperação e a como eles afetam o gerenciamento do log de transações, consulte o log de transações.

A melhor escolha de modelo de recuperação de banco de dados depende das necessidades do seu negócio. Para evitar gerenciamento de log de transações e simplificar o backup e a restauração, use o modelo de recuperação simples. Para minimizar a exposição à perda de trabalho ao custo da sobrecarga administrativa, use o modelo de recuperação completa. Para minimizar o impacto no tamanho do log durante operações com registro em massa e ainda permitir a recuperação dessas operações, use o modelo de recuperação bulk-logged. Para informações sobre o efeito dos modelos de recuperação em backup e restauração, veja Visão geral do Backup (SQL Server).

Planejar a estratégia de backup

Depois de selecionar um modelo de recuperação que atenda aos requisitos do seu negócio para um banco de dados específico, planeje e implemente uma estratégia de backup correspondente. A melhor estratégia de backup depende de vários fatores. Os seguintes fatores são especialmente importantes:

• Quantas horas por dia os aplicativos precisam para acessar o banco de informações?

  Se houver um período previsível fora de pico, você deve agendar backups completos do banco de dados para esse período.

• Com que frequência as alterações e atualizações deverão ocorrer?

  Se as mudanças forem frequentes, considere:

  • No modelo de recuperação simples, você pode agendar backups diferenciais entre backups completos do banco de dados. Um backup diferencial captura só as alterações desde o último backup completo do banco de dados.

  • No modelo completo de recuperação, você pode agendar backups frequentes de logs. O agendamento de backups diferenciais entre backups completos pode reduzir o tempo de restauração reduzindo o número de backups de log a serem restaurados após a restauração dos dados.

• Mudanças provavelmente ocorrerão apenas em uma pequena parte do banco de dados ou em grande parte?

  Para um banco de dados grande no qual as mudanças se concentram em um subconjunto dos arquivos ou grupos de arquivos, backups parciais ou completos de arquivos podem ser úteis. Para obter mais informações, consulte Backups Parciais (SQL Server) e backups de arquivos completos (SQL Server).

• Quanto espaço em disco um backup completo de banco de dados exige?

• Há quanto tempo sua empresa exige a manutenção de backups?

  Certifique-se de ter um cronograma de backup adequado que atenda às necessidades do aplicativo e aos requisitos de negócio. À medida que os backups envelhecem, o risco de perda de dados aumenta, a menos que você tenha uma forma de regenerar todos os dados até o ponto de falha. Antes de descartar backups antigos devido aos limites de armazenamento, considere se você precisa recuperar dados de uma data tão antiga.

Estimar o tamanho de um backup de banco de dados completo

Antes de implementar uma estratégia de backup e restauração, estime quanto espaço em disco um backup completo de banco de dados consome. A operação de backup copia os dados no banco de dados para o arquivo de backup. O backup contém apenas os dados reais do banco de dados, não nenhum espaço não utilizado. Portanto, o backup é geralmente menor do que o próprio banco de dados. Para estimar o tamanho de um backup completo do banco de dados, use o sp_spaceused procedimento armazenado do sistema. Para obter mais informações, consulte sp_spaceused.

Agendar backups

Uma operação de backup tem efeito mínimo nas transações em execução, então você pode rodar backups durante as operações normais. Você pode realizar um backup do SQL Server com efeito mínimo sobre as cargas de trabalho de produção.

Depois de decidir quais tipos de backups precisa e com que frequência realizar cada tipo, agende backups regulares como parte de um plano de manutenção de banco de dados. Para obter informações sobre planos de manutenção e como criá-los para fazer backups de banco de dados e backups de log, consulte Use the Maintenance Plan Wizard.

Teste seus backups

Você não tem uma estratégia de restauração até testar seus backups. Teste minuciosamente sua estratégia de backup para cada banco de dados restaurando uma cópia do banco em um sistema de teste. É necessário testar a restauração de cada tipo de backup que você pretende usar. Depois de restaurar o backup, execute DBCC CHECKDB no banco de dados para confirmar que a mídia de backup não está danificada.

Verificar a estabilidade e a consistência da mídia

Use as opções de verificação fornecidas pelas utilidades de backup (BACKUPcomando T-SQL, Planos de Manutenção do SQL Server, seu software ou solução de backup, e assim por diante). Veja um exemplo em RESTORE Instruções - VERIFYONLY.

Use recursos avançados, como BACKUP CHECKSUM para detectar problemas na própria mídia de backup. Para obter mais informações, consulte Possible Media Errors During Backup and Restore (SQL Server).

Estratégia de backup/restauração de documentos

Documente seus procedimentos de backup e restauração, e mantenha uma cópia da documentação no seu livro de processos.

Você também deve manter um manual de operações para cada banco de dados. Este manual de operações deve documentar a localização dos backups, nomes dos dispositivos de backup (se houver) e o tempo necessário para restaurar os backups de teste.

Risco de segurança de restaurar backups de fontes não confiáveis

Esta seção descreve o risco de segurança associado à restauração de backups de fontes não confiáveis para qualquer ambiente do SQL Server, incluindo local, Instância Gerenciada de SQL do Azure, SQL Server em VMs (Máquinas Virtuais) do Azure e qualquer outro ambiente.

Por que isso importa

Restaurar arquivos de backup do SQL (.bak) introduz um risco potencial se o backup se originar de uma fonte não confiável. O risco de segurança é exacerbado ainda mais quando um ambiente do SQL Server tem várias instâncias, pois amplifica a área de ameaça. Embora os backups que permanecem dentro de um limite confiável não representem nenhum problema de segurança, restaurar um backup mal-intencionado pode comprometer a segurança de todo o ambiente.

Um arquivo mal-intencionado .bak pode:

• Assuma toda a instância do SQL Server.
• Escalone privilégios e obtenha acesso não autorizado ao host ou à máquina virtual subjacente.

Esse ataque ocorre antes que qualquer validação de scripts ou verificações de segurança possa ser executada, o que o torna extremamente perigoso. Restaurar um backup não confiável é equivalente à execução de aplicativos não confiáveis em um servidor crítico ou máquina virtual e à introdução da execução arbitrária de código em seu ambiente.

Práticas recomendadas

Siga estas práticas recomendadas de segurança de backup para reduzir a ameaça aos ambientes do SQL Server:

• Trate a restauração de backups como uma operação de alto risco.
• Reduza a área de serviço de ameaça usando instâncias isoladas.
• Permitir apenas backups confiáveis: nunca restaure backups de fontes desconhecidas ou externas.
• Permitir apenas backups que estejam dentro de um limite confiável: assegure-se de que os backups se originem dentro do limite confiável.
• Não ignore os controles de segurança por conveniência.
• Habilite a auditoria no nível do servidor para capturar eventos de backup e restauração e reduzir a evasão de auditoria.

Monitore o progresso com o XEvent

Operações de backup e restauração podem levar muito tempo devido ao tamanho do banco de dados e à complexidade das operações envolvidas. Quando ocorrerem problemas com qualquer uma das operações, use o evento estendido backup_restore_progress_trace para acompanhar o progresso em tempo real. Para obter mais informações sobre eventos estendidos, consulte a visão geral de Eventos Estendidos.

-- Create the backup_restore_progress_trace extended event session
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file (SET filename = N'BackupRestoreTrace')
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = START;
GO

-- Stop the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = STOP;
GO

Saída de exemplo de Evento Estendido

Mais informações sobre tarefas de backup

• Criar um plano de manutenção
• Criar um trabalho do SQL Server Agent no SQL Server Management Studio
• Configurar agendamento para o trabalho do SQL Server Agent

Trabalhar com dispositivos de backup e mídia de backup

• Definir um dispositivo de backup lógico para um arquivo de disco (SQL Server)
• Definir um dispositivo de backup lógico para uma unidade de fita (SQL Server)
• Especificar um destino de backup de disco ou fita (SQL Server)
• Excluir um dispositivo de backup (SQL Server)
• Definir a data de expiração de um backup (SQL Server)
• Exibir o conteúdo de uma fita ou arquivo de backup (SQL Server)
• Exibir os arquivos de dados e de log em um conjunto de backup (SQL Server)
• Exibir as propriedades e o conteúdo de um dispositivo de backup lógico (SQL Server)
• Restaurar um backup de um dispositivo (SQL Server)

Criar backups

Para backups parciais ou somente cópia, use a instrução Transact-SQL BACKUP com a opção PARTIAL ou COPY_ONLY, respectivamente.

Usar SSMS

• Criar um backup de banco de dados completo
• Fazer backup de um log de transações
• Arquivos de backup e grupos de arquivos
• Criar um backup de banco de dados diferencial (SQL Server)

Usar T-SQL

• Use o Resource Governor para limitar o uso da CPU por meio de compressão de backup
• Fazer backup do log de transações quando o banco de dados estiver danificado (SQL Server)
• Habilitar ou desabilitar as somas de verificação de backup durante o backup ou a restauração (SQL Server)
• Especificar backup ou restauração para continuar ou parar após o erro

Restaurar backups de dados

Usar SSMS

• Restaurar um backup de banco de dados usando o SSMS
• Restaurar um banco de dados para um novo local (SQL Server)
• Restaurar um backup de banco de dados diferencial (SQL Server)
• Restaurar arquivos e grupos de arquivos (SQL Server)

Usar T-SQL

• Restaure um backup de banco de dados sob o modelo de recuperação simples
• Restaurar banco de dados para ponto de falha – recuperação completa
• Restaurar arquivos e grupos de arquivos sobre arquivos existentes (SQL Server)
• Restaurar arquivos para uma nova localização (SQL Server)
• Restaurar o banco de dados mestre

Restaurar logs de transações (modelo de recuperação completa)

Usar SSMS

• Restaurar um banco de dados para uma transação marcada (SQL Server Management Studio)
• Restaurar um backup de log de transações (SQL Server)
• Restaurar um banco de dados SQL Server até um ponto no tempo (modelo completo de recuperação)

Usar T-SQL

• Restaurar um banco de dados SQL Server até um ponto no tempo (modelo completo de recuperação)
• Para reinicializar uma operação de restauração interrompida
• Recuperar um banco de dados sem restaurar dados

Conteúdo relacionado

• Visão geral do backup (SQL Server)
• Visão geral de restauração e recuperação (SQL Server)
• BACKUP (Transact-SQL)
• RESTORE Instruções (Transact-SQL)
• Backup e restauração de bancos de dados do Analysis Services
• Faça backup e restaure catálogos e índices em texto completo
• Faça backup e restaure bancos de dados replicados
• O log de transações
• Modelos de recuperação (SQL Server)
• Conjuntos de mídia, famílias de mídia e conjuntos de backup (SQL Server)