Partilhar via

Backup e restauração de bancos de dados do SQL Server

Aplica-se a:SQL Server

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

Este artigo apresenta os backups do SQL Server. Para obter etapas específicas para fazer backup de bancos de dados do SQL Server, consulte Criando backups.

O componente de backup e restauração do SQL Server fornece uma salvaguarda essencial para proteger dados críticos armazenados em seus bancos de dados do SQL Server. Para minimizar o risco de perda catastrófica de dados, você precisa fazer backup de seus bancos de dados para preservar modificações em seus dados regularmente. Uma estratégia de backup e restauração bem planejada ajuda a proteger os bancos de dados contra a perda de dados causada por uma variedade de falhas. Teste sua estratégia restaurando um conjunto de backups e, em seguida, recuperando seu banco de dados para prepará-lo para responder de forma eficaz a um desastre.

Além do armazenamento local para armazenar os backups, o SQL Server também dá suporte ao backup e à restauração 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 Microsoft 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âneo de arquivo para arquivos de banco de dados no Azure. O Azure também oferece 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, que oferece suporte a grupos de disponibilidade Always On, retenção de longo prazo, recuperação point-in-time e gerenciamento e monitoramento centralizados. Para obter mais informações, consulte Sobre o backup do SQL Server em VMs do Azure.

Porquê fazer uma cópia de segurança?

  • Fazer backup de seus bancos de dados do SQL Server, executar procedimentos de restauração de teste em seus backups e armazenar cópias de backups em um local externo seguro protege você contra perda de dados potencialmente catastrófica. O 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.
    • Erros do usuário, por exemplo, soltar uma tabela por engano.
    • Falhas de hardware, por exemplo, uma unidade de disco danificada ou perda permanente de um servidor.
    • Catástrofes naturais. Usando o Backup do SQL Server para o Armazenamento de Blobs do Azure, você pode criar um backup externo em uma região diferente do local local, para usar no caso de um desastre natural que afete seu local local.

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

Glossário de termos de backup

backup [verbo]
O processo de criação de um backup [substantivo] copiando registros de dados de um banco de dados do SQL Server ou registros de log de seu log de transações.

backup [substantivo]
Uma cópia dos dados que você pode usar para restaurar e recuperar os dados após uma falha. Os backups de um banco de dados também podem ser usados para restaurar uma cópia do banco de dados para um novo local.

dispositivo de backup
Um dispositivo de disco ou fita no qual os backups do SQL Server são gravados e a partir do qual podem ser restaurados. Os backups do SQL Server também podem ser gravados em um Armazenamento de Blobs do Azure e o formato de URL é usado para especificar o destino e o nome do arquivo de backup. Para obter mais informações, consulte Backup e restauração do SQL Server com o Armazenamento de Blobs do Microsoft Azure.

mídia de backup
Uma ou mais fitas ou arquivos de disco nos quais um ou mais backups foram gravados.

backup de dados
Um backup de dados em um banco de dados completo (um backup de banco de dados), um banco de dados parcial (um backup parcial) ou um conjunto de arquivos de dados ou grupos de arquivos (um backup de arquivo).

backup de banco de dados
Um backup de um banco de dados. Os backups completos do banco de dados representam todo o banco de dados no momento em que o backup foi concluído. Os backups diferenciais de banco de dados contêm apenas alterações feitas no banco de dados desde o backup completo mais recente do banco de dados.

backup diferencial
Um backup de dados baseado no backup completo mais recente de um banco de dados completo ou parcial ou de um conjunto de arquivos de dados ou grupos de arquivos (a base diferencial) e que contém apenas os dados que foram alterados desde essa base.

backup completo
Um backup de dados que contém todos os dados em um banco de dados específico ou conjunto de grupos de arquivos ou arquivos, e também log suficiente para permitir a recuperação desses dados.

backup de log
Um backup de logs de transações que inclui todos os registros de log que não foram copiados em um backup de log anterior (modelo de recuperação completa).

recover
Para retornar um banco de dados a um estado estável e consistente.

recuperação
Uma fase de inicialização do banco de dados ou de uma restauração com recuperação que coloca o banco de dados em um estado consistente de transação.

modelo de recuperação
Uma propriedade de banco de dados que controla a manutenção do log de transações em um banco de dados. Existem três modelos de recuperação: simples, completo e registrado em massa. O modelo de recuperação de um banco de dados determina seus requisitos de backup e restauração.

restaurar
Um processo multifásico que copia todos os dados e páginas de log de um backup especificado do SQL Server para um banco de dados especificado e, em seguida, avança todas as transações registradas no backup aplicando alterações registradas para antecipar os dados no tempo.

Estratégias de backup e restauração

O backup e a restauração de dados devem ser personalizados para um ambiente específico e devem funcionar com os recursos disponíveis. Portanto, um uso confiável de backup e restauração para recuperação requer uma estratégia de backup e restauração. Uma estratégia de backup e restauração bem projetada equilibra os requisitos de negócios para máxima disponibilidade e perda mínima de dados, considerando 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 da estratégia define o tipo e a frequência dos backups, a natureza e a velocidade do hardware necessário, como os backups devem ser testados e onde e como a mídia de backup deve ser armazenada (incluindo considerações de segurança). A parte de restauração da estratégia define quem é responsável por executar restaurações, como as restaurações devem ser executadas para atender às suas metas de disponibilidade do banco de dados e minimização da perda de dados e como as restaurações são testadas.

Projetar uma estratégia eficaz de backup e restauração requer planejamento, implementação e testes cuidadosos. O teste é necessário: você não tem uma estratégia de backup até que tenha restaurado com êxito os backups em todas as combinações incluídas na estratégia de restauração e testado a consistência física do banco de dados restaurado. Você deve considerar uma variedade de fatores. Estes são, entre outros:

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

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

  • Restrições de 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 boas práticas

As contas que executam operações de backup ou restauração não devem receber mais privilégios do que o necessário. Analise o backup e a restauração para obter detalhes de permissão específicos. Recomenda-se que os backups sejam criptografados e, se possível, compactados.

Para garantir a segurança, os arquivos de backup devem ter extensões que sigam as convenções adequadas:

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

Usar armazenamento separado

Importante

Certifique-se de colocar os backups do banco de dados em um local físico ou dispositivo separado dos arquivos de banco de dados. Quando a unidade física que armazena os bancos de dados apresenta falhas ou falha, a capacidade de recuperação depende da capacidade de acessar a unidade separada ou o dispositivo remoto que armazenou os backups para executar uma restauração. Lembre-se de que você pode criar vários volumes lógicos ou partições a partir de uma mesma unidade de disco física. Estude cuidadosamente a partição de disco e os layouts de volume lógico antes de escolher um local de armazenamento para os backups.

Escolha o modelo de recuperação apropriado

As operações de backup e restauração ocorrem no contexto de um modelo de recuperação. Um modelo de recuperação é uma propriedade de banco de dados que controla como o log de transações é gerenciado. Assim, o modelo de recuperação de um banco de dados determina quais tipos de backups e cenários de restauração são suportados para o banco de dados e qual seria o tamanho dos backups de log de transações. Normalmente, um banco de dados usa o modelo de recuperação simples ou o modelo de recuperação completa. Você pode aumentar 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 como eles afetam o gerenciamento do log de transações, consulte o log de transações.

A melhor escolha de modelo de recuperação para o banco de dados depende dos seus requisitos de negócios. Para evitar o gerenciamento de logs 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 das despesas gerais administrativas, use o modelo de recuperação completa. Para minimizar o impacto no tamanho do log durante operações bulk-logged e, ao mesmo tempo, permitir a capacidade de recuperação dessas operações, use o modelo de recuperação bulk-logged. Para obter informações sobre o efeito dos modelos de recuperação no backup e na restauração, consulte Visão geral do backup.

Projete sua estratégia de backup

Depois de selecionar um modelo de recuperação que atenda aos seus requisitos de negócios para um banco de dados específico, você precisa planejar e implementar uma estratégia de backup correspondente. A estratégia de backup ideal depende de uma variedade de fatores, dos quais os seguintes são especialmente significativos:

  • Quantas horas por dia as aplicações têm para aceder à base de dados?

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

  • Com que frequência é provável que ocorram alterações e atualizações?

    Se as alterações forem frequentes, considere o seguinte:

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

    • No modelo de recuperação completa, você deve agendar backups de log frequentes. O agendamento de backups diferenciais entre backups completos pode reduzir o tempo de restauração, reduzindo o número de backups de log que você precisa restaurar depois de restaurar os dados.

  • É provável que ocorram alterações apenas numa pequena parte da base de dados ou numa grande parte da base de dados?

    Para um grande banco de dados no qual as alterações estão concentradas em uma parte dos arquivos ou grupos de arquivos, backups parciais e/ou backups completos de arquivos podem ser úteis. Para obter mais informações, consulte Backups parciais (SQL Server) e Backups completos de arquivos (SQL Server).

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

  • Até que ponto no passado sua empresa precisa manter backups?

    Certifique-se de ter um cronograma de backup adequado estabelecido de acordo com as necessidades do aplicativo e os requisitos de negócios. À medida que os backups envelhecem, o risco de perda de dados é maior, a menos que você tenha uma maneira de regenerar todos os dados até o ponto de falha. Antes de optar por descartar backups antigos devido a limitações de recursos de armazenamento, considere se a capacidade de recuperação é necessária no passado.

Estimar o tamanho de um backup de banco de dados completo

Antes de implementar uma estratégia de backup e restauração, você deve estimar quanto espaço em disco um backup de banco de dados completo usará. 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 no banco de dados e não qualquer espaço não utilizado. Portanto, o backup geralmente é menor do que o próprio banco de dados. Você pode estimar o tamanho de um backup de banco de dados completo usando o procedimento armazenado do sp_spaceused sistema. Para obter mais informações, consulte sp_spaceused (Transact-SQL).

Agendar backups

A execução de uma operação de backup tem um efeito mínimo nas transações em execução; portanto, você pode executar operações de backup durante operações regulares. Você pode executar um backup do SQL Server com efeito mínimo nas cargas de trabalho de produção.

Para obter informações sobre restrições de simultaneidade durante o backup, consulte Visão geral do backup (SQL Server).

Depois de decidir quais tipos de backups são necessários e com que frequência você precisa executar cada tipo, recomendamos que você agende backups regulares como parte de um plano de manutenção do banco de dados para o banco de dados. Para obter informações sobre planos de manutenção e como criá-los para backups de banco de dados e backups de log, consulte Usar o Assistente de Plano de Manutenção.

Teste seus backups

Você não tem uma estratégia de restauração até testar seus backups. É muito importante testar completamente sua estratégia de backup para cada um dos bancos de dados restaurando uma cópia do banco de dados em um sistema de teste. Você deve testar a restauração de todos os tipos de backup que pretende usar. Também recomendamos que, depois de restaurar o backup, você execute verificações de consistência do banco de dados via DBCC CHECKDB do banco de dados para validar que a mídia de backup não foi danificada.

Verificar a estabilidade e consistência da mídia

Use as opções de verificação fornecidas pelos utilitários de backup (BACKUP comando T-SQL, Planos de Manutenção do SQL Server, seu software ou solução de backup, etc.). Para obter um exemplo, consulte instruções RESTORE - VERIFYONLY.

Use recursos avançados, como BACKUP CHECKSUM detetar problemas com a própria mídia de backup. Para obter mais informações, consulte Possíveis erros de mídia durante o backup e a restauração (SQL Server)

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

Recomendamos que você documente seus procedimentos de backup e restauração e mantenha uma cópia da documentação em seu livro de execução. Também recomendamos que você mantenha um manual de operações para cada banco de dados. Este manual de operações deve documentar o local dos backups, os nomes dos dispositivos de backup (se houver) e o tempo necessário para restaurar os backups de teste.

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

Esta secção descreve o risco de segurança associado à restauração de backups de fontes não confiáveis para qualquer ambiente SQL Server, incluindo on-premises, Azure SQL Managed Instance, SQL Server em Máquinas Virtuais Azure (VMs) e qualquer outro ambiente.

Por que motivo isto é importante

Restaurar ficheiros de backup SQL (.bak) introduz um risco potencial se o backup tiver origem numa fonte não confiável. O risco de segurança agrava-se ainda mais quando um ambiente SQL Server tem múltiplas instâncias, pois amplifica a área de ameaça. Embora backups que permaneçam dentro de um limite de confiança não representem qualquer problema de segurança, restaurar um backup malicioso pode comprometer a segurança de todo o ambiente.

Um ficheiro malicioso .bak pode:

  • Assumir o controlo total da instância do SQL Server.
  • Escalar privilégios e obter acesso não autorizado ao host ou máquina virtual subjacente.

Este ataque ocorre antes de qualquer script de validação ou verificação de segurança poder ser executado, o que o torna extremamente perigoso. Restaurar um backup não confiável equivale a executar aplicações não confiáveis num servidor crítico ou máquina virtual, e introduzir a execução arbitrária de código no seu ambiente.

Melhores práticas

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

  • Trate a restauração de cópias de segurança como uma operação de alto risco.
  • Reduza a área de serviço de ameaça utilizando instâncias isoladas.
  • Permita apenas backups de confiança: nunca restaure backups de fontes desconhecidas ou externas.
  • Só permitam backups que tenham permanecido dentro de um limite de confiança: certifique-se de que os backups têm origem dentro do limite de confiança.
  • Não contorne os controlos de segurança por conveniência.
  • Permitir auditoria ao nível do servidor para capturar backups e restaurar eventos e mitigar evasão de auditoria.

Monitore o progresso com o XEvent

As operações de backup e restauração podem levar um tempo considerável devido ao tamanho de um banco de dados e à complexidade das operações envolvidas. Quando surgirem problemas com qualquer uma das operações, você poderá usar o evento estendido para monitorar o backup_restore_progress_trace progresso ao vivo. Para obter mais informações sobre eventos estendidos, consulte Visão geral de eventos estendidos.

Advertência

Usar o backup_restore_progress_trace evento estendido pode causar um problema de desempenho e consumir uma quantidade significativa de espaço em disco. Use por curtos períodos de tempo, tenha cuidado e teste cuidadosamente antes de implementar na produção.

-- 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 amostra do evento estendido

Captura de tela de um exemplo de saída xevent de backup. Captura de tela de um exemplo de saída xevent de backup, continuação.

Mais sobre tarefas de backup

Trabalhar com dispositivos de backup e mídia de backup

Criar backups

Observação

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

Usar o SSMS

Usar T-SQL

Restaurar backups de dados

Usar o SSMS

Usar T-SQL

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

Usar o SSMS

Usando T-SQL

Conteúdo relacionado

  • Last updated on