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

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

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

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

As operações de backup e restauração são intensivas em E/S. A taxa de transferência de backup/restauração depende do 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 demoram muito para serem concluídas, você poderá 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 SQL Server contém informações sobre operações de backup e restauração anteriores. Você pode usar esses detalhes para estimar o tempo necessário para fazer backup e restaurar o banco de dados em seu estado atual. A seguir está uma saída de exemplo do log de erros:

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

• Em versões SQL Server 2016 e 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 no voo.

• Você pode medir as informações de backup e restauração de taxa de transferência usando os contadores e Backup/Restore throughput/sec o Device throughput Bytes/sec monitor de desempenho. 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 está fazendo restauração/backup?. Esta postagem no blog fornece informações sobre o estágio atual das operações de backup ou restauração.

Coisas para marcar

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 que são discutidas nos artigos correspondentes.

   Link da Base de Dados de Conhecimento ou Livros Online	Explicação e ações recomendadas
   Otimizando o desempenho de backup e restauração em SQL Server	O tópico Livros 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 SE_MANAGE_VOLUME_NAME privilégio especial à conta do Windows que está executando SQL Server para habilitar a inicialização instantânea de arquivos de dados. Isso pode produzir ganhos significativos de desempenho.
   2920151 hotfixes e atualizações recomendados para Windows Server 2012 clusters de failover baseados em R2 2822241 Windows 8 e Windows Server 2012 rollup de atualização: abril de 2013	As rollups atuais do sistema podem incluir correções para problemas conhecidos no nível do sistema que podem degradar o desempenho de programas como SQL Server. A instalação dessas atualizações pode ajudar a evitar esses problemas.
   2878182 FIX: os processos de modo de usuário em um aplicativo não respondem em servidores que estão executando 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 software antivírus para trabalhar com SQL Server	O software antivírus pode conter bloqueios em arquivos .bak. Isso pode afetar o desempenho das operações de backup e restauração. Siga as diretrizes neste artigo para excluir arquivos de backup de 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 fortemente 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 é fortemente 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, em SQL Server 2008 ou em SQL Server 2008 R2	A presença de muitos arquivos de log virtual 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 Operações de banco de dados demoram muito tempo para serem concluídas ou disparam erros quando o log de transações tem vários arquivos de log virtual.
   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 SQL Server. Verifique o desempenho.

2. Verifique se há mensagens de erro no log de erros SQL Server e no log de eventos do Windows para obter mais ponteiros 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 você deve alterar os agendamentos para minimizar a contenção na unidade para a qual os backups estão sendo gravados.

4. Trabalhe com o administrador do Windows para marcar para atualizações de firmware para seu hardware.

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

Um backup 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 que foi feito em uma instância SQL Server 2019 para uma instância SQL Server 2017. Caso contrário, a seguinte mensagem de erro será exibida:

Erro 3169: o banco de dados foi feito com backup 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.

1. Baixe e instale a versão mais recente do SSMS (SQL Server Management Studio) em SQL_A e SQL_B.
2. Em SQL_A, siga estas etapas:
   1. Cliquecom o botão direito do mouse< em Tarefas do SeuDatabase>> Gerar Scripts e selecione a opção para rotear todo o banco de dados e todos os objetos de banco de dados.
   2. Na tela Definir Opções de Script, selecione Avançado e selecione a versão do SQL_B em Script Geral>para SQL Server Versão. 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. Em SQL_B, siga estas etapas:
   1. Use os scripts gerados no servidor SQL_A para criar o esquema de banco de dados.
   2. Em cada uma das tabelas, desabilite quaisquer restrições e 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 tabelas correspondentes.
   4. Depois que a importação de dados for concluída, habilite restrições e gatilhos de chave estrangeira e desabilite a inserção de identidade para cada uma das tabelas afetadas na etapa c.

Normalmente, esse procedimento funciona bem para bancos de dados de pequeno a médio porte. Para bancos de dados maiores, problemas de memória fora da memória podem ocorrer no SSMS e em outras ferramentas. Você deve considerar o uso SQL Server SSIS (Integration Services), 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 seu banco de dados, consulte Script de um banco de dados usando a opção Gerar Scripts.

Problemas de trabalho de backup em ambientes de Always On

Se você encontrar problemas que afetam trabalhos 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 Prefer Secondary. Isso especifica que os backups devem ocorrer em um réplica secundário - exceto se o réplica primário for o único 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 no réplica primário 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, crie os trabalhos para cada banco de dados de disponibilidade em cada instância do servidor que hospeda um réplica de disponibilidade para o grupo de disponibilidade.

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

• Configurar backups em réplicas secundárias de um grupo de disponibilidade Always On
• Descarregar backups com suporte para 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 será sintomático de um arquivo de backup corrompido. Veja a seguir 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' é formada incorretamente. 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 Formato de Fita da Microsoft.

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

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 logs de eventos e logs de hardware do Sistema Windows para obter erros relatados e tome as medidas apropriadas (por exemplo, atualize firmware ou corrija problemas de rede).

Para evitar esses erros, habilite a opção BACKUP CHECKSUM ao executar um backup para evitar fazer backup de um banco de dados corrompido. Para obter mais informações, consulte Possíveis erros de mídia durante o backup e 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 soluções que possam ajudar a recuperar dados de um conjunto de backup corrompido.

Os backups falham devido a problemas de permissões

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 de 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 dispositivo de backup '<nome> do dispositivo'. Erro do sistema operacional 5(O 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 isso se assemelha ao 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 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 de SQL Server Management Studio. Em ambos os casos, eles sempre são executados no contexto da conta de inicialização do serviço de SQL Server. Portanto, se a conta de serviço não tiver os privilégios necessários, você receberá as mensagens de erro que foram anotadas anteriormente.

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

Falha nas operações de backup ou restauração que usam aplicativos de backup de terceiros

SQL Server fornece uma ferramenta VDI (Virtual Backup Device Interface). Essa API permite que fornecedores de software independentes integrem 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 todo o intervalo de SQL Server funcionalidade de backup e restauração. Isso inclui o intervalo completo de recursos de backup instantâneo e quente.

Etapas comuns de solução de problemas

• Para versões anteriores a SQL Server 2012, verifique se o serviço SQLWriter foi iniciado e que 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 para a qual os backups são executados.

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

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

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

  Sintomas ou cenário	Artigo KB
  Backups de bancos de dados sensíveis a casos falhando	2987610 CORREÇÃO: erro ao fazer backup de um banco de dados que tenha uma ordenação sensível a casos usando VSS no SQL Server 2012 SP2
  Backups de terceiros que são feitos usando o gravador VSS podem falhar e retornar 8229 erros.	2987610 CORREÇÃO: erro ao fazer backup de um banco de dados que tenha uma ordenação sensível a casos usando VSS no SQL Server 2012 SP2
  Entender como funciona o backup do VDI	Como funciona: recursos de backup SQL Server – VDI (VSS)
  Agente de recuperação do Site do Azure relata falha	Falha no backup do VSS do ASR Agent ou de outro não componente para uma hospedagem de servidor SQL Server 2008 R2

Mais recursos

Como funciona: quantos bancos de dados podem ser backup simultaneamente?

Problemas diversos

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

Como posso marcar 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 SQL Server falhar no meio do backup?

Reinicie a operação de restauração ou backup por Reiniciar uma Operação de Restauração Interrompida (Transact-SQL).

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

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

Como fazer verificar meus backups de banco de dados 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 em 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 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 gerais para solução de problemas

• Certifique-se de provisionar permissões de leitura e gravação na conta SQL Server Serviço na pasta na qual os backups estão sendo gravados. Para obter mais informações, consulte Permissões para backup.
• Verifique se a pasta que os backups estão sendo gravados para ter 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 de um banco de dados específico.
• Use sempre a versão mais recente do SSMS para garantir que você não encontre problemas conhecidos relacionados à 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 seus backups.
• Se você planeja mover bancos de dados do sistema de um servidor para outro, examine Mover Bancos de Dados do Sistema.
• Se você notar falhas de backup intermitentes, marcar se você está enfrentando um problema que já está corrigido na atualização mais recente para sua versão SQL Server. Para obter mais informações, consulte SQL Server Versões e atualizações.
• Para agendar e automatizar backups para edições do SQL Express, consulte Agendar e automatizar backups de bancos de dados SQL Server em SQL Server Express.

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

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

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

• A tabela a seguir lista tópicos adicionais que talvez você queira examinar 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 de um compartilhamento de rede, armazenamento de blobs do Azure e tarefas relacionadas.
  Modelos de recuperação (SQL Server)	Aborda detalhadamente os vários modelos de recuperação: Simples, Completo e Registrado em Massa. Fornece informações sobre como o modelo de recuperação afeta os backups.
  Restauração de & de backup: 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 de restauração e recuperação (SQL Server)	Aborda como os modelos de recuperação afetam as operações de restauração. Você deve examinar 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 você encontra quaisquer 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 logs de transações (aplicar) nos modelos de recuperação completos e registrados em massa. Explica como fazer backups rotineiros de logs de transações (backups de log) para recuperar dados.
  SQL Server Backup Gerenciado no Microsoft Azure	Apresenta backup gerenciado e procedimentos associados.