Dépanner des opérations de sauvegarde et de restauration SQL Server

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

Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 224071

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

Les opérations de sauvegarde et de restauration utilisent beaucoup d’E/S. Le débit de sauvegarde ou restauration dépend du degré d’optimisation du sous-système d’E/S sous-jacent pour gérer le volume d’E/S. Si vous pensez que les opérations de sauvegarde sont arrêtées ou trop longues pour se terminer, vous pouvez utiliser une ou plusieurs des méthodes suivantes pour estimer le délai d’achèvement ou suivre la progression d’une opération de sauvegarde ou de restauration :

• Le journal des erreurs 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 sauvegarde et à 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 percent_complete colonne 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 sauvegarde et de restauration du débit à l’aide des compteurs et Backup/Restore throughput/sec des compteurs de l’analyseur Device throughput Bytes/sec de performances. Pour plus d’informations, consultez SQL Server, Backup Device Object.

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

• Reportez-vous à la procédure : Qu’est-ce que la restauration/sauvegarde fait ?. 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. Déterminez si vous devez implémenter les modifications ou appliquer les correctifs et les meilleures pratiques décrites dans les articles correspondants.

   Lien de la base de connaissances ou de la documentation en ligne	Explication et actions recommandées
   Optimisation des performances de sauvegarde et de restauration dans SQL Server	La rubrique Documentation en ligne traite de différentes meilleures pratiques que vous pouvez utiliser pour améliorer les performances des opérations de sauvegarde/restauration. Par exemple, vous pouvez affecter le SE_MANAGE_VOLUME_NAME privilège spécial au compte Windows exécutant SQL Server pour activer l’initialisation instantanée des fichiers de données. Cela peut produire des gains de performances significatifs.
   2920151 correctifs logiciels et mises à jour recommandés pour les clusters de basculement basés sur Windows Server 2012 R2 correctif cumulatif 2822241 Windows 8 et Windows Server 2012 : avril 2013	Les cumuls système actuels peuvent inclure des correctifs pour les problèmes connus au niveau du système qui peuvent dégrader les performances des programmes tels que SQL Server. L’installation de ces mises à jour peut aider à empêcher ces problèmes.
   2878182 CORRECTIF : les processus en mode utilisateur dans une application ne répondent pas sur les serveurs exécutant 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.
   Configurer un logiciel antivirus pour qu’il fonctionne avec SQL Server	Les logiciels antivirus peuvent contenir des verrous sur des 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	Décrit 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 programme de sauvegarde échoue lorsque vous sauvegardez un grand volume système
   2455009 CORRECTIF : Performances lentes lorsque vous récupérez une base de données s’il existe de nombreux 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 pour restaurer une base de données. Cela est particulièrement vrai pendant la phase de récupération de l’opération de restauration. Pour plus d’informations sur d’autres problèmes possibles qui peuvent être causés par la présence de nombreuses fonctions de base de données virtuelles, consultez Les opérations de base de données prennent beaucoup de temps, ou déclenchent des erreurs lorsque le journal des transactions contient de nombreux fichiers journaux virtuels.
   Une opération de sauvegarde ou de restauration sur un emplacement réseau est lente	Isolez le problème sur le réseau en essayant de copier un fichier de taille similaire vers l’emplacement réseau du serveur exécutant SQL Server. Vérifiez les performances.

2. Recherchez les messages d’erreur dans le journal des erreurs SQL Server et le journal des événements Windows pour plus de pointeurs sur la cause du problème.

3. Si vous utilisez des plans de maintenance de logiciels ou de bases de données tiers pour effectuer des sauvegardes simultanées, déterminez si vous devez modifier les planifications pour réduire la contention sur le lecteur sur lequel les sauvegardes sont écrites.

4. Collaborez avec votre administrateur Windows pour rechercher les mises à jour du microprogramme pour votre matériel.

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

Une sauvegarde SQL Server ne peut pas être restaurée vers 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 effectuée sur une instance SQL Server 2019 vers une instance SQL Server 2017. Sinon, le message d’erreur suivant s’affiche :

Erreur 3169 : la base de données a été sauvegardée sur un serveur exécutant la version %ls. Cette version est incompatible avec ce serveur, qui exécute la version %ls. Restaurez la base de données sur un serveur qui prend en charge la sauvegarde ou utilisez 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.

Note

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

1. Téléchargez et installez la dernière version de SQL Server Management Studio (SSMS) tant sur SQL_A que sur SQL_B.
2. Sur SQL_A, procédez comme suit :
   1. Cliquez avec le bouton droit sur <Vos tâches>YourDatabase>Generate Scripts, puis sélectionnez l’option permettant de scripter l’ensemble de la base de données et de tous les objets de base de données.
   2. Dans l’écran Définir les options de script, sélectionnez Avancé, puis sélectionnez la version de SQL_B sous Script général>pour la version de SQL Server. Sélectionnez également l’option qui vous convient 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, procédez comme suit :
   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 de clé étrangère et les déclencheurs. Si la table comporte 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 de données terminée, activez les contraintes et les déclencheurs de clé étrangère et 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, les problèmes de mémoire insuffisante 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 manière de générer des scripts pour votre base de données, consultez Générer un script de base de données à l’aide de l’option Générer des scripts.

Problèmes liés aux travaux 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 points suivants :

• Par défaut, la préférence de sauvegarde automatique est définie sur Prefer Secondary. 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 effectuer de sauvegardes différentielles 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 planifiés pour générer des sauvegardes de vos bases de données, veillez à créer les travaux de 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 :

• Configurer des sauvegardes sur des réplicas secondaires d’un groupe de disponibilité Always On
• Décharger les sauvegardes prises en charge vers des réplicas secondaires d’un groupe de disponibilité

Erreurs liées aux supports lorsque vous restaurez une base de données à partir d’une sauvegarde

Si vous recevez des messages d’erreur indiquant un problème de fichier, cela est symptomatique d’un fichier de sauvegarde endommagé. Voici quelques exemples d’erreurs que vous pouvez obtenir si un jeu de sauvegarde est endommagé :

• 3241 : La famille de supports sur l’appareil '%ls' est incorrectement formée. SQL Server ne peut pas la traiter.

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

• 3243 : La famille de supports sur l’unité '%ls' a été créée avec la version %d.%d de Microsoft Tape Format. SQL Server prend en charge la version %d.%d.

Note

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, et ainsi de suite) ou qui sont liés à un virus ou un programme malveillant. Passez en revue les journaux des événements du système Windows 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, consultez Erreurs de média possibles pendant la sauvegarde et la restauration (SQL Server).

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

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

Note

Si un fichier de sauvegarde est restauré avec succès sur un serveur, mais pas sur un autre, essayez différentes façons de copier le fichier entre les serveurs. Par exemple, essayez robocopy à la place 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 de SQL Server Management Studio, la sauvegarde échoue et retourne le message d’erreur suivant :

  La sauvegarde a échoué pour le nom> du serveur de serveur<. (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError : impossible d’ouvrir le périphérique de sauvegarde «< nom> de l’appareil ». Erreur du système d’exploitation 5(Accès refusé.). (Microsoft.SqlServer.Smo)

• Scénario 2 : Les sauvegardes planifiées échouent et génèrent un message d’erreur connecté dans l’historique des travaux ayant é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 de lecture et d’écriture dans le dossier dans lequel les sauvegardes sont écrites. Les instructions de sauvegarde peuvent être exécutées dans le cadre d’une étape de travail ou manuellement à partir de SQL Server Management Studio. Dans les deux cas, ils s’exécutent toujours dans le contexte du compte de démarrage du service SQL Server. Par conséquent, si le compte de service n’a pas les privilèges nécessaires, vous recevez les messages d’erreur qui ont été notés précédemment.

Pour plus d’informations, consultez l’article Unités de sauvegarde.

Note

Vous pouvez vérifier les autorisations actuelles du compte SQL Service 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 fournisseurs de logiciels indépendants d’intégrer 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 prendre en charge la gamme complète de fonctionnalités de sauvegarde et de restauration SQL Server. Cela inclut la gamme complète des fonctionnalités de capture instantanée et de sauvegarde à chaud.

Étapes de résolution des problèmes courants

• Pour les versions antérieures à SQL Server 2012, assurez-vous que le service SQLWriter est démarré et que le compte de démarrage est défini sur Système local. Vérifiez également que la connexion NT AUTHORITY\SYSTEM existe dans SQL Server et qu’elle fait partie du rôle serveur Sysadmin de l’instance sur laquelle les sauvegardes sont exécutées.

• Pour SQL Server 2012 et versions ultérieures, une nouvelle connexion nommée [NT SERVICE\SQLWriter] est créée et approvisionnée en tant que connexion lors de l’installation. Vérifiez que cette connexion existe dans SQL Server et fait partie du rôle serveur Sysadmin.

• Vérifiez que SqlServerWriter est répertorié lorsque la VSSADMIN LIST WRITERS commande est exécutée à une invite de commandes sur le serveur exécutant SQL Server. Cet enregistreur doit être répertorié et se trouver dans un état Stable pour permettre la bonne exécution des sauvegardes VSS.

• Pour plus d’informations, consultez les journaux à partir du logiciel de sauvegarde correspondant et de leurs sites de support.

  Symptômes ou scénarios	Article de la Base de connaissances
  Échec des sauvegardes des bases de données sensibles à la casse	2987610 CORRECTIF : Erreur lorsque vous sauvegardez une base de données qui a un classement respectant la casse à l’aide de VSS dans SQL Server 2012 SP2
  Les sauvegardes tierces effectuées à l’aide de l’enregistreur VSS peuvent échouer et retourner des erreurs 8229.	2987610 CORRECTIF : Erreur lorsque vous sauvegardez une base de données qui a un classement respectant la casse à l’aide de VSS dans SQL Server 2012 SP2
  Présentation du fonctionnement de la sauvegarde VDI	Fonctionnement : SQL Server - Ressources de sauvegarde VDI (VSS)
  L’agent Azure Site Recovery signale l’échec	L’agent ASR ou une autre sauvegarde VSS non composant échoue pour un serveur hébergeant SQL Server 2008 R2

Plus de ressources

Fonctionnement : combien de bases de données peuvent être sauvegardées simultanément ?

Problèmes divers

Symptôme/scénario	Actions correctives ou informations supplémentaires
Les sauvegardes peuvent échouer si le suivi des modifications est activé sur les bases de données et retourne des erreurs qui ressemblent à ce qui suit : « Erreur : 3999, Gravité : 17, État : 1. <Horodatage> spid spid <> Failed to flush the commit table to disk in dbid 8 due to error 2601. Pour plus d’informations, consultez le journal des erreurs. »	Consultez les articles suivants de la Base de connaissances Microsoft : 2682488 CORRECTIF : Échec de l’opération de sauvegarde dans sql Server 2008, dans SQL Server 2008 R2 ou dans une base de données SQL Server 2012 après avoir activé le suivi des modifications 2603910 CORRECTIF : La sauvegarde échoue dans SQL Server 2008, dans SQL Server 2008 R2 ou dans SQL Server 2012 si vous activez le suivi des modifications sur la base de données 2522893 CORRECTIF : une opération de sauvegarde sur une base de données SQL Server 2008 ou SQL Server 2008 R2 échoue si vous activez le suivi des modifications sur cette base de données
Problèmes de restauration des sauvegardes de bases de données chiffrées	Déplacer une base de données protégée par le chiffrement transparent des données vers un autre serveur SQL Server
La tentative de restauration d’une sauvegarde CRM à partir de l’édition Enterprise échoue sur une édition Standard	2567984 erreur « 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

FAQ sur les opérations de sauvegarde et de restauration SQL Server

Comment puis-je 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 si SQL Server bascule au milieu de la 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 de versions antérieures du programme sur des versions plus récentes, et vice versa ?

La sauvegarde SQL Server 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, consultez Prise en charge de compatibilité.

Comment faire vérifier mes sauvegardes de base de données SQL Server ?

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

Comment puis-je obtenir l’historique de sauvegarde des bases de données dans 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 vice versa ?

Oui. Le format de stockage sur disque SQL Server 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 des environnements 64 bits et 32 bits.

Conseils généraux de dépannage

• Veillez à provisionner des autorisations en lecture-écriture sur le compte du service SQL Server sur le dossier dans lequel les sauvegardes sont écrites. Pour plus d’informations, consultez Autorisations de sauvegarde.
• Assurez-vous que le dossier que les sauvegardes sont écrites pour avoir suffisamment d’espace pour prendre en charge vos sauvegardes de base de données. Vous pouvez utiliser la sp_spaceused procédure stockée pour obtenir une estimation approximative de la taille de sauvegarde 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.
• Effectuez une série de test de vos travaux pour vous assurer que les sauvegardes sont correctement créées. Ajoutez toujours une logique pour vérifier vos sauvegardes.
• Si vous envisagez de déplacer des bases de données système d’un serveur vers un autre, passez en revue Déplacer des 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 de SQL Server. Pour plus d’informations, consultez Versions et mises à jour de SQL Server.
• Pour planifier et automatiser des sauvegardes pour les éditions de SQL Express, consultez Planifier et automatiser des sauvegardes de bases de données SQL Server dans SQL Server Express.

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

• Pour plus d’informations sur les opérations de sauvegarde et de restauration, consultez les rubriques suivantes dans la documentation en ligne :

  « Sauvegarde et restauration des bases de données SQL Server » : cette rubrique décrit les concepts des opérations de 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 différentes sauvegardes ou tâches de restauration (telles que la vérification des sauvegardes et la sauvegarde à l’aide de T-SQL ou SSMS). Il s’agit de la rubrique parente sur ce sujet dans la documentation de SQL Server.

• Le tableau suivant répertorie d’autres rubriques que vous souhaiterez peut-être examiner pour des tâches spécifiques liées aux opérations de sauvegarde et de restauration.

  Informations de 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.
  Unités de sauvegarde (SQL Server)	Fournit une excellente référence pour comprendre les différents périphériques de sauvegarde, la sauvegarde sur un partage réseau, le stockage d’objets blob Azure et les tâches associées.
  Modes de récupération (SQL Server)	Décrit en détail les différents modèles de récupération : simple, complet et journalisé en bloc. Fournit des informations sur la façon dont le modèle de récupération affecte les sauvegardes.
  Sauvegarde &restauration : bases de données système (SQL Server)	Couvre les stratégies et traite de ce que vous devez savoir quand vous travaillez sur les opérations de sauvegarde et de restauration des bases de données système.
  Vue d'ensemble de la restauration et de la récupération (SQL Server)	Décrit la façon dont les modèles de récupération affectent les opérations de restauration. Vous devez passer en revue cela 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	Différentes considérations que vous devez prendre en compte lorsqu’une base de données est déplacée ou que vous rencontrez des problèmes qui affectent les connexions, le chiffrement, la réplication, les autorisations, et ainsi de suite.
  Utilisation des sauvegardes du journal des transactions	Présente des concepts sur la sauvegarde et la restauration des journaux de transactions (appliquer) dans les modèles de récupération complètes et journalisées en bloc. Explique comment effectuer des sauvegardes de routine des journaux de transactions (sauvegardes de journaux) pour récupérer des données.
  Sauvegarde managée de SQL Server vers Microsoft Azure	Introduit la sauvegarde managée et les procédures associées.