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

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 à des informations supplémentaires sur ces opérations.

Version du produit d’origine : SQL Server
Numéro de la 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 sont gourmandes en E/S. Le débit de sauvegarde/restauration dépend de l’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 qu’elles prennent trop de temps, vous pouvez utiliser une ou plusieurs des méthodes suivantes pour estimer le délai d’exécution 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 pour sauvegarder et restaurer 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 débit de sauvegarde et de restauration à l’aide des compteurs de l’analyseur Device throughput Bytes/sec de performances et Backup/Restore throughput/sec . 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 à 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 à case activée

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écrits dans les articles correspondants.

   Lien base de connaissances ou documentation en ligne	Explication et actions recommandées
   Optimisation des performances de sauvegarde et de restauration dans SQL Server	La rubrique de la documentation en ligne couvre diverses bonnes pratiques que vous pouvez utiliser pour améliorer les performances des opérations de sauvegarde/restauration. Par exemple, vous pouvez attribuer le SE_MANAGE_VOLUME_NAME privilège spécial au compte Windows qui exécute 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 Windows Server 2012 R2 correctif cumulatif 2822241 Windows 8 et Windows Server 2012 : avril 2013	Les correctifs cumulatifs système actuels peuvent inclure des correctifs pour des problèmes connus au niveau du système qui peuvent dégrader les performances de programmes tels que SQL Server. L’installation de ces mises à jour peut aider à éviter de tels problèmes.
   2878182 CORRECTIF : Les processus en mode utilisateur dans une application ne répondent pas sur les serveurs qui exécutent Windows Server 2012	Les opérations de sauvegarde sont gourmandes 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 .bak fichiers. 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 retardé 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 les 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 volume système volumineux
   2455009 CORRECTIF : Ralentissement des performances lorsque vous récupérez une base de données s’il existe de nombreux fichiers journaux virtuels 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 les autres problèmes possibles qui peuvent être causés par la présence de nombreux fichiers journaux virtuels, 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 à 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 à partir du serveur qui exécute 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 obtenir d’autres 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 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 case activée les mises à jour du microprogramme pour votre matériel.

Problèmes qui affectent la restauration de base de données entre différentes versions 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 un instance SQL Server 2019 dans 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 n’est pas compatible 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.

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, procédez comme suit :
   1. Cliquez avec le bouton droit sur <YourDatabase>Tasks>Generate Scripts(Générer des scripts), puis sélectionnez l’option permettant de générer un script pour l’ensemble de la base de données et 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 SQL Server Version. Sélectionnez également l’option qui vous convient le mieux pour enregistrer les scripts générés. Ensuite, continuez 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 des données terminée, activez les contraintes de clé étrangère et les déclencheurs, 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 volumineuses, des 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 à partir 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, consultez Créer un script de base de données à l’aide de l’option Générer des scripts.

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 points suivants :

• 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 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 pour chaque base de données de disponibilité sur chaque serveur instance 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 sur les réplicas secondaires d’un groupe de disponibilité

Erreurs liées aux médias 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 traiter cette famille de médias.

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

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

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. Passez en revue les journaux des événements du système Windows et les journaux du matériel pour rechercher 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 Checksum de sauvegarde lorsque vous exécutez une sauvegarde afin d’é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 des 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 rechercher un autre fichier de sauvegarde utilisable ou créer un jeu de sauvegarde. Microsoft n’offre aucune solution permettant de récupérer des données à partir d’un jeu de sauvegarde endommagé.

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 :

  Échec de la sauvegarde pour le nom> du <serveur. (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError : impossible d’ouvrir l’unité 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 enregistré 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 ne dispose pas des autorisations de lecture et d’écriture sur 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 SQL Server Service. Par conséquent, si le compte de service ne dispose pas des privilèges nécessaires, vous recevez les messages d’erreur qui ont été notés précédemment.

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

Les opérations de sauvegarde ou de restauration qui utilisent des applications de sauvegarde tierces échouent

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 pour assurer la prise en charge des 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. Cela inclut toute la gamme des fonctionnalités de sauvegarde instantané et à chaud.

Étapes communes de dépannage

• 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 du instance sur lequel 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 pendant l’installation. Vérifiez que cette connexion existe dans SQL Server et qu’elle fait partie du rôle serveur Sysadmin.

• Assurez-vous que SqlServerWriter est répertorié lorsque la VSSADMIN LIST WRITERS commande est exécutée à une invite de commandes sur le serveur qui exécute SQL Server. Cet enregistreur doit être répertorié en tant qu’enregistreur et doit être dans l’état Stable pour permettre aux sauvegardes VSS de se terminer correctement.

• Pour plus d’informations, case activée les journaux d’activité du logiciel de sauvegarde correspondant et de leurs sites de support.

  Symptômes ou scénario	Article de la Base de connaissances
  Échec des sauvegardes de bases de données sensibles à la casse	2987610 CORRECTIF : Erreur lors de la sauvegarde d’une base de données dont le classement respecte 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 lors de la sauvegarde d’une base de données dont le classement respecte la casse à l’aide de VSS dans SQL Server 2012 SP2
  Comprendre le fonctionnement de la sauvegarde VDI	Fonctionnement : SQL Server - Ressources de sauvegarde VDI (VSS)
  L’agent Azure Site Recovery signale un échec	L’agent ASR ou toute 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

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

Comment puis-je case activée la status 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 pour Redémarrer une opération de restauration interrompue (Transact-SQL).

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

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, consultez Prise en charge de la compatibilité.

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

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

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

Consultez 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 à configurer des autorisations de lecture et d’écriture sur le compte de service SQL Server sur le dossier dans lequel les sauvegardes sont écrites. Pour plus d’informations, consultez Autorisations pour la sauvegarde.
• Assurez-vous que le dossier dans lequel les sauvegardes sont écrites dispose de 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 tests 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 à un autre, consultez Déplacer des bases de données système.
• Si vous constatez des échecs de sauvegarde intermittents, case activée 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, consultez versions et mises à jour SQL Server.
• Pour planifier et automatiser des sauvegardes pour les éditions SQL Express, consultez Planifier et automatiser des sauvegardes de bases de données SQL Server dans SQL Server Express.

Rubriques de référence sur 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 couvre les concepts des opérations de sauvegarde et de restauration des 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 sur ce sujet dans SQL Server documentation.

• Le tableau suivant répertorie des rubriques supplémentaires que vous pouvez consulter pour des 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.
  Unités de sauvegarde (SQL Server)	Fournit une référence pratique pour comprendre les différentes unités de sauvegarde, la sauvegarde sur un partage réseau, le stockage Blob Azure et les tâches associées.
  Modèles 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 explique ce que vous devez savoir quand 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)	Décrit comment les modèles de récupération affectent les opérations de restauration. Vous devez le consulter 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, etc.
  Utilisation des sauvegardes du journal des transactions	Présente les concepts relatifs à la sauvegarde et à la restauration (appliquer) des journaux des transactions dans les modèles de récupération complète et de récupération des journaux en bloc. Explique comment effectuer des sauvegardes de routine des journaux des transactions (sauvegardes de journaux) pour récupérer des données.
  SQL Server sauvegarde managée sur Microsoft Azure	Introduit la sauvegarde managée et les procédures associées.