Partager via

Sauvegarder et restaurer des bases de données SQL Server

S’applique à :SQL Server

Cet article décrit les avantages de la sauvegarde des bases de données SQL Server, décrit les 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.

En plus du stockage local pour stocker les 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 du Stockage Blob Azure, SQL Server 2016 (13.x) offre la possibilité d’utiliser des instantanés Azure pour obtenir des sauvegardes quasi instantanées et des restaurations plus rapides. Pour plus d’informations, consultez Sauvegardes 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. Solution de sauvegarde entièrement managée, elle prend en charge les groupes de disponibilité Always On, la rétention à long terme, la récupération jusqu`à une date et heure, ainsi que des fonctions de gestion et de surveillance centralisées. Pour plus d’informations, consultez À propos de la sauvegarde SQL Server sur des machines virtuelles 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 des copies des sauvegardes à un emplacement sécurisé et hors site permettent de vous protéger contre une éventuelle perte catastrophique de données. 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. Utilisez Sauvegarde SQL Server dans le 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.

  • 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é Always On ou la mise en miroir de bases 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 d’enregistrements de journal à partir de son journal des transactions.

backup [nom]
Copie des données que vous pouvez utiliser 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 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 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).

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 modèles de récupération : simple, complet et journalisé en bloc. Le modèle de récupération d’une base de données détermine ses besoins en matière 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. 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 équilibre les exigences métier pour la disponibilité maximale des données et la perte minimale de données tout en tenant compte du 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. 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 dont ils ont besoin, 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, comment les restaurations doivent être effectuées pour atteindre vos objectifs de disponibilité de la base de données et réduire la perte de données et comment les restaurations sont testées.

La conception d'une stratégie de sauvegarde et de restauration efficace nécessite une planification, une mise en œuvre et des tests rigoureux. 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 et que vous avez testé la base de données restaurée pour assurer la cohérence physique. Vous devez prendre en compte différents facteurs. Il s’agit notamment des paramètres suivants :

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

  • 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. ;

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

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. Consultez la section Sauvegarde et restauration pour plus d’informations sur les autorisations spécifiques. Il est recommandé de chiffrer les sauvegardes et, si possible, de les compresser.

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

  • Les fichiers de sauvegarde des bases de données doivent porter l’extension .BAK
  • Les fichiers de sauvegarde des journaux doivent porter 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. Par conséquent, le modèle de récupération d’une base de données détermine les types de sauvegardes et de restaurations pris en charge pour la base de données et la taille des sauvegardes du journal 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. Vous pouvez augmenter le modèle de récupération complète en basculant vers le modèle de récupération journalisé en bloc avant les opérations en bloc. 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.

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

Concevoir votre 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. 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 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 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 complètes 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 ?

  • 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 d’anciennes sauvegardes en raison des limitations des ressources de stockage, envisagez si la récupération est nécessaire jusqu’à présent.

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 à l’aide de la sp_spaceused procédure stockée système. 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, vous pouvez exécuter des opérations de sauvegarde pendant les opérations régulières. Vous pouvez effectuer une sauvegarde de SQL Server avec un effet minimal sur les charges 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.

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 également d’effectuer des vérifications de cohérence de base de données une fois la sauvegarde rétablie par le biais de DBCC CHECKDB de la base de données pour vérifier 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 (BACKUP commande T-SQL, plans de maintenance SQL Server, logiciel de sauvegarde ou solution, etc.). Pour obtenir un exemple, consultez les instructions RESTORE - VERIFYONLY.

Utilisez des fonctionnalités avancées comme BACKUP CHECKSUM pour détecter les problèmes liés au support de sauvegarde lui-même. Pour plus d’informations, consultez Les erreurs de média possibles lors de la sauvegarde et de la 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 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. Lorsque des problèmes surviennent avec l’une ou l’autre opération, vous pouvez utiliser l’événement backup_restore_progress_trace étendu pour surveiller la progression en direct. Pour plus d’informations sur les événements étendus, consultez vue d’ensemble des événements étendus.

Avertissement

L’utilisation de l’événement backup_restore_progress_trace étendu peut entraîner un problème de performances et consommer une quantité importante 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 session
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

Capture d’écran d’un exemple de sortie xevent de sauvegarde. Capture d’écran d’un exemple de sauvegarde de la sortie xevent, continue.

En savoir plus sur les tâches de sauvegarde

Utiliser des périphériques de sauvegarde et un support de sauvegarde

Créer des sauvegardes

Remarque

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

Utiliser SSMS

Utiliser T-SQL

Restaurer des sauvegardes de données

Utiliser SSMS

Utiliser T-SQL

Restaurer les journaux des transactions (modèle de récupération complète)

Utiliser SSMS

Utilisation de T-SQL

Contenu connexe

  • Last updated on