Fazer backup e restaurar 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, 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, confira Criar backups.

O componente Backup e restauração do SQL Server fornece uma proteção essencial para proteger dados críticos armazenados em seus bancos de dados do SQL Server. Para minimizar o risco de perda de dados catastrófica, você precisa fazer backup de seus bancos de dados para preservar as modificações nos seus dados regularmente. Uma estratégia de backup e restauração bem planejada ajuda a proteger os bancos de dados contra 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 se preparar para responder com eficiência 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, confira 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 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, confira Backup do Azure para o SQL Server em VM do Azure.

Por que fazer backup?

• O backup dos bancos de dados do SQL Server, a execução de procedimentos de restauração de teste nos backups e o armazenamento de cópias de backups em um local externo seguro evitam a perda de dados potencialmente catastrófica. 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. Usando o Backup do SQL Server para o Armazenamento de Blobs do Azure, você pode criar um backup externo em uma região diferente de sua localização local, para usar no caso de um desastre natural que afete a 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

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

backup [substantivo]
Uma cópia dos dados do que pode ser usada 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 em um novo local.

dispositivo de backup
Um disco ou dispositivo de fita no qual os backups do SQL Server serão gravados e nos quais eles poderão ser restaurados. Os backups do SQL Server também podem ser gravados no Armazenamento de Blobs do Azure. O formato de URL é usado para especificar o destino e o nome do arquivo de backup. Para obter mais informações, confira 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 de banco de dados representam todo o banco de dados no momento em que o backup é concluído. Os backups de banco de dados diferenciais contêm somente alterações feitas no banco de dados desde seu backup completo de banco de dados mais recente.

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

backup completo
Um backup de dados que contém todos os dados em um banco de dados ou em um conjunto de grupos de arquivos ou arquivos, além de 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 dos quais não foi feito backup 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 de banco de dados ou de restauração com recuperação que coloca o banco de dados em um estado de transação consistente.

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 bulk-logged. O modelo de recuperação de banco de dados determina seus requisitos de backup e de restauração.

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

Estratégias de backup e restauração

O backup e a restauração dos dados devem ser personalizados em 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 de dados e mínima perda de dados, levando em consideração 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 velocidade do hardware exigido para eles, como os backups serão testados e em que local 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 realizar restaurações, como as restaurações devem ser executadas para atender às suas metas de disponibilidade de banco de dados e minimizar a perda de dados e como as restaurações são testadas.

O design de uma estratégia de backup e restauração eficaz requer planejamento, implementação e teste cuidadosos. Testar é 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 sua estratégia de restauração e tenha testado a consistência física do banco de dados restaurado. Você deve considerar uma variedade de fatores. Estão incluídos:

• As metas de sua organização para os bancos de dados de produção, especialmente os requisitos para disponibilidade e proteção contra perda ou danificação de dados.

• 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 de recursos, como hardware, pessoal, espaço para armazenagem de mídia de backup, a segurança física da mídia armazenada, e assim por diante.

Recomendações de melhores 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. Veja detalhes de permissões específicas em backup e restauração. É recomendável que os backups sejam criptografados e, se possível, compactados.

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

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

Usar armazenamento separado

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. Assim, o modelo de recuperação de um banco de dados determina quais tipos de backup e cenários de restauração são compatíveis com o banco de dados e qual será o tamanho dos backups de log de transações. Em geral, um banco de dados usa o modelo de recuperação simples ou o modelo de recuperação completa. O modelo de recuperação completa pode ser aumentado 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ção (SQL Server)

A melhor escolha do modelo de recuperação para o banco de dados depende de seus requisitos empresariais. 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 exposição à perda de trabalho, às custas de uma sobrecarga administrativa, use o modelo de recuperação completa. Para minimizar o impacto sobre o tamanho do log durante operações bulk-logged e, ao mesmo tempo, permitir a 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 (SQL Server).

Planejar a estratégia de backup

Depois de selecionar um modelo de recuperação que satisfaça seus requisitos empresariais para um banco de dados específico, você precisa planejar e implementar uma estratégia de backup correspondente. A melhor estratégia de backup depende de uma série de fatores, dos quais os seguintes são especialmente significativos:

• Quantas horas ao dia os aplicativos precisam acessar o banco de dados?

  Se houver um período de pouca atividade previsível, recomendamos que você agende backups de banco de dados completos para aquele período.

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

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

  • No modelo de recuperação simples, agende backups diferenciais entre os backups de banco de dados completos. Um backup diferencial captura só 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 a serem restaurados após a restauração dos dados.

• As alterações ocorrem geralmente em uma pequena parte do banco de dados ou em uma grande parte do banco de dados?

  Para um banco de dados grande no qual mudanças estão concentradas em uma parte dos arquivos ou grupos de arquivos, backups parciais e backups de arquivo 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 é necessário para um backup completo de banco de dados?

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

  Verifique se você tem uma agenda de backup adequado estabelecida de acordo com as necessidades dos aplicativos e dos requisitos de negócios. Conforme 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 os backups antigos devido a limitações de recursos de armazenamento, considere se é necessária uma capacidade de recuperação distante no passado

Estimar o tamanho de um backup de banco de dados completo

Antes de implementar uma estratégia de backup e restauração, calcule 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 só 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 sistema sp_spaceused . Para obter mais informações, consulte sp_spaceused (Transact-SQL).

Agendar backups

A execução do backup tem um efeito mínimo sobre as transações em andamento; portanto, as operações de backup podem ser realizadas durante a operação regular. Você pode realizar um backup do SQL Server com efeito mínimo sobre as 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 os tipos de backups necessários e a frequência de execução de cada tipo, recomendamos que você agende backups regulares como parte de um plano de manutenção de banco de dados para o 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.

Não existirá uma estratégia de restauração até que você tenha testado seus backups. É muito importante testar sua estratégia de backup completamente para cada um dos bancos de dados, restaurando uma cópia do banco de dados em um sistema de teste. É necessário testar a restauração de cada tipo de backup que você pretende usar. Também é recomendável que, depois de restaurar o backup, você execute verificações de consistência do banco de dados usando DBCC CHECKDB do banco de dados para validar se a mídia de backup não foi danificada.

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

Use as opções de verificação fornecidas pelos utilitários de backup (comando T-SQL de BACKUP, Planos de Manutenção do SQL Server, seu software ou solução de backup etc). Para um exemplo, confira [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md) Use recursos avançados como BACKUP CHECKSUM para detectar problemas com a mídia de backup em si. Para obter mais informações, confira os Possíveis erros de mídia durante o backup e a restauração (SQL Server)

Documentar estratégia de backup/restauração

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

Monitorar o progresso com xEvent

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 ocorrem problemas com a operação, você pode usar o evento estendido backup_restore_progress_trace para monitorar o progresso em tempo real. Para obter mais informações sobre eventos estendidos, veja eventos estendidos.

-- Create the backup_restore_progress_trace extended event esssion
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  

Exemplo de saída do evento estendido

Mais informações sobre tarefas de backup

• Criar um Plano de Manutenção

• Criar um trabalho

• Agendar um trabalho

Trabalhando com dispositivos e mídias 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 disco ou fita como destino de backup (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 um arquivo ou fita 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)

Criando backups

Usando SSMS

• Criar um backup completo de banco de dados (SQL Server)

• Fazer backup de um log de transações (SQL Server)

• Fazer backup de arquivos e de grupos de arquivos (SQL Server)

• Criar um backup diferencial de banco de dados (SQL Server)

Usando o T-SQL

• Usar o Resource Governor para limitar o uso de CPU por meio da compactação de backup (Transact-SQL)

• Fazer backup do log de transações quando o banco de dados está danificado (SQL Server)

• Habilitar ou desabilitar as somas de verificação de backup durante o backup ou a restauração (SQL Server)

• Especificar se uma operação de backup ou restauração é interrompida ou continua depois de encontrar um erro (SQL Server)

Restaurar backups de dados

Usando SSMS

• Restore a Database Backup Using SSMS

• Restaurar um banco de dados em um novo local (SQL Server)

• Restaurar um backup de banco de dados diferencial (SQL Server)

• Restaurar arquivos e grupos de arquivos (SQL Server)

Usando o T-SQL

• Restaurar um backup de banco de dados no modelo de recuperação simples (Transact-SQL)

• Restaurar um banco de dados até o ponto de falha no modelo de recuperação completa (Transact-SQL)

• Restaurar arquivos e grupos de arquivos sobre arquivos existentes (SQL Server)

• Restaurar arquivos para um novo local (SQL Server)

• Restaurar o banco de dados mestre (Transact-SQL)

Restaurar logs de transações (Modelo de Recuperação Completa)

Usando 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 do SQL Server até um ponto determinado (modelo de recuperação completa)

Usando o T-SQL

• Restaurar um banco de dados do SQL Server até um ponto determinado (modelo de recuperação completa)

• Reiniciar uma operação de restauração interrompida (Transact-SQL)

• Recuperar um banco de dados sem restaurar dados (Transact-SQL)

Mais informações e recursos

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