Fazer backup e restaurar bases de dados do SQL Server

Aplica-se a:SQL Server

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

O componente de backup e restauro do SQL Server fornece uma salvaguarda essencial para dados críticos armazenados nas suas bases de dados SQL Server. Para minimizar o risco de perda catastrófica de dados, faça cópias de segurança regulares das suas bases de dados para preservar modificações nos seus dados. Uma estratégia bem planeada de backup e restauro ajuda a proteger as bases de dados contra a perda de dados causada por vários tipos de falhas. Teste a sua estratégia restaurando um conjunto de backups e depois recuperando a sua base de dados, para estar preparado para responder a um desastre.

Para além do armazenamento local, o SQL Server também suporta backup e restauração a partir do Armazenamento de Blobs do Azure. Para mais informações, consulte backup e restauro do SQL Server com Armazenamento de Blobs do Azure. Para ficheiros de base de dados armazenados no Armazenamento de Blobs do Azure, o SQL Server 2016 (13.x) oferece a opção de utilizar instantâneos do Azure para cópias de segurança quase instantâneas e restauros mais rápidos. Para obter mais informações, consulte Cópias de segurança de instantâneo de ficheiro para ficheiros de base 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.

Por que criar uma cópia de segurança?

• Fazer cópias de segurança das suas bases de dados do SQL Server, executar procedimentos de restauro de teste nos seus backups e armazenar cópias dos backups num local seguro e fora do local protege-o de uma possível perda catastrófica de dados. 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 utilizador, por exemplo, eliminar uma tabela por engano.

  • Falhas de hardware, por exemplo, uma unidade de disco danificada ou perda permanente de um servidor.

  • Catástrofes naturais. Ao usar o SQL Server Backup para Armazenamento de Blobs do Azure, pode criar um backup fora do local numa região diferente da sua localização local, para usar caso um desastre natural afete a sua localização 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

Estratégias de backup e restauração

Deve personalizar estratégias de backup e restauro para o seu ambiente e recursos disponíveis. Uma recuperação fiável requer uma estratégia de backup e restauração. Uma estratégia bem desenhada equilibra os requisitos empresariais para máxima disponibilidade e perda mínima de dados com 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 velocidade do hardware que necessitam, como testar backups e onde e como armazenar os suportes de backup (incluindo considerações de segurança). A parte de restauro define quem é responsável por realizar restaurações, como realizar restaurações para atingir os objetivos de disponibilidade da base de dados e perda mínima de dados, e como testar restaurações.

Uma estratégia eficaz de backup e restauro requer planeamento, implementação e testes cuidadosos. É obrigatório fazer testes. Não tem uma estratégia de backup até restaurar com sucesso backups em todas as combinações incluídas na sua estratégia de restauro e testar cada base de dados restaurada para verificar a consistência física. Considere vários fatores, incluindo:

• 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

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

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

• Os ficheiros de backup da base de dados devem ter a extensão .BAK .
• Os arquivos de backup de log devem ter a .TRN extensão.

Use armazenamento separado

Coloque as suas cópias de segurança da base de dados num local físico separado ou num dispositivo separado dos ficheiros da base de dados. Quando o disco físico que armazena as tuas bases de dados falha ou falha, a recuperação depende da tua capacidade de aceder ao disco separado ou dispositivo remoto que armazenou as cópias de segurança. Podes criar vários volumes ou partições lógicas a partir do mesmo disco físico. Revê cuidadosamente a partição do disco e os layouts lógicos dos volumes antes de escolheres 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 uma base de dados determina que tipos de cenários de backup e restauro a base de dados suporta, bem como o tamanho das cópias de segurança dos registos 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 de base de dados depende das necessidades do seu negócio. 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 efeito no tamanho do registo durante operações com registo em massa, continuando a permitir a recuperação dessas operações, utilize o modelo de recuperação com registo em massa. Para informações sobre o efeito dos modelos de recuperação na cópia de segurança e restauração, consulte a visão geral do Backup (SQL Server).

Projete sua estratégia de backup

Depois de selecionar um modelo de recuperação que cumpra os requisitos do seu negócio para uma base de dados específica, planeie 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 as aplicações precisam para aceder à base de dados?

  Se houver um período previsível de menor utilização, deve programar cópias de segurança completas da base 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:

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

  • No modelo completo de recuperação, 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 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?

  Para uma grande base de dados em que as alterações se concentram num subconjunto dos ficheiros ou grupos de ficheiros, cópias de segurança parciais ou completas de ficheiros podem ser úteis. Para mais informações, consulte Backups Parciais (SQL Server) e Backups Completos de Ficheiros (SQL Server).

• Quanto espaço em disco requer um backup completo de base de dados?

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

  Certifique-se de que tem um calendário de backup adequado que corresponda às necessidades da aplicação e aos requisitos empresariais. À medida que os backups envelhecem, o risco de perda de dados aumenta, a menos que tenha uma forma de regenerar todos os dados até ao ponto de falha. Antes de descartar cópias de segurança antigas devido às limitações de armazenamento, considere se precisa de 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 restauro, estima quanto espaço em disco um backup completo de base de dados consome. A operação de backup copia os dados no banco de dados para o arquivo de backup. A cópia de segurança contém apenas os dados reais da base de dados, não qualquer espaço não utilizado. Portanto, o backup geralmente é menor do que o próprio banco de dados. Para estimar o tamanho de uma cópia de segurança completa da base de dados, utilize o sp_spaceused procedimento armazenado do sistema. Para obter mais informações, consulte sp_spaceused.

Programar cópias de segurança

Uma operação de backup tem um efeito mínimo nas transações em execução, por isso pode fazer backups durante as operações normais. Você pode executar um backup do SQL Server com efeito mínimo nas cargas de trabalho de produção.

Depois de decidir que tipos de backups precisa e com que frequência realizar cada tipo, agende backups regulares como parte de um plano de manutenção de base 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. Teste minuciosamente a sua estratégia de backup para cada base de dados, restaurando uma cópia da base de dados num sistema de teste. Você deve testar a restauração de todos os tipos de backup que pretende usar. Depois de restaurar a cópia de segurança, executa DBCC CHECKDB na base de dados para confirmar que o suporte da cópia de segurança não está danificado.

Verificar a estabilidade e 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, o seu software ou solução de backup, e assim por diante). Para um exemplo, veja RESTORE Instruções - VERIFYONLY.

Utilize funcionalidades avançadas, como BACKUP CHECKSUM, para detetar problemas com o próprio suporte de cópia de segurança. Para mais informações, consulte Possíveis Erros de Media Durante Backup e Restauro (SQL Server).

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

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

Deve também manter um manual de operações para cada base de dados. Este manual de operações deve documentar a localização dos backups, nomes dos dispositivos de backup (se existirem) 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 restauro podem demorar muito tempo devido ao tamanho da base de dados e à complexidade das operações envolvidas. Quando surgirem problemas com qualquer 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 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 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 em disco ou fita (SQL Server)
• Excluir um dispositivo de backup (SQL Server)
• Definir a data de expiração em um backup (SQL Server)
• Exibir o conteúdo de uma fita ou arquivo de backup (SQL Server)
• Exibir os dados e arquivos 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 cópias de segurança

Para cópias de segurança parciais ou só de cópia, utilize a instrução Transact-SQL BACKUP com a opção PARTIAL ou COPY_ONLY, respetivamente.

Usar SSMS

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

Usar T-SQL

• Use o Resource Governor para limitar o uso da CPU através da compressão de backup
• Fazer backup do log de transações quando o banco de dados estiver danificado (SQL Server)
• Habilitar ou desabilitar 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 diferencial de banco de dados (SQL Server)
• Restaurar ficheiros e grupos de ficheiros (SQL Server)

Usar T-SQL

• Restaurar uma cópia de segurança da base de dados sob o modelo de recuperação simples
• Restaurar o banco de dados para o ponto de falha - recuperação total
• Restaurar ficheiros e grupos de ficheiros sobre ficheiros existentes (SQL Server)
• Restaurar ficheiros para uma nova localização (SQL Server)
• Restaurar a base 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 uma base de dados do SQL Server para um determinado momento (modelo de recuperação completo)

Usar T-SQL

• Restaurar uma base de dados do SQL Server para um determinado momento (modelo de recuperação completo)
• Reiniciar uma operação de restauração interrompida
• Recuperar uma base de dados sem restaurar dados

Conteúdo relacionado

• Visão geral do backup (SQL Server)
• Visão geral da restauração e recuperação (SQL Server)
• BACKUP (Transact-SQL)
• RESTORE Afirmações (Transact-SQL)
• Cópia de segurança e restauro de bases de dados do Analysis Services
• Fazer cópias de segurança e restaurar catálogos e índices em texto completo
• Fazer backup e restaurar bases de dados replicadas
• 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)