Résoudre les problèmes SQL Server opérations de sauvegarde et de restauration

Cet article fournit des solutions aux problèmes courants que vous pouvez être en mesure de Microsoft SQL Server opérations de sauvegarde et de restauration, et fournit des références à d’autres informations sur ces opérations.

Version du produit d’origine :   SQL Server
Numéro de la ko d’origine :   224071

Les opérations de sauvegarde et de restauration prennent beaucoup de temps

Les opérations de sauvegarde et de restauration sont intensives en opérations d’I/S. Le débit de sauvegarde/restauration dépend de l’optimisation du sous-système d’opérations d’entreprise sous-jacent pour gérer le volume d’opérations d’opérations d’entreprise. Si vous pensez que les opérations de sauvegarde sont arrêtées ou prennent trop de temps pour se terminer, vous pouvez utiliser une ou plusieurs des méthodes suivantes pour estimer le temps d’exécution ou pour suivre la progression d’une opération de sauvegarde ou de restauration :

  • Le journal des SQL Server contient des informations sur les opérations de sauvegarde et de restauration précédentes. Vous pouvez utiliser ces détails pour estimer le temps nécessaire à la restauration de la base de données dans son état actuel. Voici un exemple de sortie du journal des erreurs :

    RESTORE DATABASE successfully processed 315 pages in 0.372 seconds (6.604 MB/sec)
    
  • Dans SQL Server 2016 et versions ultérieures, vous pouvez utiliser XEvent backup_restore_progress_trace pour suivre la progression des opérations de sauvegarde et de restauration.

  • Vous pouvez utiliser la colonne percent_complete de sys.dm_exec_requests pour suivre la progression des opérations de sauvegarde et de restauration en cours.

  • Vous pouvez mesurer les informations de débit de sauvegarde et de restauration à l’aide des compteurs d’écran de performances « Débit de l’appareil en octets/s » et « Débit de sauvegarde/restauration/s ». Pour plus d’informations, voir SQL Server, Backup Device Object.

  • Utilisez le script estimate_backup_restore pour obtenir une estimation des temps de sauvegarde.

  • Reportez-vous à How It Works: What is Restore/Backup Doing?   Ce billet de blog fournit des informations sur l’étape actuelle des opérations de sauvegarde ou de restauration.

Éléments à vérifier

  1. Vérifiez si vous rencontrez l’un des problèmes connus répertoriés dans le tableau suivant. Envisagez si vous devez implémenter les modifications ou appliquer les correctifs et les meilleures pratiques abordés dans les articles correspondants.

    Lien en ligne Base de connaissances ou Livres Explication et actions recommandées
    Optimisation des performances de sauvegarde et de restauration dans SQL Server La rubrique Books Online couvre différentes meilleures pratiques que vous pouvez utiliser pour améliorer les performances des opérations de sauvegarde/restauration. Par exemple, vous pouvez attribuer le privilège SE_MANAGE_VOLUME_NAME au compte Windows en cours d’exécution SQL Server pour permettre l’initialisation instantanée des fichiers de données. Cela peut produire des gains de performances significatifs.
    2920151 correctifs et mises à jour recommandés pour les clusters de Windows Server 2012 R2

    2822241 Windows 8 et Windows Server 2012 de mise à jour : avril 2013
    Les correctifs système actuels peuvent inclure des correctifs pour les problèmes connus au niveau du système qui peuvent entraîner une dégradation des performances des programmes tels que SQL Server. L’installation de ces mises à jour permet d’éviter de tels problèmes.
    2878182 fix : les processus en mode utilisateur dans une application ne sont pas résponsibles sur les serveurs qui s’exécutent Windows Server 2012

    Les opérations de sauvegarde sont intensives en E/S et peuvent être affectées par ce bogue. Appliquez ce correctif pour éviter ces problèmes.
    309422 choisir un logiciel antivirus à exécuter sur des ordinateurs qui exécutent des SQL Server Le logiciel antivirus peut contenir des verrous sur les fichiers .bak. Cela peut affecter les performances des opérations de sauvegarde et de restauration. Suivez les instructions de cet article pour exclure les fichiers de sauvegarde des analyses antivirus.
    2820470 message d’erreur Différé lorsque vous essayez d’accéder à un dossier partagé qui n’existe plus dans Windows Traite d’un problème qui se produit lorsque vous essayez d’accéder à un dossier partagé qui n’existe plus dans Windows 2012 et versions ultérieures.
    967351 un fichier fortement fragmenté dans un volume NTFS peut ne pas dépasser une certaine taille Traite d’un problème qui se produit lorsqu’un système de fichiers NTFS est fortement fragmenté.
    304101 sauvegarde échoue lorsque vous sauvegardez un volume système important
    2455009 FIX : performances lentes lorsque vous récupérez une base de données s’il existe de nombreux fichiers VLF dans le journal des transactions dans SQL Server 2005, dans SQL Server 2008 ou dans SQL Server 2008 R2 La présence de nombreux fichiers journaux virtuels peut affecter le temps nécessaire à la restauration d’une base de données. Cela est particulièrement vrai lors de la phase de récupération de l’opération de restauration. Pour plus d’informations sur les autres problèmes possibles qui peuvent être causés par la présence de nombreux fichiers VLF, consultez La procédure des opérations de base de données prend beaucoup de temps, ou elles déclenchent des erreurs lorsque le journal des transactionscontient de nombreux fichiers journaux virtuels.
    Une opération de sauvegarde ou de restauration sur un emplacement réseau est lente Isolez le problème au réseau en essayant de copier un fichier de même taille sur l’emplacement réseau à partir du serveur qui exécute SQL Server. Vérifiez les performances.
  2. Recherchez les messages d’erreur dans SQL Server journal des erreurs et Windows journal des événements pour plus de pointeurs sur la cause du problème.

  3. Si vous utilisez des logiciels tiers ou des plans de maintenance de base de données pour des sauvegardes simultanées, pensez à modifier les planifications afin de minimiser la contention sur le lecteur sur lequel les sauvegardes sont écrites.

  4. Travaillez avec votre administrateur Windows pour vérifier les mises à jour du microprogramme de votre matériel.

Problèmes affectant la restauration de base de données entre différentes versions SQL Server données

Une SQL Server de sauvegarde ne peut pas être restaurée dans une version antérieure de SQL Server que la version à laquelle la sauvegarde a été créée. Par exemple, vous ne pouvez pas restaurer une sauvegarde qui est prise sur une instance SQL Server 2019 vers une instance SQL Server 2017. Si vous essayez de le faire, le message d’erreur suivant sera généré :

Erreur 3169 : la base de données a été backed sur un serveur exécutant la version %ls. Cette version est incompatible avec ce serveur, qui exécute la version %ls. Restituer la base de données sur un serveur qui prend en charge la sauvegarde ou utiliser une sauvegarde compatible avec ce serveur.

Utilisez la méthode suivante pour copier une base de données hébergée sur une version ultérieure de SQL Server vers une version antérieure de SQL Server.

Notes

La procédure suivante suppose que vous avez deux instances SQL Server nommées SQL_A (version supérieure) et SQL_B (version inférieure).

  1. Téléchargez et installez la dernière version de SQL Server Management Studio (SSMS) sur SQL_A et SQL_B.
  2. Sur SQL_A, suivez les étapes suivantes :
    1. Cliquez avec le bouton droit <vos tâches YourDatabase génèrent des > > scripts, puis sélectionnez l’option de script de la base de données entière et de tous les objets de base de données.
    2. Dans l’écran Définir les options de script, sélectionnez Options avancées, puis sélectionnez la version de SQL_B sous Script général pour > SQL Server version. En outre, sélectionnez l’option qui fonctionne le mieux pour enregistrer les scripts générés. Ensuite, poursuivez l’Assistant.
    3. Utilisez l’utilitaire de programme de copie en bloc (bcp) pour copier des données à partir de différentes tables.
  3. Sur SQL_B, suivez les étapes suivantes :
    1. Utilisez les scripts générés sur le serveur SQL_A pour créer un schéma de base de données.
    2. Sur chacune des tables, désactivez les contraintes et déclencheurs de clé étrangère. Si le tableau possède des colonnes d’identité, activez l’insertion d’identité.
    3. Utilisez bcp pour importer les données que vous avez exportées à l’étape précédente dans les tables correspondantes.
    4. Une fois l’importation des données terminée, activez les contraintes et déclencheurs de clé étrangère, puis désactivez l’insertion d’identité pour chacune des tables affectées à l’étape c.

Cette procédure fonctionne généralement bien pour les bases de données de petite à moyenne taille. Pour les bases de données plus volumineuses, des problèmes de mémoire peuvent se produire dans SSMS et d’autres outils. Vous devez envisager d’utiliser SQL Server Integration Services (SSIS),la réplication ou d’autres options pour créer une copie d’une base de données d’une version ultérieure vers une version antérieure de SQL Server.

Pour plus d’informations sur la façon de générer des scripts pour votre base de données, voir Script a database by using the Generate Scripts option.

Problèmes de travail de sauvegarde dans les environnements Always On

Si vous rencontrez des problèmes qui affectent les travaux de sauvegarde ou les plans de maintenance dans les environnements Always On, notez les remarques suivantes :

  • Par défaut, la préférence de sauvegarde automatique est définie sur Préférer secondaire. Cela spécifie que les sauvegardes doivent se produire sur un réplica secondaire, sauf si le réplica principal est le seul réplica en ligne. Vous ne pouvez pas prendre de sauvegardes différentielle de votre base de données à l’aide de ce paramètre. Pour modifier ce paramètre, utilisez SSMS sur votre réplica principal actuel et accédez à la page Préférences de sauvegarde sous Propriétés de votre groupe de disponibilité.
  • Si vous utilisez un plan de maintenance ou des travaux programmés pour générer des sauvegardes de vos bases de données, veillez à créer les travaux pour chaque base de données de disponibilité sur chaque instance de serveur qui héberge un réplica de disponibilité pour le groupe de disponibilité.

Pour plus d’informations sur les sauvegardes dans un environnement Always On, consultez les rubriques suivantes :

Si vous recevez des messages d’erreur qui indiquent un problème de fichier, cela est symptôme d’un fichier de sauvegarde endommagé. Voici quelques exemples d’erreurs que vous pouvez voir si un jeu de sauvegarde est endommagé :

  • 3241 : la famille de médias sur l’appareil « %ls » est mal formée. SQL Server ne peut pas traiter cette famille de médias.

  • 3242 : le fichier sur l’appareil « %ls » n’est pas un jeu de sauvegarde Microsoft Tape Format valide.

  • 3243 : la famille de médias sur l’appareil « %ls » a été créée à l’aide de la version %d.%d du format de bande Microsoft. SQL Server prend en charge la version %d.%d.

Notes

Vous pouvez utiliser l’instruction Restore Header pour vérifier vos sauvegardes.

Ces problèmes peuvent se produire en raison de problèmes qui affectent le matériel sous-jacent (disques durs, stockage réseau, etc.) ou qui sont liés à un virus ou à un programme malveillant. Examinez Windows journaux des événements système et les journaux matériels pour les erreurs signalées et prenez les mesures appropriées (par exemple, mettre à niveau le microprogramme ou résoudre les problèmes réseau).

Pour éviter ces erreurs, activez l’option BACKUP CHECKSUM lorsque vous exécutez une sauvegarde pour éviter la sauvegarde d’une base de données endommagée. Pour plus d’informations, voir Possible Media Errors During Backup and Restore (SQL Server).

Vous pouvez également activer l’indicateur de suivi 3023 pour activer une checksum lorsque vous exécutez des sauvegardes à l’aide des outils de sauvegarde. Pour plus d’informations, voir Comment activer l’option CHECKSUM si les utilitaires de sauvegarde n’exposent pas l’option.

Pour résoudre ces problèmes, vous devez rechercher un autre fichier de sauvegarde utilisable ou créer un jeu de sauvegarde. Microsoft ne propose pas de solutions qui peuvent vous aider à récupérer des données à partir d’un jeu de sauvegarde endommagé.

Notes

Si un fichier de sauvegarde est correctement restauré sur un serveur, mais pas sur un autre, essayez différentes façons de copier le fichier entre les serveurs. Par exemple, essayez robocopy au lieu d’une opération de copie normale.

Les sauvegardes échouent en raison de problèmes d’autorisations

Lorsque vous essayez d’exécuter des opérations de sauvegarde de base de données, l’une des erreurs suivantes se produit.

  • Scénario 1 : lorsque vous exécutez une sauvegarde à partir SQL Server Management Studio, la sauvegarde échoue et renvoie le message d’erreur suivant :

    Échec de la sauvegarde pour le <Server name> serveur. (Microsoft.SqlServer.SmoExtended)
    System.Data.SqlClient.SqlError : impossible d’ouvrir le périphérique de sauvegarde ' <device name> '. Erreur du système d’exploitation 5(Accès refusé). (Microsoft.SqlServer.Smo)

  • Scénario 2: les sauvegardes programmées échouent et génèrent un message d’erreur consigné dans l’historique des travaux du travail qui a échoué et qui ressemble à ce qui suit :

    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.
    

L’un de ces scénarios peut se produire si le compte de service SQL Server n’a pas d’autorisations lecture et écriture sur le dossier dans quoi les sauvegardes sont écrites. Les instructions de sauvegarde peuvent être exécutés dans le cadre d’une étape du travail ou manuellement à partir SQL Server Management Studio. Dans les deux cas, ils s’exécutent toujours dans le contexte du compte de démarrage SQL Server service. Par conséquent, si le compte de service ne bénéficie pas des privilèges nécessaires, vous recevez les messages d’erreur mentionnés précédemment.

Pour plus d’informations, voir Périphériques de sauvegarde.

Notes

Vous pouvez vérifier les autorisations actuelles du compte de service SQL sur un dossier en accédant à l’onglet Sécurité dans les propriétés du dossier correspondant, en sélectionnant le bouton Avancé, puis en utilisant l’onglet Accès effectif.

Échec des opérations de sauvegarde ou de restauration qui utilisent des applications de sauvegarde tierces

SQL Server fournit un outil VDI (Virtual Backup Device Interface). Cette API permet aux éditeurs de logiciels indépendants d’intégrer des SQL Server dans leurs produits afin de prendre en charge les opérations de sauvegarde et de restauration. Ces API sont conçues pour fournir une fiabilité et des performances maximales, et pour prendre en charge la gamme complète des fonctionnalités de sauvegarde et de restauration SQL Server de données. Cela inclut la gamme complète des fonctionnalités de capture instantanée et de sauvegarde à chaud.

Étapes communes de dépannage

Plus de ressources

How It Works: How many databases can be backed up simultaneously?

Divers problèmes

Symptôme/scénario Mesures correctives ou informations supplémentaires
Les sauvegardes peuvent échouer si le suivi des changements est activé sur les bases de données et renvoie des erreurs semblables à ce qui suit :

« Erreur : 3999, Gravité : 17, État : 1.

<Time Stamp> Spid N’a pas réussi à vider la table de validation sur disque en <spid> dbid 8 en raison de l’erreur 2601. Pour plus d’informations, consultez le journal des erreurs. »


Consultez les articles suivants de la Base de connaissances Microsoft :
Problèmes de restauration des sauvegardes de bases de données chiffrées Déplacer une base de données protégée TDE vers une autre SQL Server
La tentative de restauration d’une sauvegarde CRM à partir de l’édition Enterprise échoue sur une édition Standard 2567984 erreur « La base de données ne peut pas être démarrée dans cette édition de SQL Server » lors de la restauration d’une base de données Microsoft Dynamics CRM données

FAQ sur les SQL Server de sauvegarde et de restauration

Comment vérifier l’état d’une opération de sauvegarde ?

Utilisez le script estimate_backup_restore pour obtenir une estimation des temps de sauvegarde.

Que dois-je faire en cas SQL Server de secours en cours de sauvegarde ?

Redémarrez l’opération de restauration ou de sauvegarde par redémarrage d’une opération de restauration interrompue (Transact-SQL).

Puis-je restaurer des sauvegardes de base de données à partir d’anciennes versions de programmes sur des versions plus récentes, et inversement ?

SQL Server sauvegarde ne peut pas être restaurée à l’aide d’une version de SQL Server ultérieure à la version qui a créé la sauvegarde. Pour plus d’informations, voir Prise en charge de la compatibilité.

Comment puis-je vérifier mes sauvegardes SQL Server base de données ?

Consultez les procédures documentées dans RESTORE Statements - VERIFYONLY (Transact-SQL).

Comment puis-je obtenir l’historique de sauvegarde des bases de données SQL Server ?

Découvrez comment obtenir l’historique de sauvegarde des bases de données dans SQL Server.

Puis-je restaurer des sauvegardes 32 bits sur des serveurs 64 bits, et inversement ?

Oui. Le SQL Server de stockage sur disque est le même dans les environnements 64 bits et 32 bits. Par conséquent, les opérations de sauvegarde et de restauration fonctionnent dans les environnements 64 bits et 32 bits.

Conseils généraux de dépannage

  • Veillez à fournir des autorisations de lecture et d’écriture au compte SQL Server service sur le dossier dans le dossier où les sauvegardes sont écrites. Pour plus d’informations, voir Autorisations pour la sauvegarde.
  • Assurez-vous que le dossier que les sauvegardes sont en cours d’écriture doit contenir suffisamment d’espace pour prendre en charge vos sauvegardes de base de données. Vous pouvez utiliser la procédure stockée pour obtenir une estimation approximative de la taille de sauvegarde sp_spaceused d’une base de données spécifique.
  • Utilisez toujours la dernière version de SSMS pour vous assurer que vous ne rencontrez aucun problème connu lié à la configuration des travaux et des plans de maintenance.
  • Testez vos tâches pour vous assurer que les sauvegardes sont correctement créées. Toujours ajouter une logique pour vérifier vos sauvegardes.
  • Si vous prévoyez de déplacer des bases de données système d’un serveur à un autre, examinez Déplacer les bases de données système.
  • Si vous remarquez des échecs de sauvegarde intermittents, vérifiez si vous rencontrez un problème déjà résolu dans la dernière mise à jour de votre version SQL Server. Pour plus d’informations, voir SQL Server versions et mises à jour.
  • Pour planifier et automatiser des sauvegardes pour SQL éditions Express, voir Planifier et automatiser les sauvegardesde bases de données SQL Server dans SQL Server Express .

Rubriques de référence pour les SQL Server de sauvegarde et de restauration

  • Pour plus d’informations sur les opérations de sauvegarde et de restauration, consultez les rubriques suivantes dans Books Online :

    « Sauvegarder et restaurer des bases de données SQL Server » : cette rubrique traite des concepts des opérationsde sauvegarde et de restauration pour les bases de données SQL Server, fournit des liens vers des rubriques supplémentaires et fournit des procédures détaillées pour exécuter diverses sauvegardes ou tâches de restauration (telles que la vérification des sauvegardes et la sauvegarde à l’aide de T-SQL ou de SSMS). Il s’agit de la rubrique parente à ce sujet dans SQL Server documentation.

  • Le tableau suivant répertorie les rubriques supplémentaires que vous souhaitez peut-être consulter pour les tâches spécifiques liées aux opérations de sauvegarde et de restauration.

    Référence Description
    BACKUP (Transact-SQL) Fournit des réponses aux questions de base relatives aux sauvegardes. Fournit des exemples de différents types d’opérations de sauvegarde et de restauration.
    Périphériques de sauvegarde (SQL Server) Fournit une excellente référence pour comprendre différents périphériques de sauvegarde, la sauvegarde d’un partage réseau, le stockage d’objets blob Azure et les tâches connexes.
    Modèles de récupération (SQL Server) Couvre en détail les différents modèles de récupération : simple, complet et journalisé en bloc. Fournit des informations sur l’impact du modèle de récupération sur les sauvegardes.
    Restauration & sauvegarde : bases de données système (SQL Server) Aborde les stratégies et explique ce que vous devez savoir lorsque vous travaillez sur des opérations de sauvegarde et de restauration de bases de données système.
    Vue d’ensemble de la restauration et de la récupération (SQL Server) Aborde l’impact des modèles de récupération sur les opérations de restauration. Vous devez examiner ce point si vous avez des questions sur la façon dont le modèle de récupération d’une base de données peut affecter le processus de restauration.
    Gérer les métadonnées lors de la mise à disposition d’une base de données sur un autre serveur Diverses considérations doivent être à prendre en compte lorsqu’une base de données est déplacée ou que vous rencontrez des problèmes affectant les connexions, le chiffrement, la réplication, les autorisations, etc.
    Working with Transaction Log Backups Présente des concepts sur la façon de back up and restore (apply) transaction logs in the full and bulk-logged recovery models. Explique comment prendre des sauvegardes de routine des journaux de transactions (sauvegardes des journaux) pour récupérer des données.
    SQL Server Sauvegarde gérée à des Microsoft Azure Présente la sauvegarde gérée et les procédures associées.