Sauvegarder et restaurer des bases de données SQL Server

  • Article

S’applique à :SQL Server

Cet article décrit les avantages de la sauvegarde des bases de données SQL Server, des termes de sauvegarde et de restauration de base, et présente les stratégies de sauvegarde et de restauration pour SQL Server et les considérations de sécurité relatives à la sauvegarde et à la restauration SQL Server.

Cet article présente les sauvegardes SQL Server. Pour découvrir la procédure spécifique de sauvegarde des bases de données SQL Server, consultez Création de sauvegardes.

Le composant Sauvegarde et restauration SQL Server apporte une sécurité essentielle pour la protection des données critiques stockées dans vos bases de données SQL Server. Pour réduire le risque de perte catastrophique de données, vous devez sauvegarder vos bases de données pour conserver les modifications apportées à vos données de façon régulière. Une stratégie de sauvegarde et de restauration correctement planifiée permet de protéger les bases de données contre la perte de données provoquée par différentes défaillances. Testez votre stratégie en restaurant un ensemble de sauvegardes, puis en récupérant votre base de données pour vous préparer à réagir efficacement en cas de sinistre.

Outre le stockage local pour le stockage des sauvegardes, SQL Server prend également en charge la sauvegarde et la restauration à partir de Stockage Blob Azure. Pour plus d’informations, consultez Sauvegarde et restauration SQL Server avec Stockage Blob Microsoft Azure. Pour les fichiers de base de données stockés à l’aide de Stockage Blob Azure, SQL Server 2016 (13.x) offre la possibilité d’utiliser des instantané Azure pour des sauvegardes quasi instantanées et des restaurations plus rapides. Pour plus d’informations, consultez Sauvegarde d’instantanés de fichiers pour les fichiers de base de données dans Azure. Azure offre également une solution de sauvegarde de classe entreprise pour les instances SQL Server s’exécutant sur des machines virtuelles Azure. Une solution de sauvegarde entièrement managée prend en charge les groupes de disponibilité Always On, la rétention à long terme, la récupération à un point dans le temps et la gestion centralisée et la supervision. Pour plus d’informations, voir Sauvegarde Azure pour SQL Server dans une machine virtuelle Azure.

Pourquoi sauvegarder ?

  • La sauvegarde de vos bases de données SQL Server, l’exécution de procédures de restauration de test sur vos sauvegardes et le stockage de copies de sauvegardes dans un emplacement sûr et hors site vous protège contre la perte de données potentiellement catastrophique. La sauvegarde est le seul moyen de protéger vos données.

    Avec des sauvegardes valides d'une base de données, vous pouvez récupérer vos données suite à de nombreuses défaillances, par exemple :

    • défaillance du support ;
    • erreurs utilisateur, telles que la suppression d'une table par inadvertance ;
    • défaillances matérielles, telles qu'un lecteur de disque endommagé ou la perte permanente d'un serveur ;
    • Catastrophes naturelles. En utilisant la sauvegarde SQL Server pour Stockage Blob Azure, vous pouvez créer une sauvegarde hors site dans une autre région que votre emplacement local, à utiliser en cas de sinistre naturel affectant votre emplacement local.

  • En outre, les sauvegardes d’une base de données sont utiles à des fins administratives courantes, telles que la copie d’une base de données d’un serveur vers un autre, la configuration des groupes de disponibilité Always On ou des miroir de base de données et l’archivage.

Glossaire des termes de sauvegarde

sauvegarder [verbe]
Processus de création d’une sauvegarde [nom] en copiant des enregistrements de données à partir d’une base de données SQL Server ou des enregistrements de journal à partir de son journal des transactions.

sauvegarde [nom]
Copie de données qui peut être utilisée pour restaurer et récupérer les données après une défaillance. Les sauvegardes d'une base de données peuvent également être utilisées pour restaurer une copie de la base de données à un nouvel emplacement.

unité de sauvegarde
Unité de disque ou de bande sur laquelle les sauvegardes de SQL Server sont écrites et à partir de laquelle elles peuvent être restaurées. Les sauvegardes SQL Server peuvent également être écrites dans une Stockage Blob Azure, et le format d’URL est utilisé pour spécifier la destination et le nom du fichier de sauvegarde. Pour plus d’informations, consultez Sauvegarde et restauration SQL Server avec Stockage Blob Microsoft Azure.

support de sauvegarde
Une ou plusieurs bandes ou un ou plusieurs fichiers sur disque sur lesquels une ou plusieurs sauvegardes ont été écrites.

sauvegarde de données
Sauvegarde de données dans une base de données complète (sauvegarde de base de données), une base de données partielle (sauvegarde partielle) ou un ensemble de fichiers ou groupes de fichiers (sauvegarde de fichiers).

sauvegarde de base de données
Sauvegarde d'une base de données. Les sauvegardes complètes de base de données représentent l'intégralité de la base de données à l'issue de l'opération de sauvegarde. Les sauvegardes différentielles contiennent uniquement les modifications apportées à la base de données depuis sa plus récente sauvegarde complète de base de données.

sauvegarde différentielle
Sauvegarde de données basée sur la dernière sauvegarde complète d'une base de données complète ou partielle ou d'un ensemble de fichiers de données ou de groupes de fichiers (base différentielle) et qui contient uniquement les données ayant changé depuis cette base.

sauvegarde complète
Sauvegarde de données qui contient toutes les données d'une base de données particulière ou d'un jeu de groupes de fichiers ou de fichiers, ainsi qu'une partie suffisante du journal pour permettre la récupération de ces données.

sauvegarde de fichier journal
Sauvegarde des journaux des transactions qui inclut tous les enregistrements des journaux qui n'ont pas été sauvegardés lors d'une sauvegarde de fichier journal précédente. (mode de récupération complète)

recover
Pour rétablir une base de données à un état stable et cohérent.

recovery
Phase de démarrage de base de données ou de restauration avec récupération qui place la base de données dans un état cohérent au niveau des transactions.

mode de récupération
Propriété de base de données qui contrôle la maintenance du journal des transactions sur une base de données. Il existe trois modes de récupération : simple, complète et utilisant les journaux de transactions. Le mode de récupération de base de données détermine les spécifications de sauvegarde et de restauration.

restore
Processus multiphase qui copie toutes les pages de données et de journaux d’une sauvegarde SQL Server spécifiée vers une base de données spécifiée, puis transfère toutes les transactions enregistrées dans la sauvegarde en appliquant des modifications journalisées pour transférer les données dans le temps.

Stratégies de sauvegarde et de restauration

Les sauvegardes et restaurations de données doivent être adaptées à un environnement particulier et doivent pouvoir utiliser les ressources disponibles. Pour qu’elles soient efficaces, les sauvegardes et restaurations aux fins de récupération doivent par conséquent faire l’objet d’une stratégie. Une stratégie de sauvegarde et de restauration bien conçue doit équilibrer les besoins spécifiques de l’entreprise en matière de disponibilité maximale et de perte minimale des données, tout en prenant en compte le coût de maintenance et de stockage des sauvegardes.

Une stratégie de sauvegarde et de restauration s'articule autour de deux pôles : la sauvegarde et la restauration. Le pôle sauvegarde de la stratégie définit le type et la fréquence des sauvegardes, la nature et la vitesse du matériel employé, la manière dont les sauvegardes sont testées, ainsi que les modalités et l’emplacement de stockage des supports de sauvegarde (sans oublier les questions de sécurité). Le pôle restauration détermine le responsable des restaurations et leurs modalités d’exécution pour atteindre les objectifs de l’entreprise en termes de disponibilité de la base de données, de limitation des pertes de données et de test des restaurations.

La conception d'une stratégie de sauvegarde et de restauration efficace nécessite une planification, une mise en œuvre et des tests rigoureux. Des tests sont nécessaires : vous ne disposez d’aucune stratégie de sauvegarde tant que vous n’avez pas restauré comme il se doit les sauvegardes dans toutes les combinaisons incluses dans votre stratégie de restauration et testé la cohérence physique de la base de données restaurée. Vous devez prendre en compte différents facteurs. Il s’agit notamment des paramètres suivants :

  • les objectifs de votre entreprise en ce qui concerne les bases de données de production, en particulier les besoins de disponibilité et de protection des données contre les pertes ou les sinistres ;

  • la nature de chaque base de données : sa taille, ses modèles d’utilisation, la nature de son contenu, les exigences au niveau des données, etc. ;

  • les contraintes imposées aux ressources telles que : matériel, personnel, espace de stockage des supports de sauvegarde et leur sécurité physique, etc.

Recommandations de bonnes pratiques

Les comptes qui effectuent des opérations de sauvegarde ou de restauration ne doivent pas recevoir plus de privilèges que nécessaire. Passez en revue la sauvegarde et la restauration pour obtenir des détails d’autorisation spécifiques. Il est recommandé que les sauvegardes soient chiffrées et, si possible, compressées.

Pour garantir la sécurité, les fichiers de sauvegarde doivent avoir des extensions qui suivent les conventions appropriées :

  • Les fichiers de sauvegarde de base de données doivent avoir l’extension .BAK
  • Les fichiers de sauvegarde de journaux doivent avoir l’extension .TRN .

Utiliser un stockage distinct

Important

Veillez à placer vos sauvegardes de bases de données sur un appareil ou à un emplacement physique distinct des fichiers de base de données. Quand votre lecteur physique qui stocke les bases de données ne fonctionne pas ou plante, la récupération dépend de la possibilité d’accéder à un lecteur distinct ou à un appareil distant qui stocke les sauvegardes afin de procéder à une restauration. Gardez à l’esprit que vous pouvez créer plusieurs partitions ou volumes logiques à partir d’un même lecteur de disque physique. Étudiez attentivement la disposition des partitions de disque et des volumes logiques avant de choisir un emplacement de stockage pour les sauvegardes.

Choisir le mode de récupération approprié

Les opérations de sauvegarde et de restauration interviennent dans le cadre d’un mode de récupération. Un mode de récupération désigne une propriété de base de données qui contrôle le mode de gestion du journal des transactions. Ainsi, le mode de récupération d’une base de données détermine les types de sauvegardes et les scénarios de restauration pris en charge pour la base de données ainsi qu’une estimation de la taille des sauvegardes des journaux des transactions. En règle générale, une base de données utilise le mode de récupération complète ou le mode de récupération simple. Le mode de récupération complète peut être augmenté en basculant vers le mode de récupération utilisant les journaux de transactions avant d’effectuer des opérations en bloc. Pour obtenir une présentation de ces modes de récupération et leur impact sur la gestion du journal des transactions, consultez Journal des transactions (SQL Server)

Le meilleur choix du mode de récupération de la base de données dépend de vos exigences d'entreprise. Pour éviter la gestion du journal des transactions et simplifier la sauvegarde et la restauration, optez pour le mode de récupération simple. Pour minimiser les risques de perte de travail, mais avec un coût en termes de charges d'administration, choisissez le mode de récupération complète. Pour réduire l’impact sur la taille du journal pendant les opérations journalisées en bloc tout en autorisant la récupération de ces opérations, utilisez le mode de récupération utilisant les journaux de transactions. Pour plus d’informations sur l’effet des modèles de récupération sur la sauvegarde et la restauration, consultez Vue d’ensemble de la sauvegarde (SQL Server).

Concevoir votre stratégie de sauvegarde

Après avoir choisi un mode de récupération satisfaisant vos exigences d'entreprise pour une base de données précise, vous devez planifier et implémenter une stratégie de sauvegarde correspondante. Le choix de la meilleure stratégie de sauvegarde possible dépend d'un éventail de facteurs, notamment des facteurs primordiaux suivants :

  • Combien d'heures par jour les applications ont-elles besoin d'accéder à la base de données ?

    S'il existe une période creuse évidente, nous vous recommandons de planifier les sauvegardes complètes de la base de données pendant cette période.

  • Quelle est la fréquence de modification et de mise à jour possible ?

    Si les modifications sont fréquentes, tenez compte des éléments suivants :

    • Dans le cadre du mode de récupération simple, planifiez, si possible, des sauvegardes différentielles entre les sauvegardes complètes de la base de données. Une sauvegarde différentielle enregistre uniquement les modifications effectuées depuis la toute dernière sauvegarde complète de la base de données.

    • Si vous travaillez en mode de récupération complète, prévoyez des sauvegardes fréquentes du journal. La planification de sauvegardes différentielles entre des sauvegardes complètes peut réduire le temps de restauration en diminuant le nombre de sauvegardes de fichier journal à restaurer après la restauration des données.

  • Ces modifications risquent-elles de porter sur une petite ou sur une grande partie de la base de données ?

    Pour une base de données volumineuse dans laquelle les modifications sont concentrées dans une partie des fichiers ou groupes de fichiers, des sauvegardes partielles et ou des sauvegardes complètes de fichiers peuvent être utiles. Pour plus d’informations, consultez Sauvegardes partielles (SQL Server) et Sauvegardes de fichiers complètes (SQL Server).

  • Quelle est la quantité d'espace disque nécessaire pour une sauvegarde complète de base de données ?

  • Pendant combien de temps votre entreprise doit-elle conserver les sauvegardes ?

    Vérifiez que vous disposez d’une planification de sauvegarde appropriée établie en fonction des besoins de l’application et des exigences de l’entreprise. À mesure que les sauvegardes vieillissent, le risque de perte de données est plus élevé, sauf si vous avez la possibilité de régénérer toutes les données jusqu’au point de défaillance. Avant de choisir de supprimer les anciennes sauvegardes en raison des limitations de ressources de stockage, voyez si la récupération est nécessaire aussi loin dans le passé

Estimer la taille d’une sauvegarde complète de base de données

Avant de mettre en place une stratégie de sauvegarde et de restauration, vous devez estimer la quantité d'espace disque qu'utilisera une sauvegarde complète de base de données. L'opération de sauvegarde copie les données de la base de données dans un fichier de sauvegarde. La sauvegarde contient uniquement les données réelles de la base de données et aucun espace inutilisé. Ainsi, la sauvegarde est généralement moins volumineuse que la base de données elle-même. Vous pouvez estimer la taille d’une sauvegarde complète de base de données en utilisant la procédure stockée système sp_spaceused . Pour plus d’informations, consultez sp_spaceused (Transact-SQL).

Planifier des sauvegardes

Les opérations de sauvegarde ayant peu d'impact sur l'exécution des transactions, elles peuvent donc avoir lieu en même temps que les opérations normales. Vous pouvez effectuer une sauvegarde SQL Server avec un effet minimal sur les charges de travail de production.

Pour plus d’informations sur les restrictions de concurrence lors de la sauvegarde, consultez Vue d’ensemble de la sauvegarde (SQL Server).

Après avoir choisi les types de sauvegardes dont vous avez besoin et défini la fréquence à laquelle vous devez effectuer chaque type, nous vous recommandons de prévoir des sauvegardes régulières dans le cadre d'un plan de maintenance de la base de données. Pour plus d'informations sur les plans de maintenance et leur création pour les sauvegardes de base de données et de journaux, consultez Use the Maintenance Plan Wizard.

Évaluer vos sauvegardes

Vous ne disposez d'aucune stratégie de restauration tant que vous n'avez pas testé vos sauvegardes. Il est essentiel de procéder à une évaluation minutieuse de votre stratégie de sauvegarde pour chacune de vos bases de données en restaurant une copie de la base de données sur un système de test. Vous devez tester la restauration de chaque type de sauvegarde que vous envisagez d'utiliser. Une fois que vous avez restauré la sauvegarde, il est également recommandé d’effectuer des vérifications de cohérence de la base de données via DBCC CHECKDB de la base de données pour valider que le support de sauvegarde n’a pas été endommagé.

Vérifier la stabilité et la cohérence des supports

Utilisez les options de vérification fournies par les utilitaires de sauvegarde (commande BACKUP T-SQL, plans de maintenance SQL Server, logiciel de sauvegarde ou solution, etc.). Pour obtenir un exemple, consultez [RESTORE VERIFYONLY] (../t-sql/statements/restore-statements-verifyonly-transact-sql.md). Utilisez des fonctionnalités avancées telles que BACKUP CHECKSUM pour détecter les problèmes liés au support de sauvegarde lui-même. Pour plus d’informations, consultez Erreurs de support possibles pendant les opérations de sauvegarde et de restauration (SQL Server).

Stratégie de sauvegarde/restauration de documents

Nous vous recommandons de documenter vos procédures de sauvegarde et de restauration, sans oublier de conserver une copie de la documentation dans votre dossier d'exploitation. Nous vous recommandons également la tenue d’un manuel des opérations pour chaque base de données. Ce manuel doit consigner l'emplacement des sauvegardes, les noms des unités de sauvegarde (le cas échéant) et le temps requis pour la restauration des sauvegardes de test.

Surveiller la progression avec un xEvent

Les opérations de sauvegarde et de restauration peuvent prendre beaucoup de temps en raison de la taille de la base de données et de la complexité des opérations impliquées. En cas de problème avec l’une ou l’autre des opérations, vous pouvez utiliser l’événement étendu backup_restore_progress_trace pour surveiller la progression en direct. Pour plus d'informations sur les événements étendus, consultez Événements étendus.

Avertissement

L’utilisation de l’événement étendu backup_restore_progress_trace peut impacter les performances et consommer une grande quantité d’espace disque. Utilisez-le sur de courtes périodes, avec précaution, et effectuez des tests avant l’implémentation en production.

-- Create the backup_restore_progress_trace extended event esssion
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file(SET filename=N'BackupRestoreTrace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

-- Start the event session  
ALTER EVENT SESSION [BackupRestoreTrace]  
ON SERVER  
STATE = start;  
GO  

-- Stop the event session  
ALTER EVENT SESSION [BackupRestoreTrace]  
ON SERVER  
STATE = stop;  
GO

Exemple de sortie d’un événement étendu

Example of back up xevent outputExample of restore xevent output

En savoir plus sur les tâches de sauvegarde

Utilisation des unités et supports de sauvegarde

Création de sauvegardes

Remarque

Pour les sauvegardes partielles ou de copie uniquement, vous devez utiliser l’instruction Transact-SQLBACKUP avec l’option PARTIAL ou COPY_ONLY, respectivement.

Utilisation de SSMS

Utilisation de T-SQL

Restaurer des sauvegardes de données

Utilisation de SSMS

Utilisation de T-SQL

Restaurer des journaux de transactions (mode de récupération complète)

Utilisation de SSMS

Utilisation de T-SQL

Informations et ressources supplémentaires

Backup Overview (SQL Server)
Vue d'ensemble de la restauration et de la récupération (SQL Server)
BACKUP (Transact-SQL)
RESTORE (Transact-SQL)
Sauvegarde et restauration de bases de données Analysis Services
Sauvegarder et restaurer des catalogues et des index de recherche en texte intégral
Sauvegarder et restaurer des bases de données répliquées
Journal des transactions (SQL Server)
Modes de récupération (SQL Server)
Jeux de supports, familles de supports et jeux de sauvegarde (SQL Server)