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

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

Résumé

Cet article vous aide à résoudre les problèmes courants liés aux opérations de sauvegarde et de restauration SQL Server. Ces problèmes incluent des performances de sauvegarde ou de restauration lentes, des erreurs de compatibilité de version, des travaux de sauvegarde de groupe de disponibilité Always On, des erreurs multimédias, des échecs d’autorisations, des sauvegardes VDI tierces et VSS, des échecs de suivi des modifications et des restaurations de bases de données chiffrées. L’article comprend également une section des questions fréquemment posées et des liens vers des rubriques de référence pour SQL Server sauvegarde et restauration.

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 et de restauration dépend de la façon dont le sous-système d’E/S sous-jacent est optimisé 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 pour terminer, utilisez 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 versions 2016 et ultérieures, utilisez le backup_restore_progress_trace XEvent pour suivre la progression des opérations de sauvegarde et de restauration.

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

• Mesurez le débit de sauvegarde et de restauration à l’aide des Device throughput Bytes/seccompteurs d’analyse de performancesBackup/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.

• Consultez Fonctionnement : que fait la restauration/sauvegarde ?. Ce billet de blog fournit des informations sur l’étape actuelle des opérations de sauvegarde ou de restauration.

Examiner les performances de sauvegarde ou de restauration lentes

1. Vérifiez si vous rencontrez l’un des problèmes connus dans le tableau suivant et envisagez d’appliquer les correctifs ou bonnes pratiques appropriés.

   Lien de la Base de connaissances	Explication et actions recommandées
   Sauvegarder et restaurer des bases de données SQL Server	Couvre les meilleures pratiques qui peuvent améliorer les performances de sauvegarde et de restauration. Par exemple, accordez le privilège SE_MANAGE_VOLUME_NAME au compte Windows sous lequel SQL Server s’exécute afin de permettre à l’initialisation instantanée des fichiers d’accélérer les opérations sur les fichiers de données.
   Configurer un logiciel antivirus pour qu’il fonctionne avec SQL Server	Les logiciels antivirus peuvent contenir des verrous sur .bak des fichiers, ce qui 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.
   Une opération de sauvegarde ou de restauration sur un emplacement réseau est lente	Isolez le problème sur le réseau en copiant un fichier de taille similaire vers l’emplacement réseau du serveur qui s’exécute SQL Server, puis vérifiez les performances.

2. Vérifiez le journal des erreurs SQL Server et Windows journal des événements pour les messages d’erreur qui pointent vers 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, envisagez de modifier les planifications pour réduire la contention sur le lecteur dans lequel les sauvegardes sont écrites.

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

Erreurs lors de la restauration de sauvegardes vers des versions antérieures de SQL Server

Symptoms

Vous ne pouvez pas restaurer une sauvegarde SQL Server vers une version antérieure de SQL Server que la version qui a créé la sauvegarde. Par exemple, vous ne pouvez pas restaurer une sauvegarde effectuée sur une instance SQL Server 2022 vers une instance SQL Server 2019. 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.

Résolution

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) tant sur SQL_A que sur SQL_B.
2. Sur SQL_A, procédez comme suit :
   1. Cliquez avec le bouton droit sur < Vostâches >>>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 ensuite l’option d’enregistrement qui vous convient le mieux, puis 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 le schéma de base de données.
   2. Sur chaque table, désactivez toutes les contraintes de clé étrangère et les déclencheurs associés. 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 modifié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 insuffisante peuvent se produire dans SSMS et d’autres outils. Envisagez d’utiliser SQL Server Integration Services (SSIS), la réplication ou d’autres options pour copier 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 de travail de sauvegarde dans les groupes de disponibilité Always On

Symptoms

Vous rencontrez des problèmes qui affectent les travaux de sauvegarde ou les plans de maintenance dans les environnements de groupe de disponibilité Always On.

Résolution

• Par défaut, la préférence de sauvegarde automatique est définie sur Prefer Secondary. Ce paramètre spécifie que les sauvegardes se produisent 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 avec 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, créez les travaux pour chaque base de données de disponibilité sur chaque instance de serveur qui héberge une réplique de disponibilité pour le groupe de disponibilité.

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

• 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 de média lorsque vous restaurez une base de données à partir d’une sauvegarde

Symptoms

Messages d’erreur qui indiquent un problème de fichier pointent généralement vers un fichier de sauvegarde endommagé. Les erreurs suivantes sont des exemples de problèmes que vous pouvez rencontrer si un jeu de sauvegarde est endommagé :

3241 : La famille de supports du périphérique « %ls » est mal formée. SQL Server ne peut pas traiter cette famille de supports.

3242 : Le fichier sur le périphérique '%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.

Cause

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 en raison d’un virus ou d’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).

Résolution

• Utilisez l’instruction RESTORE HEADERONLY pour vérifier votre sauvegarde.
• Pour réduire l’occurrence de ces erreurs de restauration, 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 Les erreurs de média possibles lors de la sauvegarde et de 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 Configuration du serveur : somme de contrôle de sauvegarde par défaut.
• Pour résoudre ces problèmes, recherchez un autre fichier de sauvegarde utilisable ou créez 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é.
• 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. Vérifiez si le fichier est modifié pendant l’opération de copie sur le réseau ou sur l’appareil de stockage de destination.

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

Symptoms

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 serveur <Nom du 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 consigné dans l’historique du travail ayant échoué, et ressemblant à 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.
  

Cause

L'un ou l'autre scénario 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 s’exécuter dans le cadre d’une étape de travail ou manuellement à partir de SQL Server Management Studio. Dans les deux cas, ils s’exécutent 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 obtenez les messages d’erreur notés précédemment.

Résolution

Vérifiez les autorisations actuelles du compte de service SQL Server sur un dossier en accédant à l’onglet Sécurité dans les propriétés du dossier, en sélectionnant Avancé, puis en utilisant l’onglet Accès effectif. Pour plus d’informations, consultez Périphériques de sauvegarde.

Échec des opérations de sauvegarde ou de restauration tierces

SQL Server fournit une interface de périphérique de sauvegarde virtuelle (VDI). Cette API permet aux fournisseurs de logiciels indépendants d’intégrer SQL Server dans leurs produits pour prendre en charge les opérations de sauvegarde et de restauration. Ces API sont conçues pour fournir une fiabilité et des performances, et prendre en charge la gamme complète de fonctionnalités de sauvegarde et de restauration de SQL Server, y compris les fonctionnalités de capture instantanée et de sauvegarde à chaud.

Étapes courantes de dépannage

• Dans toutes les versions prises en charge de SQL Server, une connexion nommée NT SERVICE\SQLWriter est créée et provisionnée lors de l’installation. Vérifiez que cette connexion existe dans SQL Server et fait partie du rôle serveur sysadmin sur l'instance sauvegardée. Vérifiez également que le service enregistreur VSS SQL Server est démarré et que son compte de démarrage est défini sur Système local.

• Vérifiez que SqlServerWriter figure dans la liste lorsque vous exécutez la commande VSSADMIN LIST WRITERS dans une invite de commandes élevée sur le serveur exécutant SQL Server. L’enregistreur doit être présent et dans l’état stable pour que les sauvegardes VSS se terminent correctement.

• Pour plus d’informations, consultez les journaux du logiciel de sauvegarde et le site d’assistance du fournisseur.

  Symptômes ou scénarios	Référence
  Présentation du fonctionnement de la sauvegarde VDI	Fonctionnement : SQL Server - Ressources de sauvegarde VDI (VSS)
  Nombre de bases de données pouvant être sauvegardées simultanément	Fonctionnement : combien de bases de données peuvent être sauvegardées simultanément ?

Les sauvegardes échouent lorsque le suivi des modifications est activé

Symptoms

Les sauvegardes peuvent échouer lorsque vous activez le suivi des modifications sur la base de données. Une erreur semblable à celle-ci peut s’afficher :

Erreur : 3999, Gravité : 17, État : 1.
<Horodatage> spid <spid> Échec de la vidange de la table de validation sur le disque dans dbid 8 en raison de l’erreur 2601. Consultez le journal des erreurs pour plus de détails.

Résolution

Si vous rencontrez ce problème sur une version prise en charge de SQL Server, installez la dernière mise à jour cumulative pour votre version. Pour obtenir des informations générales et consulter les correctifs historiques, voir les articles suivants :

• 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

Erreurs lors de la restauration de sauvegardes de bases de données chiffrées

Symptoms

Vous rencontrez des problèmes lorsque vous restaurez des sauvegardes de bases de données protégées par le chiffrement transparent des données (TDE).

Résolution

Pour résoudre le problème, consultez Déplacer une base de données protégée par TDE vers une autre SQL Server.

Questions fréquentes sur la sauvegarde et la restauration de SQL Server

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

Utilisez le script estimate_backup_restore pour estimer les temps de sauvegarde.

Que dois-je faire si SQL Server bascule au milieu d’une 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 sur des versions plus récentes, et vice versa ?

Vous ne pouvez pas restaurer une sauvegarde SQL Server à l'aide d'une version de SQL Server antérieure à la version qui a créé la sauvegarde. Pour plus d’informations, consultez la prise en charge de la compatibilité RESTORE.

Comment 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.

Comment sauvegarder et restaurer une base de données protégée par le chiffrement transparent des données (TDE) ?

Sauvegardez la base de données, le certificat de serveur de la clé de chiffrement de base de données et la clé privée du certificat. Pour restaurer la sauvegarde sur une autre instance, restaurez d’abord le certificat de serveur (avec sa clé privée) sur la master base de données sur l’instance cible, puis restaurez la sauvegarde de la base de données utilisateur. Pour obtenir des instructions pas à pas, consultez Déplacer une base de données protégée par TDE vers une autre SQL Server.

La compression de sauvegarde fonctionne-t-elle sur les bases de données compatibles TDE ?

Oui. À compter de SQL Server 2016, la compression de sauvegarde fonctionne sur les bases de données compatibles TDE lorsque vous spécifiez MAXTRANSFERSIZE plus de 65536 (64 Ko) dans l’instructionBACKUP. Sans ce paramètre, la sauvegarde s’exécute non compressée même lorsque vous demandez la compression. Pour plus d’informations, consultez compression de sauvegarde.

Comment les sauvegardes VDI et VSS interagissent-elles avec les réplicas secondaires du groupe de disponibilité Always On ?

Les sauvegardes basées sur VSS (capture instantanée) effectuées via le service Sql Writer ne sont prises en charge que par rapport au réplica principal. Sur les réplicas secondaires, demandez une sauvegarde complète de type Copy-Only via le client VDI, car les sauvegardes complètes VSS sur des réplicas secondaires ne sont pas prises en charge. Pour plus d’informations, consultez Les fichiers secondaires actifs : Sauvegarde sur les réplicas secondaires (groupes de disponibilité Always On).

Conseils généraux de dépannage

• Accordez des autorisations de lecture et d’écriture au compte de service SQL Server sur le dossier dans lequel vous écrivez des sauvegardes. Pour plus d’informations, consultez Autorisations de sauvegarde.
• Vérifiez que le dossier dans lequel vous écrivez des sauvegardes a suffisamment d’espace pour vos sauvegardes de base de données. Utilisez la sp_spaceused procédure stockée pour obtenir une estimation approximative de la taille de sauvegarde d’une base de données.
• Utilisez la dernière version de SSMS pour éviter les problèmes connus liés à la configuration du plan de travail et de maintenance.
• Effectuez un test d’exécution de vos travaux pour vérifier que les sauvegardes sont créées correctement. Ajoutez 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 les bases de données système.
• Si vous constatez des échecs de sauvegarde intermittents, vérifiez si la dernière mise à jour de votre version de SQL Server résout le problème. Pour plus d’informations, consultez SQL Server versions et mises à jour.
• Pour planifier et automatiser des sauvegardes pour SQL Server éditions Express, consultez Planification et automatisation des sauvegardes de bases de données SQL Server dans SQL Server Express.

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

Le tableau suivant répertorie les rubriques à examiner pour des tâches de sauvegarde et de restauration spécifiques.

Contenu connexe

• Sauvegarder et restaurer des bases de données SQL Server
• Autorisations pour la sauvegarde
• Erreurs de support possibles pendant les opérations de sauvegarde et de restauration (SQL Server)
• Déplacer une base de données protégée par TDE vers un autre SQL Server