Solucionar problemas de operações de backup e restauração do SQL Server

Este artigo fornece soluções para problemas comuns que você pode enfrentar durante as operações de backup e restauração do Microsoft SQL Server e fornece referências para obter mais informações sobre essas operações.

Versão original do produto: SQL Server
Número original do KB: 224071

As operações de backup e restauração levam muito tempo

As operações de backup e restauração têm uso intensivo de E/S. A taxa de transferência de backup/restauração depende de quão bem o subsistema de E/S subjacente é otimizado para lidar com o volume de E/S. Se você suspeitar que as operações de backup foram interrompidas ou estão demorando muito para serem concluídas, você pode usar um ou mais dos seguintes métodos para estimar o tempo de conclusão ou para acompanhar o progresso de uma operação de backup ou restauração:

• O log de erros do SQL Server contém informações sobre operações anteriores de backup e restauração. Você pode usar esses detalhes para estimar o tempo necessário para fazer backup e restaurar o banco de dados em seu estado atual. Veja a seguir um exemplo de saída do log de erros:

  RESTORE DATABASE successfully processed 315 pages in 0.372 seconds (6.604 MB/sec)
  

• No SQL Server 2016 e versões posteriores, você pode usar o XEvent backup_restore_progress_trace para acompanhar o progresso das operações de backup e restauração.

• Você pode usar a percent_complete coluna de sys.dm_exec_requests para acompanhar o progresso das operações de backup e restauração em andamento.

• Você pode medir as informações de taxa de transferência de backup e restauração usando os contadores do Device throughput Bytes/sec monitor de desempenho e Backup/Restore throughput/sec . Para obter mais informações, consulte SQL Server, objeto de dispositivo de backup.

• Use o script estimate_backup_restore para obter uma estimativa dos tempos de backup.

• Consulte Como funciona: O que a restauração / backup está fazendo?. Esta postagem do blog fornece informações sobre o estágio atual das operações de backup ou restauração.

Alguns pontos a verificar

1. Verifique se você está enfrentando algum dos problemas conhecidos listados na tabela a seguir. Considere se você deve implementar as alterações ou aplicar as correções e as práticas recomendadas discutidas nos artigos correspondentes.

   Base de conhecimento ou links de Livros Online	Explicação e ações recomendadas
   Otimizando o desempenho de backup e restauração no SQL Server	O tópico Manuais Online aborda várias práticas recomendadas que você pode usar para melhorar o desempenho das operações de backup/restauração. Por exemplo, você pode atribuir o privilégio especial à conta do Windows que está executando o SQL Server para habilitar a SE_MANAGE_VOLUME_NAME inicialização instantânea de arquivos de dados. Isso pode produzir ganhos significativos de desempenho.
   2920151 hotfixes e atualizações recomendados para clusters de failover baseados no Windows Server 2012 R2 2822241 Pacote cumulativo de atualizações do Windows 8 e do Windows Server 2012: abril de 2013	Os pacotes cumulativos atuais do sistema podem incluir correções para problemas conhecidos no nível do sistema que podem prejudicar o desempenho de programas como o SQL Server. A instalação dessas atualizações pode ajudar a evitar esses problemas.
   2878182 CORREÇÃO: Os processos do modo de usuário em um aplicativo não respondem em servidores que executam o Windows Server 2012	As operações de backup são intensivas em E/S e podem ser afetadas por esse bug. Aplique essa correção para ajudar a evitar esses problemas.
   Configurar o software antivírus para funcionar com o SQL Server	O software antivírus pode bloquear .bak arquivos. Isso pode afetar o desempenho das operações de backup e restauração. Siga as orientações deste artigo para excluir arquivos de backup das verificações de vírus.
   2820470 Mensagem de erro atrasada ao tentar acessar uma pasta compartilhada que não existe mais no Windows	Discute um problema que ocorre quando você tenta acessar uma pasta compartilhada que não existe mais no Windows 2012 e versões posteriores.
   967351 Um arquivo muito fragmentado em um volume NTFS pode não crescer além de um determinado tamanho	Discute um problema que ocorre quando um sistema de arquivos NTFS é muito fragmentado.
   304101 programa de backup não é bem-sucedido quando você faz backup de um grande volume do sistema
   2455009 CORREÇÃO: Desempenho lento ao recuperar um banco de dados se houver muitos VLFs dentro do log de transações no SQL Server 2005, no SQL Server 2008 ou no SQL Server 2008 R2	A presença de muitos arquivos de log virtuais pode afetar o tempo necessário para restaurar um banco de dados. Isso é especialmente verdadeiro durante a fase de recuperação da operação de restauração. Para obter informações sobre outros possíveis problemas que podem ser causados pela presença de muitos VLFs, consulte As operações de banco de dados demoram muito para serem concluídas ou disparam erros quando o log de transações tem vários arquivos de log virtuais.
   Uma operação de backup ou restauração em um local de rede é lenta	Isole o problema na rede tentando copiar um arquivo de tamanho semelhante para o local de rede do servidor que está executando o SQL Server. Verifique o desempenho.

2. Verifique se há mensagens de erro no log de erros do SQL Server e no log de eventos do Windows para obter mais dicas sobre a causa do problema.

3. Se você estiver usando software de terceiros ou planos de manutenção de banco de dados para fazer backups simultâneos, considere se deve alterar os agendamentos para minimizar a contenção na unidade na qual os backups estão sendo gravados.

4. Trabalhe com o administrador do Windows para verificar se há atualizações de firmware para o seu hardware.

Problemas que afetam a restauração do banco de dados entre diferentes versões do SQL Server

Um backup do SQL Server não pode ser restaurado para uma versão anterior do SQL Server do que a versão na qual o backup foi criado. Por exemplo, você não pode restaurar um backup feito em uma instância do SQL Server 2019 para uma instância do SQL Server 2017. Caso contrário, a seguinte mensagem de erro será exibida:

Erro 3169: O backup do banco de dados foi feito em um servidor que executa a versão %ls. Essa versão é incompatível com esse servidor que está executando a versão %ls. Restaure o banco de dados em um servidor que dê suporte ao backup ou use um backup compatível com esse servidor.

Use o método a seguir para copiar um banco de dados hospedado em uma versão posterior do SQL Server para uma versão anterior do SQL Server.

Observação

O procedimento a seguir pressupõe que você tenha duas instâncias do SQL Server denominadas SQL_A (versão superior) e SQL_B (versão inferior).

1. Baixe e instale a última versão do SSMS (SQL Server Management Studio) ou ambas (SQL_A e SQL_B).
2. No SQL_A, siga estas etapas:
   1. Clique com o botão direito do mouse em <YourDatabase>Tasks>Generate Scripts e selecione a opção para criar scripts de todo o banco de dados e de todos os objetos de banco de dados.
   2. Na tela Definir Opções de Script, selecione Avançado e, em seguida, selecione a versão do SQL_B em Script Geral>para Versão do SQL Server. Além disso, selecione a opção que funciona melhor para você salvar os scripts gerados. Em seguida, continue o assistente.
   3. Use o utilitário de programa de cópia em massa (bcp) para copiar dados de tabelas diferentes.
3. No SQL_B, siga estas etapas:
   1. Use os scripts que foram gerados no servidor SQL_A para criar o esquema de banco de dados.
   2. Em cada uma das tabelas, desabilite as restrições e os gatilhos de chave estrangeira. Se a tabela tiver colunas de identidade, habilite a inserção de identidade.
   3. Use bcp para importar os dados exportados na etapa anterior para as tabelas correspondentes.
   4. Após a conclusão da importação de dados, habilite as restrições e gatilhos de chave estrangeira e desabilite a inserção de identidade para cada uma das tabelas afetadas na etapa c.

Esse procedimento normalmente funciona bem para bancos de dados de pequeno a médio porte. Para bancos de dados maiores, problemas de memória insuficiente podem ocorrer no SSMS e em outras ferramentas. Você deve considerar o uso do SQL Server Integration Services (SSIS), replicação ou outras opções para criar uma cópia de um banco de dados de uma versão posterior para uma versão anterior do SQL Server.

Para obter mais informações sobre como gerar scripts para o banco de dados, confira Script de um banco de dados usando a opção Gerar Scripts.

Problemas de trabalho de backup de ambientes Always On

Se você encontrar problemas que afetem tarefas de backup ou planos de manutenção em ambientes Always On, observe o seguinte:

• Por padrão, a preferência de backup automático é definida como Preferir secundário. Isso especifica que os backups devem ocorrer em uma réplica secundária, exceto se a réplica primária for a única réplica online. Você não pode fazer backups diferenciais do banco de dados usando essa configuração. Para alterar essa configuração, use o SSMS em sua réplica primária atual e navegue até a página Preferências de Backup em Propriedades do grupo de disponibilidade.
• Se você estiver usando um plano de manutenção ou trabalhos agendados para gerar backups de seus bancos de dados, certifique-se de criar os trabalhos para cada banco de dados de disponibilidade em cada instância de servidor que hospeda uma réplica de disponibilidade para o grupo de disponibilidade.

Para obter mais informações sobre backups em um ambiente Always On, consulte os seguintes tópicos:

• Configurar backups em réplicas secundárias de um grupo de disponibilidade Always On
• Descarregar backups com suporte nas réplicas secundárias de um grupo de disponibilidade

Erros relacionados à mídia ao restaurar um banco de dados de um backup

Se você receber mensagens de erro que indicam um problema de arquivo, isso é sintomático de um arquivo de backup corrompido. A seguir estão alguns exemplos de erros que você pode obter se um conjunto de backup estiver corrompido:

• 3241: A família de mídia no dispositivo '%ls' está formada incorretamente. O SQL Server não pode processar essa família de mídia.

• 3242: O arquivo no dispositivo '%ls' não é um conjunto de backup válido do Microsoft Tape Format.

• 3243: A família de mídia no dispositivo '%ls' foi criada usando a versão %d.%d do formato de fita Microsoft. O SQL Server dá suporte à versão %d.%d.

Observação

Você pode usar a instrução Restore Header para verificar seus backups.

Esses problemas podem ocorrer devido a problemas que afetam o hardware subjacente (discos rígidos, armazenamento de rede e assim por diante) ou que estão relacionados a um vírus ou malware. Examine os logs de eventos do sistema Windows e os logs de hardware em busca de erros relatados e tome as medidas apropriadas (por exemplo, atualizar o firmware ou corrigir problemas de rede).

Para evitar esses erros, ative a opção Backup CHECKSUM ao executar um backup para evitar o backup de um banco de dados corrompido. Para obter mais informações, consulte Possíveis erros de mídia durante o backup e a restauração (SQL Server).

Você também pode habilitar o sinalizador de rastreamento 3023 para habilitar uma soma de verificação ao executar backups usando ferramentas de backup. Para obter mais informações, consulte Como habilitar a opção CHECKSUM se os utilitários de backup não exporem a opção.

Para corrigir esses problemas, você precisa localizar outro arquivo de backup utilizável ou criar um novo conjunto de backup. A Microsoft não oferece nenhuma solução que possa ajudar a recuperar dados de um conjunto de backup corrompido.

Observação

Se um arquivo de backup for restaurado com êxito em um servidor, mas não em outro, tente maneiras diferentes de copiar o arquivo entre os servidores. Por exemplo, experimente o robocopy, em vez de uma operação de cópia regular.

Os backups falham devido a problemas de permissão

Quando você tenta executar operações de backup de banco de dados, ocorre um dos seguintes erros.

• Cenário 1: quando você executa um backup do SQL Server Management Studio, o backup falha e retorna a seguinte mensagem de erro:

  Falha no backup para o nome do servidor do servidor<>. (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError: Não é possível abrir o '<nome> do dispositivo' do dispositivo de backup. Erro do sistema operacional 5 (Acesso negado.). (Microsoft.SqlServer.Smo)

• Cenário 2: os backups agendados falham e geram uma mensagem de erro registrada no histórico de trabalho do trabalho com falha e semelhante à seguinte:

  Executed as user: <Owner of the job>. ....2 for 64-bit  Copyright (C) 2019 Microsoft. All rights reserved.    
  Started:  5:49:14 PM  Progress: 2021-08-16 17:49:15.47    
  Source: {GUID}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  
  Error: 2021-08-16 17:49:15.74     
  Code: 0xC002F210     
  Source: Back Up Database (Full) Execute SQL Task     
  Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'C:\backups\D..." failed with the following error: "xp_create_subdir() returned error 5, 'Access is denied.'". 
  Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
  

Qualquer um desses cenários poderá ocorrer se a conta de serviço do SQL Server não tiver permissões de leitura e gravação para a pasta na qual os backups estão sendo gravados. As instruções de backup podem ser executadas como parte de uma etapa de trabalho ou manualmente no SQL Server Management Studio. Em ambos os casos, eles sempre são executados no contexto da conta de inicialização do Serviço SQL Server. Portanto, se a conta de serviço não tiver os privilégios necessários, você receberá as mensagens de erro observadas anteriormente.

Para obter mais informações, consulte Dispositivos de backup.

Observação

Você pode verificar as permissões atuais da conta do Serviço SQL em uma pasta navegando até a guia Segurança nas propriedades da pasta correspondente, selecionando o botão Avançado e usando a guia Acesso Efetivo .

As operações de backup ou restauração que usam aplicativos de backup de terceiros falham

O SQL Server fornece uma ferramenta VDI (Interface de Dispositivo de Backup Virtual). Essa API permite que fornecedores independentes de software integrem o SQL Server em seus produtos para fornecer suporte para operações de backup e restauração. Essas APIs são projetadas para fornecer confiabilidade e desempenho máximos e para dar suporte a toda a gama de funcionalidades de backup e restauração do SQL Server. Isso inclui toda a gama de recursos de snapshot e backup dinâmico.

Etapas de solução de problemas comuns

• Para versões anteriores ao SQL Server 2012, verifique se o serviço SQLWriter foi iniciado e se a conta de inicialização está definida como Sistema Local. Além disso, verifique se o logon NT AUTHORITY\SYSTEM existe no SQL Server e se ele faz parte da função de servidor Sysadmin da instância na qual os backups são executados.

• Para SQL Server 2012 e versões posteriores, um novo logon chamado [NT SERVICE\SQLWriter] é criado e provisionado como um logon durante a instalação. Verifique se esse logon existe no SQL Server e faz parte da função de servidor Sysadmin.

• Verifique se SqlServerWriter está listado quando o VSSADMIN LIST WRITERS comando é executado em um prompt de comando no servidor que está executando o SQL Server. Esse gravador deve estar listado como um gravador e deve estar no estado Estável para permitir que os backups do VSS sejam concluídos com êxito.

• Para obter mais informações, verifique os logs do software de backup correspondente e seus sites de suporte.

  Sintomas ou cenário	Artigo KB
  Falha nos backups de bancos de dados que diferenciam maiúsculas de minúsculas	2987610 CORREÇÃO: Erro ao fazer backup de um banco de dados que diferencia maiúsculas de minúsculas usando o VSS no SQL Server 2012 SP2
  Os backups de terceiros feitos usando o gravador VSS podem falhar e retornar erros 8229.	2987610 CORREÇÃO: Erro ao fazer backup de um banco de dados que diferencia maiúsculas de minúsculas usando o VSS no SQL Server 2012 SP2
  Noções básicas sobre como funciona o backup de VDI	Como funciona: SQL Server - Recursos de backup VDI (VSS)
  Falha de relatórios do agente do Azure Site Recovery	Falha no agente ASR ou em outro backup VSS não componente para um servidor que hospeda o SQL Server 2008 R2

Mais recursos

Como funciona: Quantos bancos de dados podem ser copiados simultaneamente?

Problemas diversos

Sintoma/cenário	Ações corretivas ou informações adicionais
Os backups poderão falhar se o controle de alterações estiver habilitado nos bancos de dados e retornar erros semelhantes aos seguintes: "Erro: 3999, Gravidade: 17, Estado: 1. <Carimbo de data/hora> spid <spid> Falha ao liberar a tabela de confirmação para o disco no dbid 8 devido ao erro 2601. Verifique o log de erros para obter mais informações."	Consulte os seguintes artigos da Base de Dados de Conhecimento Microsoft: 2682488 CORREÇÃO: A operação de backup falha em um SQL Server 2008, em um SQL Server 2008 R2 ou em um banco de dados SQL Server 2012 depois de habilitar o controle de alterações 2603910 CORREÇÃO: O backup falha no SQL Server 2008, no SQL Server 2008 R2 ou no SQL Server 2012 se você habilitar o controle de alterações no banco de dados 2522893 CORREÇÃO: Uma operação de backup em um banco de dados SQL Server 2008 ou SQL Server 2008 R2 falhará se você habilitar o controle de alterações neste banco de dados
Problemas ao restaurar backups de bancos de dados criptografados	Mover um banco de dados protegido por TDE para outro SQL Server
A tentativa de restaurar um backup do CRM da edição Enterprise falha em uma edição Standard	2567984 erro "O banco de dados não pode ser iniciado nesta edição do SQL Server" ao restaurar um banco de dados do Microsoft Dynamics CRM

Perguntas frequentes sobre operações de backup e restauração do SQL Server

Como posso verificar o status de uma operação de backup?

Use o script estimate_backup_restore para obter uma estimativa dos tempos de backup.

O que devo fazer se o SQL Server falhar no meio do backup?

Reinicie a operação de restauração ou backup de acordo com Reiniciar uma operação de restauração interrompida (Transact-SQL).

Posso restaurar backups de banco de dados de versões mais antigas do programa em versões mais recentes e vice-versa?

O backup do SQL Server não pode ser restaurado usando uma versão do SQL Server posterior à versão que criou o backup. Para obter mais informações, consulte Suporte à compatibilidade.

Como verifico meus backups de banco de dados do SQL Server?

Consulte os procedimentos documentados em Instruções RESTORE – VERIFYONLY (Transact-SQL).

Como posso obter o histórico de backup de bancos de dados no SQL Server?

Consulte Como obter o histórico de backup de bancos de dados no SQL Server.

Posso restaurar backups de 32 bits em servidores de 64 bits e vice-versa?

Sim. O formato de armazenamento em disco do SQL Server é o mesmo nos ambientes de 64 bits e 32 bits. Portanto, as operações de backup e restauração funcionam em ambientes de 64 bits e 32 bits.

Dicas de solução de problemas gerais

• Certifique-se de provisionar permissões de Leitura e Gravação para a conta de Serviço do SQL Server na pasta em que os backups estão sendo gravados. Para obter mais informações, confira Permissões para backup.
• Certifique-se de que a pasta em que os backups estão sendo gravados tenha espaço suficiente para acomodar seus backups de banco de dados. Você pode usar o sp_spaceused procedimento armazenado para obter uma estimativa aproximada do tamanho do backup para um banco de dados específico.
• Sempre use a versão mais recente do SSMS para garantir que você não encontre nenhum problema conhecido relacionado à configuração de trabalhos e planos de manutenção.
• Faça uma execução de teste de seus trabalhos para garantir que os backups sejam criados com êxito. Sempre adicione lógica para verificar os backups.
• Se você planeja mover bancos de dados do sistema de um servidor para outro, revise Mover bancos de dados do sistema.
• Se você notar falhas intermitentes de backup, verifique se está enfrentando um problema que já foi corrigido na atualização mais recente da versão do SQL Server. Para obter mais informações, confira Versões e atualizações do SQL Server.
• Para agendar e automatizar backups para edições do SQL Express, confira Agendar e automatizar backups de bancos de dados do SQL Server no SQL Server Express.

Tópicos de referência para operações de backup e restauração do SQL Server

• Para obter mais informações sobre operações de backup e restauração, consulte os seguintes tópicos nos Manuais Online:

  "Backup e restauração de bancos de dados do SQL Server": este tópico aborda os conceitos das operações de backup e restauração para bancos de dados do SQL Server, fornece links para tópicos adicionais e fornece procedimentos detalhados para executar vários backups ou tarefas de restauração (como verificar backups e fazer backup usando T-SQL ou SSMS). Este é o tópico pai sobre esse assunto na documentação do SQL Server.

• A tabela a seguir lista tópicos adicionais que talvez você queira revisar para tarefas específicas relacionadas a operações de backup e restauração.

  Referência	Descrição
  BACKUP (Transact-SQL)	Fornece respostas para perguntas básicas relacionadas a backups. Fornece exemplos de diferentes tipos de operações de backup e restauração.
  Dispositivos de backup (SQL Server)	Fornece uma ótima referência para entender vários dispositivos de backup, fazer backup em um compartilhamento de rede, armazenamento de blobs do Azure e tarefas relacionadas.
  Modelos de recuperação (SQL Server)	Aborda em detalhes os vários modelos de recuperação: Simples, Completo e Bulk-Logged. Fornece informações sobre como o modelo de recuperação afeta os backups.
  Backup e restauração: bancos de dados do sistema (SQL Server)	Aborda estratégias e discute o que você deve estar ciente ao trabalhar em operações de backup e restauração de bancos de dados do sistema.
  Visão geral da restauração e recuperação (SQL Server)	Aborda como os modelos de recuperação afetam as operações de restauração. Você deve revisar isso se tiver dúvidas sobre como o modelo de recuperação de um banco de dados pode afetar o processo de restauração.
  Gerenciar metadados ao disponibilizar um banco de dados em outro servidor	Várias considerações que você deve estar ciente quando um banco de dados é movido ou encontra problemas que afetam logons, criptografia, replicação, permissões e assim por diante.
  Trabalhando com backups de log de transações	Apresenta conceitos sobre como fazer backup e restaurar (aplicar) logs de transações nos modelos de recuperação completa e bulk-logged. Explica como fazer backups de rotina de logs de transações (backups de log) para recuperar dados.
  Backup Gerenciado do SQL Server para o Microsoft Azure	Apresenta o backup gerenciado e os procedimentos associados.