Partager via

Sauvegarder et restaurer des bases de données SQL Server

Cette rubrique 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.

Le composant de sauvegarde et de restauration SQL Server fournit une protection essentielle pour protéger les données critiques stockées dans vos bases de données SQL Server. Pour réduire le risque de perte de données catastrophique, vous devez sauvegarder vos bases de données pour conserver régulièrement les modifications apportées à vos données. Une stratégie de sauvegarde et de restauration bien planifiée permet de protéger les bases de données contre la perte de données causée par diverses 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épondre efficacement à un sinistre.

Outre le stockage local pour le stockage des sauvegardes, SQL Server prend également en charge la sauvegarde et la restauration à partir du service stockage Blob Azure. Pour plus d’informations, consultez Sauvegarde et restauration SQL Server avec le service Stockage Blob Azure.

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 de copies de sauvegardes dans un emplacement sûr et hors site vous protège contre la perte de données potentiellement catastrophique.

    Important

    Il s’agit du seul moyen 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 à partir de nombreuses défaillances, telles que :

    • Échec du média.

    • Erreurs utilisateur, par exemple, la suppression d’une table par erreur.

    • Défaillances matérielles, par exemple, un lecteur de disque endommagé ou une perte permanente d’un serveur.

    • Catastrophes naturelles. En utilisant la sauvegarde SQL Server vers le service 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 de groupes de disponibilité Always On 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 vers un appareil de sauvegarde, tel qu’un disque, pour créer une sauvegarde de données ou une sauvegarde de journal.

sauvegarde [nom]
Copie des données qui peuvent être utilisées 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 vers un nouvel emplacement.

unité de sauvegarde
Un disque ou un périphérique sur bande sur lequel les sauvegardes 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, consultez Sauvegarde et restauration SQL Server avec le service Stockage Blob Azure.

support de sauvegarde
Une ou plusieurs bandes ou fichiers de disque dans 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), base de données partielle (sauvegarde partielle) ou ensemble de fichiers ou de 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’ensemble de la base de données au moment de la fin de la sauvegarde. Les sauvegardes différentielles de base de données contiennent uniquement des modifications apportées à la base de données depuis sa dernière 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 qui ont changé depuis cette base.

sauvegarde complète
Sauvegarde de données qui contient toutes les données d’une base de données ou d’un ensemble spécifique de groupes de fichiers ou de fichiers, ainsi que suffisamment de journaux pour permettre la récupération de ces données.

sauvegarde des journaux
Sauvegarde des journaux de transactions qui inclut tous les enregistrements de journal qui n’ont pas été sauvegardés dans une sauvegarde de journal précédente. (modèle de récupération complète)

guérir
Pour retourner une base de données à un état stable et cohérent.

récupération
Phase de démarrage de la base de données ou d’une restauration avec récupération qui met la base de données dans un état cohérent avec les transactions.

Modèle 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. Trois modèles de récupération existent : simple, complet et journalisé en bloc. Le modèle de récupération de base de données détermine ses besoins en matière de sauvegarde et de restauration.

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

Présentation des stratégies de sauvegarde et de restauration

La sauvegarde et la restauration des données doivent être personnalisées dans un environnement particulier et doivent utiliser les ressources disponibles. Par conséquent, une utilisation fiable de la sauvegarde et de la restauration pour la récupération nécessite une stratégie de sauvegarde et de restauration. Une stratégie de sauvegarde et de restauration bien conçue optimise la disponibilité des données et réduit la perte de données, tout en tenant compte de vos besoins métier particuliers.

Important

Placez la base de données et les sauvegardes sur des appareils distincts. Sinon, si l’appareil contenant la base de données échoue, vos sauvegardes ne seront pas disponibles. Le placement des données et des sauvegardes sur des appareils distincts améliore également les performances des E/S pour l’écriture de sauvegardes et l’utilisation de production de la base de données.

Une stratégie de sauvegarde et de restauration contient une partie de sauvegarde et une partie de restauration. La partie sauvegarde de la stratégie définit le type et la fréquence des sauvegardes, la nature et la vitesse du matériel requis pour eux, la façon dont les sauvegardes doivent être testées et où et comment le support de sauvegarde doit être stocké (y compris les considérations de sécurité). La partie restauration de la stratégie définit qui est responsable de l’exécution des restaurations et de la façon dont les restaurations doivent être effectuées pour atteindre vos objectifs de disponibilité de la base de données et pour réduire la perte de données. Nous vous recommandons de documenter vos procédures de sauvegarde et de restauration et de conserver une copie de la documentation dans votre run book.

La conception d’une stratégie efficace de sauvegarde et de restauration nécessite une planification, une implémentation et des tests minutieux. Les tests sont requis. Vous n’avez pas de stratégie de sauvegarde tant que vous n’avez pas restauré correctement les sauvegardes dans toutes les combinaisons incluses dans votre stratégie de restauration. Vous devez tenir compte de divers facteurs. Ces options en question sont les suivantes :

  • Les objectifs de production de votre organisation pour les bases de données, en particulier les exigences en matière de disponibilité et de protection des données contre la perte.

  • La nature de chacune de vos bases de données : sa taille, ses modèles d’utilisation, la nature de son contenu, les exigences de ses données, et ainsi de suite.

  • Contraintes sur les ressources, telles que le matériel, le personnel, l’espace pour le stockage du support de sauvegarde, la sécurité physique du support stocké, etc.

    Remarque

    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 dans des 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 modèle de récupération sur la sauvegarde et la restauration

Les opérations de sauvegarde et de restauration se produisent dans le contexte d’un modèle de récupération. Un modèle de récupération est une propriété de base de données qui contrôle la façon dont le journal des transactions est géré. En outre, le modèle 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 modèle de récupération simple ou le modèle de récupération complète. Le modèle de récupération complète peut être complété en basculant vers le modèle de récupération par journalisation en charges avant les opérations par lots. Pour une présentation de ces modèles de récupération et leur impact sur la gestion des journaux de transactions, consultez le journal des transactions (SQL Server).

Le meilleur choix de modèle de récupération pour la base de données dépend de vos besoins métier. Pour éviter la gestion des journaux de transactions et simplifier la sauvegarde et la restauration, utilisez le modèle de récupération simple. Pour réduire l’exposition à la perte de travail, au coût de la surcharge administrative, utilisez le modèle 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

Une fois que vous avez sélectionné un modèle de récupération qui répond à vos besoins métier pour une base de données spécifique, vous devez planifier et implémenter une stratégie de sauvegarde correspondante. La stratégie de sauvegarde optimale dépend de divers facteurs dont les éléments suivants sont particulièrement importants :

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

    S’il existe une période creuse prévisible, nous vous recommandons de planifier des sauvegardes complètes de base de données pour cette période.

  • Quelle est la fréquence des modifications et des mises à jour susceptibles de se produire ?

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

    • Sous le modèle de récupération simple, envisagez de planifier des sauvegardes différentielles entre les sauvegardes complètes de base de données. Une sauvegarde différentielle capture uniquement les modifications depuis la dernière sauvegarde complète de la base de données.

    • Sous le modèle de récupération complète, vous devez planifier des sauvegardes de journaux fréquentes. 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.

  • Les modifications sont-elles susceptibles de se produire dans une petite partie de la base de données ou dans 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 de fichiers peuvent être utiles. Pour plus d’informations, consultez Sauvegardes partielles (SQL Server) et Sauvegardes de fichiers complètes (SQL Server).

  • Combien d’espace disque une sauvegarde complète de la base de données nécessite-t-elle ?

    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 d’implémenter une stratégie de sauvegarde et de restauration, vous devez estimer la quantité d’espace disque qu’une sauvegarde complète de base de données utilisera. L’opération de sauvegarde copie les données de la base de données dans le fichier de sauvegarde. La sauvegarde contient uniquement les données réelles de la base de données et non pas d’espace inutilisé. Par conséquent, la sauvegarde est généralement plus petite que la base de données elle-même. Vous pouvez estimer la taille d’une sauvegarde complète de base de données à l’aide de la procédure stockée système sp_spaceused . Pour plus d’informations, consultez sp_spaceused (Transact-SQL).

Planifier des sauvegardes

L’exécution d’une opération de sauvegarde a un effet minimal sur les transactions en cours d’exécution ; par conséquent, les opérations de sauvegarde peuvent être exécutées pendant les opérations régulières. Vous pouvez effectuer une sauvegarde SQL Server avec un effet minimal sur les charges de travail de production.

Remarque

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 décidé quels types de sauvegardes vous avez besoin et la fréquence à laquelle vous devez effectuer chaque type, nous vous recommandons de planifier des sauvegardes régulières dans le cadre d’un plan de maintenance de base de données pour la base de données. Pour plus d’informations sur les plans de maintenance et sur la façon de les créer pour les sauvegardes de base de données et les sauvegardes de journaux, consultez l’Assistant Utilisation du plan de maintenance.

Tester vos sauvegardes

Vous n’avez pas de stratégie de restauration tant que vous n’avez pas testé vos sauvegardes. Il est très important de tester soigneusement 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 de conserver un manuel des opérations pour chaque base de données. Ce manuel des opérations doit documenter l’emplacement des sauvegardes, des noms d’appareils de sauvegarde (le cas échéant) et la durée nécessaire pour restaurer les sauvegardes de test.

Tâches associées

Planification des travaux de sauvegarde

Utilisation des périphériques de sauvegarde et du support 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 SQL Server Management Studio

Utilisation de Transact-SQL

Restauration des sauvegardes de données

Utilisation de SQL Server Management Studio

Utilisation de Transact-SQL

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

Utilisation de SQL Server Management Studio

Utilisation de Transact-SQL

Tâches de restauration supplémentaires

Utilisation de Transact-SQL

Voir aussi

Vue d’ensemble de la sauvegarde (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 des bases de données Analysis Services
Sauvegarder et restaurer des catalogues et des index Full-Text
Sauvegarder et restaurer des bases de données répliquées
Journal des transactions (SQL Server)
Modes de récupération (SQL Server)
Ensembles de médias, familles de supports et ensembles de sauvegarde (SQL Server)

  • Last updated on