Trois Modes de récupération dans SQL Server : modèle de récupération simple, complète et utilisant les journaux de transactions

Il existe trois modèles de récupération différents pour SQL Server, vous pouvez d'abord choisir un outil de récupération ou directement un mode de récupération pour gérer les fichiers journaux et préparer la récupération de SQL en cas de désastre. Ce document traite des trois modèles de récupération de SQL Server: simple, complète et utilisant les journaux de transactions. Avant de passer à la récupération, vous devez sauvegarder votre base de données SQL Server.

Cet article présente un logiciel fiable de sauvegarde et de récupération des bases de données de SQL Server : EaseUS Todo Backup.

Informations de base sur les fichiers journaux

La base de données SQL Server contient au moins un fichier de données MDF et un fichier journal LDF. Le fichier MDF contient tous les objets et données de la base de données, tels que la table, la procédure et les informations sur l'utilisateur. Le fichier journal LDF contient tous les journaux de la base de données. Cependant, vous ne devez jamais compter uniquement sur les fichiers journaux pour récupérer votre base de données, par exemple, récupérer la base de données à un moment donné grâce au fichier journal LDF et à la dernière sauvegarde complète.

Que fait le fichier journal ?

• Comme il s'agit d'une position de stockage physique des journaux, nous pouvons sauvegarder les journaux à partir du fichier journal LDF et récupérer la base de données à l'aide du fichier de sauvegarde des journaux.
• SQL Server lira le journal à partir du fichier Ldf chaque fois qu'il est lancé. Il sauvegardera les affaires qui n'ont pas été soumises avant et reportera les affaires qui ont été soumises mais qui n'ont pas été écrites dans le fichier de données afin de garantir leur intégrité.
• Une sauvegarde complète contient toutes les données de la base de données mais n'inclut pas tous les journaux. Une sauvegarde complète contient uniquement toutes les pages de données et les journaux dans la queue de la base de données actuelle.

Un journal d'activité désigne les enregistrements des affaires en cours. Un journal virtuel fait référence à une unité de stockage logique du journal. SQL Server divise logiquement l'espace du fichier journal ldf en plusieurs morceaux. Chaque morceau est un journal virtuel. La troncature du journal signifie la suppression de l'ancien journal.

Mode de récupération simple

Lorsque vous choisissez le mode de récupération simple, SQL Server ne conserve qu'une quantité minimale d'informations dans le journal des transactions. SQL Server tronque le journal des transactions chaque fois que la base de données atteint un point de contrôle, ne laissant aucune entrée de journal non sauvegardée, afin de faciliter la récupération en cas de sinistre.

Dans les bases de données utilisant le mode de récupération simple, vous pouvez restaurer uniquement les sauvegardes complètes ou différentielles. Il n'est pas possible de restaurer une telle base de données à un moment donné dans le temps, vous pouvez seulement la restaurer au moment où une sauvegarde complète ou différentielle s'est produite. Par conséquent, vous perdrez automatiquement toutes les modifications de données effectuées entre le moment de la sauvegarde complète/différentielle la plus récente et le moment de la panne.

La restauration simple est celle qui nécessite le moins d'administration. Elle est plus facile à gérer que les modèles complets ou volumineux, mais au prix d'un risque de perte de données plus élevé si un fichier de données est endommagé. La récupération simple n'est pas un choix approprié pour les systèmes de production où la perte des changements récents est inacceptable. Lors de l'utilisation de la restauration simple, l'intervalle de sauvegarde doit être suffisamment long pour que les frais de sauvegarde n'affectent pas le travail de production, mais suffisamment court pour éviter la perte de quantités importantes de données.

• Avantage : Permet des opérations de copie en masse très performantes. Récupère l'espace du journal pour maintenir les exigences d'espace à un niveau faible.
• Inconvénient : Les modifications apportées depuis la dernière sauvegarde complète ou différentielle doivent être refaites.

Mode de récupération complete

Avec le mode de récupération complete, SQL Server préserve le journal des transactions jusqu'à ce que vous le sauvegardiez. Cela vous permet de concevoir un plan de récupération après sinistre qui inclut une combinaison de sauvegardes complètes et différentielles de la base de données en conjonction avec des sauvegardes du journal des transactions.

Vous disposez de la plus grande souplesse pour restaurer les bases de données à l'aide du mode de récupération complète lorsqu'une défaillance de la base de données se produit. En plus de préserver les modifications de données stockées dans le journal des transactions, le mode de récupération complète vous permet de restaurer base de données SQL Server à un moment précis dans le temps.

• Avantage : Rien n'est perdu en cas d'endommagement ou perte de données. Vous pouvez restaurer à un point arbitraire dans le temps.
• Inconvénient : Si le journal est endommagé, les modifications apportées depuis la dernière sauvegarde du journal doivent être refaites.

Mode de récupération utilisant les journaux de transactions

Le mode de récupération utilisant les journaux de transactions est un modèle spécial qui fonctionne de manière similaire au mode de récupération complète. La seule différence réside dans la façon dont il traite les opérations de modification des données en masse. Le modèle de journalisation en masse enregistre les opérations dans le journal des transactions à l'aide d'une technique connue sous le nom de journalisation minimale. Cela permet de réduire considérablement le temps de traitement mais vous empêche d'utiliser l'option de restauration ponctuelle.

• Avantage : Des opérations de copie en masse très performantes, et les opérations en masse utilisent un espace minimal dans le journal.
• Inconvénient : Si le journal est endommagé ou si des opérations en masse ont eu lieu depuis la dernière sauvegarde du journal, les modifications apportées depuis cette dernière sauvegarde doivent être refaites.

Conclusion

Les modes de récupération complète et utilisant les journaux de transactions offrent la meilleure protection des données. Ces modes s'appuient sur le journal des transactions pour assurer une récupération complète et empêcher la perte de travail en cas de défaillance. Le mode de récupération en masse offre des performances supérieures et une consommation d'espace de journal inférieure pour certaines opérations à grande échelle.

Selon les modes de sauvegarde MS SQL Server, vous aurez des limitations spécifiques pour récupérer les sauvegardes SQL Server.