Sauvegarde et restauration des bases de données SQL Server

Article
10/16/2013

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

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 régulièrement vos bases de données pour conserver les modifications apportées à vos données. Une stratégie de sauvegarde et de restauration correctement planifiée permet de protéger les bases de données contre toute perte de données provoquée par différentes défaillances. Vous pouvez tester 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.

[!REMARQUE]

À compter de la mise à jour cumulative 2 de SQL Server 2012 SP1, la sauvegarde SQL Server vers le service de stockage d'objets blob Windows Azure est prise en charge. Pour plus d'informations, consultez Améliorations de la sauvegarde et de la restauration et Sauvegarde et restauration SQL Server avec le service de stockage d'objets blob Windows Azure.

Pour connaître les solutions aux problèmes courants que rencontrent les clients avec la Base de données SQL Windows Azure, avec SQL Server Data Tools et avec SQL Server Management Studio, consultez Gestion du cycle de vie de base de données.

Dans cette rubrique :

Avantages
Composants et concepts
Présentation des stratégies de sauvegarde et de restauration
Tâches associées

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.
Par ailleurs, il peut être utile d'effectuer des sauvegardes d'une base de données afin d'effectuer des tâches administratives de routine, telles que la copie d'une base de données d'un serveur vers un autre, la configuration de Groupes de disponibilité AlwaysOn ou la mise en miroir de bases de données et l'archivage.

[Haut de la page]

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.
support de sauvegarde
Une ou plusieurs bandes ou un ou plusieurs fichiers disque sur lesquels une ou plusieurs sauvegarde 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)
récupérer
Pour rétablir une base de données à un état stable et cohérent.
récupération
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.

[Haut de la page]

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 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. Il s'agit des facteurs suivants :

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.

[!REMARQUE]

Le format de stockage sur disque de 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.

[Haut de la page]

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 restauration complète ou le mode de récupération simple. Le mode de restauration 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 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 commerciales. 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 restauration complète. Pour plus d'informations sur l'impact des modes 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 commerciales 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 restauration 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 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 Estimation de 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.

[!REMARQUE]

Pour plus d'informations sur les restrictions d'accès concurrentiel lors d'une 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 Utiliser l'Assistant Plan de maintenance.

É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

[!REMARQUE]

Pour réaliser des sauvegardes partielles ou de copie uniquement, utilisez l'instruction Transact-SQL BACKUP avec l'option PARTIAL ou COPY_ONLY, respectivement.

Utilisation de SQL Server Management Studio

Utilisation de Transact-SQL

[Haut de la page]