Modes de récupération des journaux de transaction dans SQL Server

Mise en contexte

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 stockée et les informations utilisateur.

Le fichier journal LDF contient tous les journaux de la base de données. Il ne faut jamais compter uniquement sur des fichiers journaux pour récupérer votre base de données, par exemple, pour récupérer la base de données à un certain moment, il faut le fichier journal LDF et la sauvegarde complète précédente.

Que fait le fichier journal?

  • Étant l’emplacement de stockage physique des journaux, il est possible de sauvegarder les journaux à partir du fichier journal LDF et récupérer la base de données à l'aide du fichier de sauvegarde du journal.
  • SQL Server lit le fichier journal à partir du fichier LDF à chaque lancement. Il déplacera d'abord les opérations de sauvegarde non soumises et fera ensuite avancer les opérations qui ont été soumises, mais qui n'ont pas été écrites pour garantir l'intégrité.
  • Une sauvegarde complète contient toutes les données de la base de données, mais pas tous les journaux. La sauvegarde complète ne contient que toutes les pages de données et les journaux à la fin de la base de données actuelle.

 

Sommaire

 

Types de mode de récupération

Simple Recovery Model

Lorsque vous choisissez le modèle 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 de transaction, ne laissant aucune entrée de journal à des fins de récupération d'urgence.

Dans les bases de données utilisant le modèle de récupération simple, vous ne pouvez restaurer que des sauvegardes complètes ou différentielles. Il n'est pas possible de restaurer une telle base de données à un moment donné, vous ne pouvez la restaurer qu'à l'heure exacte à laquelle 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 l'échec.

 
  • Avantage : permet des opérations de copie en bloc hautes performances. Récupère l'espace de journal pour limiter les besoins en espace.
  • Inconvénient : les modifications depuis la base de données la plus récente ou la sauvegarde différentielle doivent être refaites.

 

Full Recovery Model

Avec le modèle de récupération complète, SQL Server conserve le journal des transactions jusqu'à la prochaine sauvegarde. Cela vous permet de concevoir un plan de reprise après sinistre qui comprend une combinaison de sauvegardes de base de données complètes et différentielles conjointement avec des sauvegardes du journal des transactions.

Vous disposez de la plus grande flexibilité pour restaurer des bases de données à l'aide du modèle de récupération complète en cas de défaillance d'une base de données. En plus de conserver les modifications de données stockées dans le journal des transactions, le modèle de récupération complète vous permet de restaurer une base de données à un moment précis.

 
  • Avantage : aucune transaction n'est perdue en raison d'un fichier de données endommagé. Il peut récupérer à un moment arbitraire.
  • Inconvénient : si le journal est endommagé, les modifications depuis la dernière sauvegarde du journal doivent être refaites.

 

Bulk-Logged Recovery Model

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

 
  • Avantage : permet des opérations de copie en bloc hautes performances, un espace de journal minimal est utilisé par les opérations en bloc.
  • Inconvénient : si le journal est endommagé ou si des opérations en bloc se sont produites depuis la dernière sauvegarde du journal, les modifications depuis cette dernière sauvegarde doivent être refaites.

 

Choix d'un mode de récupération

Les modèles de récupération Full Recovery et Bulk-logged fournissent la meilleure protection des données. Ces modèles s'appuient sur le journal des transactions pour fournir une récupération complète et éviter la perte de travail dans la plus large gamme de scénarios de défaillance. Lorsque vous choisissez différentes stratégies de sauvegarde MS SQL Server, vous aurez des limitations spécifiques pour récupérer les sauvegardes SQL Server.

 

Afficher et modifier le mode de récupération

  1. Connectez-vous à l'instance appropriée du moteur de base de données SQL Server. Dans l'Explorateur d'objets, cliquez sur le nom du serveur pour développer son arborescence.
  2. Développez Bases de données, puis selon la base de données, sélectionnez une base de données utilisateur ou une base de données système.
  3. Cliquez avec le bouton droit de la souris sur la base de données, puis cliquez sur Propriétés pour ouvrir la boîte de dialogue Propriétés de la base de données.
  4. Dans le volet Sélectionner une page, cliquez sur Options.
  5. Le mode de récupération actuel s'affiche dans la zone de liste Recovery model.
  6. Pour modifier le mode de récupération, sélectionnez un autre mode dans la liste. Les choix sont Full, Bulk-logged ou Simple.

 

Voir aussi

 

Dernière modification : 18 mars 2025