Sauvegarder et restaurer les bases de données SQL Server

S’applique à :SQL Server

Cet article décrit les avantages de sauvegarder des bases de données SQL Server, introduit des termes de base pour sauvegarde et restauration, et aborde les stratégies de sauvegarde et restauration ainsi que les considérations de sécurité pour SQL Server.

Le composant sauvegarde et restauration de SQL Server offre une protection essentielle pour les données critiques stockées dans vos bases de données SQL Server. Pour minimiser le risque de perte catastrophique de données, sauvegardez régulièrement vos bases de données afin de préserver les modifications apportées à vos données. Une stratégie de sauvegarde et de restauration bien planifiée aide à protéger les bases de données contre la perte de données causée par de nombreux types de défaillance. Testez votre stratégie en restaurant un ensemble de sauvegardes puis en récupérant votre base de données, afin d’être prêt à intervenir en cas de catastrophe.

En plus du stockage local, SQL Server prend également en charge la sauvegarde et la restauration depuis Stockage Blob Azure. Pour plus d’informations, consultez Sauvegarde et restauration de SQL Server avec le stockage Blob 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 ?

• Sauvegarder vos bases de données SQL Server, exécuter des procédures de restauration par test sur vos sauvegardes et stocker des copies de sauvegardes dans un lieu sûr et hors site vous protège contre une 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 média.

  • 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 SQL Server Backup vers Stockage Blob Azure, vous pouvez créer une sauvegarde hors site dans une région différente de votre emplacement sur site, à utiliser si une catastrophe naturelle affecte votre localisation.

• 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

Stratégies de sauvegarde et de restauration

Vous devez personnaliser les stratégies de sauvegarde et de restauration de votre environnement et des ressources disponibles. Une récupération fiable nécessite une stratégie de sauvegarde et de restauration. Une stratégie bien conçue équilibre les exigences métier pour une disponibilité maximale des données et un minimum de perte de données avec 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. La partie sauvegarde définit le type et la fréquence des sauvegardes, le type et la vitesse du matériel nécessaire, comment tester les sauvegardes, ainsi que le lieu et la manière de stocker les supports de sauvegarde (y compris les considérations de sécurité). La partie restauration définit qui est responsable des restaurations, comment effectuer les restaurations pour atteindre vos objectifs de disponibilité de base de données et de perte minimale de données, ainsi que comment tester les restaurations.

Une stratégie efficace de sauvegarde et de restauration nécessite une planification, une mise en œuvre et des tests rigoureuses. Les tests sont requis. Vous n’avez pas de stratégie de sauvegarde tant que vous n’avez pas réussi à restaurer les sauvegardes dans toutes les combinaisons incluses dans votre stratégie de restauration et testé chaque base de données restaurée pour vérifier sa cohérence physique. Considérez plusieurs facteurs, notamment :

• 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

N’accordez pas aux comptes qui effectuent des opérations de sauvegarde ou de restauration plus de privilèges que nécessaire. Pour plus d’informations, consultez sauvegarde et restauration pour des détails précis sur les permissions. Chiffrer les sauvegardes de bases de données et, si possible, les compresser .

Utilisez des extensions de fichiers cohérentes pour faciliter l’identification et la gestion des sauvegardes. SQL Server ne nécessite ni ne fait appliquer ces extensions, mais la cohérence aide pour les tâches opérationnelles telles que la configuration des exclusions antivirus pour les fichiers de sauvegarde. Pour plus d’informations, consultez Configurer le logiciel antivirus pour qu’il fonctionne avec SQL Server.

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

Utilisez un stockage séparé

Placez vos sauvegardes de base de données dans un emplacement physique séparé ou sur un appareil distinct des fichiers de la base de données. Lorsque le disque physique qui stocke vos bases de données tombe en panne ou plante, la récupération dépend de votre capacité à accéder au disque séparé ou au périphérique distant qui a stocké les sauvegardes. Vous pouvez créer plusieurs volumes ou partitions logiques à partir du même disque dur physique. Examinez attentivement la partition du disque et la disposition 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 modèle de récupération d’une base de données détermine quels types de scénarios de sauvegarde et de restauration la base supporte, ainsi que la taille de ses sauvegardes dans les journaux de transaction. 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 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 de modèle de récupération de base de données dépend des besoins de votre 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’effet 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 modèle de récupération utilisant les journaux de transactions. Pour des informations sur l’effet des modèles de récupération sur la sauvegarde et la restauration, voir Aperçu de la sauvegarde (SQL Server).

Concevoir votre stratégie de sauvegarde

Après avoir sélectionné un modèle de récupération qui répond aux besoins de votre entreprise pour une base de données spécifique, planifiez et mettez en place une stratégie de sauvegarde correspondante. La meilleure stratégie de secours dépend de plusieurs facteurs. Les facteurs suivants sont particulièrement importants :

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

  S’il y a une période hors pointe prévisible, vous devriez programmer des sauvegardes complètes de la base de données pour cette période.

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

  Si les changements sont fréquents, considérez :

  • Dans le modèle de récupération simple, vous pouvez programmer des sauvegardes différenciées entre des 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.

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

• Des changements sont-ils susceptibles d’intervenir uniquement dans une petite partie de la base de données, ou dans une grande partie ?

  Pour une grande base de données où les modifications sont concentrées dans un sous-ensemble des fichiers ou groupes de fichiers, des sauvegardes partielles ou complètes peuvent être utiles. Pour plus d’informations, consultez Sauvegardes partielles (SQL Server) et Sauvegardes de fichiers complètes (SQL Server).

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

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

  Assurez-vous d’avoir un planning de sauvegarde approprié qui correspond aux besoins de l’application et aux exigences commerciales. À mesure que les sauvegardes vieillissent, le risque de perte de données augmente à moins que vous ne disposiez d’un moyen de régénérer toutes les données jusqu’au point de défaillance. Avant de supprimer d’anciennes sauvegardes en raison des limites de stockage, demandez-vous si vous devez pouvoir restaurer des données aussi anciennes.

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, estimez combien d’espace disque une sauvegarde complète de base de données consomme. L'opération de sauvegarde copie les données de la base de données dans un fichier de sauvegarde. La sauvegarde ne contient que les données réelles de la base de données, pas d’espace inutilisé. Ainsi, la sauvegarde est généralement moins volumineuse que la base de données elle-même. Pour estimer la taille d’une sauvegarde complète de la base de données, utilisez la sp_spaceused procédure de stockage système. Pour plus d’informations, consultez sp_spaceused.

Planifier des sauvegardes

Une opération de sauvegarde a un effet minimal sur les transactions en cours, donc vous pouvez effectuer des sauvegardes pendant les opérations normales. Vous pouvez effectuer une sauvegarde de SQL Server avec un effet minimal sur les charges de production.

Après avoir décidé des types de sauvegardes nécessaires et à quelle fréquence effectuer chaque type, planifiez des sauvegardes régulières dans le cadre d’un plan de maintenance de 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. Testez soigneusement votre stratégie de sauvegarde pour chaque base 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. Après avoir restauré la sauvegarde, exécutez DBCC CHECKDB sur la base de données afin de confirmer que le support de sauvegarde n’est pas endommagé.

Vérifier la stabilité et la cohérence des médias

Utilisez les options de vérification fournies par les utilitaires de sauvegarde (BACKUPcommande T-SQL, plans de maintenance SQL Server, votre logiciel ou solution de sauvegarde, etc.). Pour un exemple, voir RESTORE Énoncés - VERIFYONLY.

Utilisez des fonctionnalités avancées telles que BACKUP CHECKSUM, pour détecter des problèmes liés au support de sauvegarde lui-même. Pour plus d’informations, consultez Possible Media Errors During Backup and Restore (SQL Server).

Stratégie de sauvegarde/restauration de documents

Documentez vos procédures de sauvegarde et de restauration, et gardez une copie de la documentation dans votre livre de cours.

Vous devriez également tenir un manuel d’opérations pour chaque base de données. Ce manuel d’opérations doit documenter l’emplacement des sauvegardes, les noms des périphériques de sauvegarde (s’il y en a), ainsi que le temps nécessaire pour restaurer les sauvegardes de test.

Risque de sécurité de restauration des sauvegardes à partir de sources non approuvées

Cette section décrit le risque de sécurité associé à la restauration des sauvegardes à partir de sources non approuvées dans n’importe quel environnement SQL Server, notamment local, Azure SQL Managed Instance, SQL Server sur des machines virtuelles Azure et tout autre environnement.

Pourquoi c’est important

La restauration de fichiers de sauvegarde SQL (.bak) introduit un risque potentiel si la sauvegarde provient d’une source non approuvée. Le risque de sécurité est accru davantage lorsqu’un environnement SQL Server a plusieurs instances, car il agrandit la zone de menace. Bien que les sauvegardes qui restent dans une limite approuvée ne posent aucun problème de sécurité, la restauration d’une sauvegarde malveillante peut compromettre la sécurité de l’ensemble de l’environnement.

Un fichier malveillant .bak peut :

• Prenez en charge l’ensemble de l’instance SQL Server.
• Augmentez les privilèges et obtenez un accès non autorisé à l’hôte ou à la machine virtuelle sous-jacente.

Cette attaque se produit avant que des scripts de validation ou des vérifications de sécurité puissent s’exécuter, ce qui le rend extrêmement dangereux. La restauration d’une sauvegarde non approuvée équivaut à exécuter des applications non approuvées sur un serveur ou une machine virtuelle critique et à introduire une exécution de code arbitraire dans votre environnement.

Meilleures pratiques

Suivez ces bonnes pratiques de sécurité de sauvegarde pour réduire la menace pour vos environnements SQL Server :

• Traitez la restauration des sauvegardes comme une opération à haut risque.
• Réduisez la zone de service des menaces à l’aide d’instances isolées.
• Autorisez uniquement les sauvegardes approuvées : ne restaurez jamais les sauvegardes à partir de sources inconnues ou externes.
• Autorisez uniquement les sauvegardes qui sont restées dans une limite approuvée : assurez-vous que les sauvegardes proviennent de la limite approuvée.
• Ne contournez pas les contrôles de sécurité pour des raisons pratiques.
• Activez l’audit au niveau du serveur pour capturer les événements de sauvegarde et de restauration et atténuer l’évasion d’audit.

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 des opérations, utilisez l’événement backup_restore_progress_trace prolongé pour suivre l’avancement en direct. Pour plus d’informations sur les événements étendus, consultez vue d’ensemble des événements étendus.

-- 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’Événement Étendu

En savoir plus sur les tâches de sauvegarde

• Créer un plan de maintenance
• Créer un travail SQL Server Agent dans SQL Server Management Studio
• Configurer la planification d’une tâche SQL Server Agent

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

• Définir un périphérique de sauvegarde logique pour un fichier de disque (SQL Server)
• Définir un périphérique de sauvegarde logique pour un lecteur de bande (SQL Server)
• Spécifier une destination de sauvegarde sur disque ou bande (SQL Server)
• Supprimer un appareil de sauvegarde (SQL Server)
• Définir la date d’expiration d’une sauvegarde (SQL Server)
• Afficher le contenu d’une bande de sauvegarde ou d’un fichier (SQL Server)
• Afficher les fichiers de données et les fichiers de journaux dans un jeu de sauvegarde (SQL Server)
• Afficher les propriétés et le contenu d’un appareil de sauvegarde logique (SQL Server)
• Restaurer une sauvegarde à partir d’un appareil (SQL Server)

Créer des sauvegardes

Pour les sauvegardes partielles ou uniquement copie, utilisez l’instruction Transact-SQL BACKUP avec l’option PARTIAL ou COPY_ONLY , respectivement.

Utiliser SSMS

• Créer une sauvegarde complète de base de données
• Sauvegarder un journal des transactions
• Fichiers de sauvegarde et groupes de fichiers
• Créer une sauvegarde différentielle de base de données (SQL Server)

Utiliser T-SQL

• Utilisez Resource Governor pour limiter l’utilisation du processeur par compression de sauvegarde
• Sauvegarder le journal des transactions lorsque la base de données est endommagée (SQL Server)
• Activer ou désactiver les sommes de contrôle de sauvegarde pendant la sauvegarde ou la restauration (SQL Server)
• Spécifier la sauvegarde ou la restauration pour continuer ou arrêter après l’erreur

Restaurer des sauvegardes de données

Utiliser SSMS

• Restaurer une sauvegarde de base de données à l’aide de SSMS
• Restaurer une base de données à un nouvel emplacement (SQL Server)
• Restaurer une sauvegarde différentielle de base de données (SQL Server)
• Restaurer les fichiers et groupes de fichiers (SQL Server)

Utiliser T-SQL

• Restaurer une sauvegarde de base de données selon le modèle de récupération simple
• Restaurer la base de données au point de défaillance - récupération complète
• Restaurer des fichiers et groupes de fichiers sur des fichiers existants (SQL Server)
• Restaurer les fichiers à un nouvel emplacement (SQL Server)
• Restaurer la base de données principale

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

Utiliser SSMS

• Restaurer une base de données dans une transaction marquée (SQL Server Management Studio)
• Restaurer une sauvegarde du journal des transactions (SQL Server)
• Restaurer une base de données SQL Server à un moment donné (modèle de récupération complet)

Utiliser T-SQL

• Restaurer une base de données SQL Server à un moment donné (modèle de récupération complet)
• Redémarrer une opération de restauration interrompue
• Récupérer une base de données sans restaurer les données

Contenu connexe

• 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)
• Instructions RESTORE (Transact-SQL)
• Sauvegarde et restauration de bases de données Analysis Services
• Sauvegarder et restaurer les catalogues et index en texte intégral
• Sauvegarder et restaurer les bases de données répliquées
• Journal des transactions
• Modèles de récupération (SQL Server)
• Jeux de supports, familles de supports et jeux de sauvegarde (SQL Server)