Sauvegarde et restauration des bases de données SQL Server

  • Article

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

Le composant de sauvegarde et restauration de SQL Server apporte une sécurité essentielle pour la protection des données cruciales 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.

En plus du stockage local pour stocker les sauvegardes, SQL Server prend également en charge la sauvegarde et la restauration à partir du service Stockage Blob Azure. Pour plus d’informations, voir SQL Server Backup and Restore with Azure Blob Storage Service(en anglais).

Avantages

  • 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 des copies des sauvegardes à un emplacement sécurisé et hors site permettent de vous protéger contre une éventuelle perte catastrophique de données.

    Important

    Il s’agit de la seule façon de protéger de manière fiable vos données SQL Server.

    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. Utilisez Sauvegarde SQL Server dans le service Stockage Blob Azure pour créer une sauvegarde hors site dans une autre région que celle de votre emplacement local, qui sera disponible en cas de catastrophe naturelle affectant votre emplacement local.

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

Composants et concepts

sauvegarder [verbe]
Copie les données ou les enregistrements de journal d'une base de données SQL Server ou de son journal des transactions sur une unité de sauvegarde, telle qu'un disque, pour créer une sauvegarde de données ou de journal.

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 un service de 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, voir SQL Server Backup and Restore with Azure Blob Storage Service(en anglais).

support de sauvegarde
Une ou plusieurs bandes ou un ou plusieurs fichiers 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.

restauration
Processus à plusieurs phases qui copie toutes les données et les pages des journaux à partir d'une sauvegarde SQL Server spécifiée dans une base de données spécifiée, puis restaure toutes les transactions journalisées dans la sauvegarde en appliquant les modifications journalisées pour rétablir un état ultérieur des données.

Présentation des 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 maximiser la disponibilité des données et minimiser la perte de données, en prenant en compte les besoins spécifiques de votre entreprise.

Important

Placez la base de données et les sauvegardes sur des périphériques distincts. Dans le cas contraire, si le périphérique contenant la base de données tombe en panne, vos sauvegardes ne seront pas disponibles. Le fait de placer les données et les sauvegardes sur des périphériques distincts augmente également les performances d'E/S pour l'écriture des sauvegardes et l'utilisation en production de la base de données.

Une stratégie de sauvegarde et de restauration s'articule autour de deux pôles : la sauvegarde et la restauration. Le pôle sauvegarde 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 seront testées, ainsi que les modalités et l'emplacement de stockage des supports de sauvegarde (sans oublier les considérations relatives à la 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 et de limitation des pertes de données. 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.

La conception d'une stratégie de sauvegarde et de restauration efficace nécessite une planification, une mise en œuvre et des tests rigoureux. Il est nécessaire d'effectuer des tests. 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. Vous devez prendre en compte différents facteurs. Ces options en question sont les suivantes :

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

  • la nature de chacune de vos bases de données : leur taille, les modèles d'utilisation, nature de leur contenu, 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.

    Notes

    Le format de stockage sur disque SQL Server est le même dans les environnements 64 bits et 32 bits. Par conséquent, la sauvegarde et la restauration fonctionnent sur les environnements 32 bits et 64 bits. Une sauvegarde créée sur une instance de serveur s'exécutant dans un environnement peut être restaurée sur une instance de serveur qui s'exécute dans l'autre environnement.

Impact du mode de récupération sur la sauvegarde et la restauration

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. Également, 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. 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 complété en basculant provisoirement vers le mode de récupération utilisant les journaux de transactions avant d'effectuer les opérations en bloc. Pour une présentation de ces modèles de récupération et de 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 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 la 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 ?

    Dans le cas d'une base de données volumineuse dont les modifications sont concentrées dans une partie des fichiers ou des groupes de fichiers, des sauvegardes partielles et/ou des sauvegardes de fichiers peuvent s'avérer 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 ?

    Pour plus d'informations, consultez Estimer la taille d'une sauvegarde complète de base de données plus loin dans cette section.

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 de SQL Server avec un effet minimal sur les charges de production.

Notes

Pour plus d’informations sur les restrictions d’accès concurrentiel pendant 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.

Nous vous recommandons 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.

Tâches associées

Planification des travaux de sauvegarde

Utilisation des unités et supports de sauvegarde

Création de sauvegardes

Notes

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 SQL Server Management Studio

Avec Transact-SQL

Restauration de sauvegardes de données

Utilisation de SQL Server Management Studio

Avec Transact-SQL

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

Utilisation de SQL Server Management Studio

Avec Transact-SQL

Tâches de restauration supplémentaires

Avec Transact-SQL

Voir aussi

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)