Sauvegardes de journaux de transactions SQL Server : utilité et mise en œuvre

Lors de mes audits, je rencontre régulièrement des bases de production en mode de récupération FULL sans sauvegarde de journal planifiée. Ou, au contraire, les bases de production sont en mode SIMPLE, et une sauvegarde complète seule est effectuée une fois par jour, ou pire, une fois par semaine, ou encore pire, jamais, parce qu’un logiciel de sauvegarde effectue des snapshots de la VM.

Dans le pire des cas, donc, le responsable infrastructure indique que « les VM sont sauvegardées par Veeam (ou un équivalent) », ce qui semble suffisant. Ce n’est pas le cas, et cet article explique pourquoi.

Mode de récupération et comportement du journal de transactions

Le comportement du journal de transactions, et les modes de récupération, sont expliqués dans :

• cet article : Identifier et régler les problèmes de journaux de transactions.
• cette vidéo YouTube : Comprendre les problèmes du journal de transaction dans SQL Server

En résumé :

Le mode FULL est le mode par défaut pour toute nouvelle base de données. C’est le seul mode qui garantit une perte de données nulle entre deux sauvegardes complètes, à condition que des sauvegardes de journaux soient effectuées régulièrement.

En mode FULL, le journal ne se vide jamais automatiquement. Il accumule les enregistrements transactionnels jusqu’à ce qu’une commande BACKUP LOG les archive et libère l’espace. Sans cette commande, le fichier .ldf grossit en continu, quelle que soit la fréquence des sauvegardes complètes.

Ce que fait une sauvegarde de journal de transactions

La commande BACKUP LOG réalise deux opérations simultanément :

1. Elle copie les enregistrements transactionnels actifs vers un fichier .trn (extension conventionnelle).
2. Elle tronque la partie inactive du journal, libérant l’espace dans le fichier .ldf.

Ces fichiers .trn forment une chaîne de sauvegarde continue. Chaque fichier couvre une période donnée de l’activité transactionnelle de la base. Cette chaîne, appliquée après une restauration complète, permet de rejouer l’historique des transactions et de retrouver l’état exact de la base à n’importe quel instant couvert par les sauvegardes.

C’est le principe du Point-In-Time Recovery (PITR).

Les sauvegardes de VM ne remplacent pas les sauvegardes SQL Server

Une stratégie de sauvegarde basée uniquement sur des sauvegardes de VM, ou une stratégie basée uniquement sur des sauvegardes complètes une fois pas jour ou à n’importe quel intervalle, sont insuffisantes :

Pas de vidage du journal

Si vous restez en mode de récupération FULL, une sauvegarde de VM, même si elle utilise le service VSS (Volume Shadow Copy Service) pour garantir la cohérence du système de fichiers, se contente de copier les blocs du disque. Elle ne communique pas nativement avec le moteur SQL Server pour lui ordonner de tronquer son journal. Résultat : vous pouvez sauvegarder votre VM toutes les heures, votre fichier .ldf continuera de grossir jusqu’à saturation complète du disque, car SQL Server attend une sauvegarde de journal « officielle » pour libérer l’espace.

Un RPO (Recovery Point Objective) beaucoup trop élevé

Le RPO désigne la quantité de données que vous acceptez de perdre en cas de sinistre.

• Avec des sauvegardes de VM, la fréquence est généralement de 24 heures (parfois toutes les 4 ou 12 heures pour les serveurs critiques). Si votre VM est sauvegardée à minuit et qu’un crash disque survient à 16h, vous perdez 16 heures de transactions.
• Avec les sauvegardes de journaux, on descend classiquement à un intervalle de 5 ou 15 minutes. La perte de données potentielle est alors réduite au strict minimum, ce qui est l’exigence standard pour toute application métier sérieuse.

L’impossibilité du Point-In-Time Recovery (PITR)

Imaginez le scénario suivant : un utilisateur exécute par erreur un UPDATE massif sans clause WHERE à 10h42.

• Avec une sauvegarde de VM : Votre seule option est de restaurer la VM complète (ou le fichier .mdf/.ldf) telle qu’elle était à minuit. Vous perdez tout le travail de la matinée pour corriger une erreur d’une seconde.
• Avec les sauvegardes de journaux : Vous pouvez restaurer votre sauvegarde complète, puis vos journaux, dans une copie de la base de données, en indiquant à SQL Server de s’arrêter exactement à 10h41 et 59 secondes. Vous récupérez l’intégralité de vos données, moins l’erreur humaine.

Avec une chaîne de journaux intacte, il est possible de restaurer une base à n’importe quel instant couvert par cette chaîne.

Une sauvegarde de VM est une « photo » à un instant T ; la chaîne de journaux est un « film » que l’on peut rembobiner à l’image près.

Sans sauvegardes de journaux, la seule option est de revenir à la dernière sauvegarde complète, avec la perte de toutes les transactions intermédiaires.

La granularité de la restauration

Restaurer une VM complète de plusieurs téraoctets pour récupérer une seule base de données de 50 Go est une hérésie en termes d’efficacité. Le processus est lourd, nécessite souvent de l’espace disque supplémentaire conséquent pour le montage de l’image, et mobilise les ressources réseau et de stockage de l’infrastructure de virtualisation. À l’inverse, une restauration SQL native est un processus direct, optimisé par le moteur de base de données, et qui ne dépend d’aucune couche d’infrastructure externe.

L’intégrité des données (Checksums)

Lorsqu’un BACKUP DATABASE ou BACKUP LOG est exécuté avec l’option CHECKSUM (fortement recommandée), SQL Server vérifie l’intégrité des pages de données pendant la lecture. Si une corruption de page existe sur le disque, la sauvegarde échoue et vous avertit immédiatement. Une sauvegarde de VM, elle, copie aveuglément les blocs du disque, qu’ils soient sains ou corrompus. Vous pourriez ainsi sauvegarder pendant des mois une base de données corrompue sans le savoir, pour ne découvrir le désastre qu’au moment où vous aurez réellement besoin de restaurer.

La stratégie hybride

Est-ce que cela signifie qu’il faut arrêter les sauvegardes de VM ? Non. Les sauvegardes de VM sont excellentes pour la reprise après sinistre globale (DRP) : elles permettent de remonter un serveur complet (OS, configuration, instances) très rapidement sur un nouvel hôte.

Cependant, pour la protection des données, la stratégie de production standard doit combiner :

1. Sauvegarde complète (Full) : Quotidienne ou hebdomadaire (pour réinitialiser la base de restauration).
2. Sauvegarde différentielle (optionnelle) : Si sauvegarde quotidienne hebdomadaire : tous jours, ou toutes les 6 heures (pour accélérer le temps de restauration). Cette étape n’est pas nécessaire si vous effectuez une sauvegarde complète quotidienne. Cela dépend de votre RTO.
3. Sauvegarde de journal (Log) : Toutes les 5 à 15 minutes (pour le PITR et la santé du disque).
4. Sauvegarde de VM : Pour la protection du contenant (le serveur), et non du contenu (les données).

En déléguant la gestion des journaux à SQL Server, vous garantissez la stabilité de votre stockage et la survie de vos données métier les plus récentes.

La fréquence des sauvegardes de journaux détermine directement le RPO (Recovery Point Objective) : la perte de données maximale en cas d’incident.

Pour les bases transactionnelles critiques (facturation, commandes, données financières), un RPO de 15 minutes ou moins est l’objectif standard.

Le mythe de la « surcharge » par les sauvegardes fréquentes

L’une des réticences les plus courantes que j’entends est la suivante : « Si je fais une sauvegarde toutes les 5 minutes, je vais mettre mon serveur à genoux à cause du flux incessant d’écritures. »

C’est une pensée intuitive qui se comprend, mais elle est techniquement fausse concernant SQL Server.

Le volume total de données généré par votre activité transactionnelle sur une heure est une constante. Que vous décidiez de l’extraire en une seule fois ou en douze fois, la quantité de données à écrire sur le support de sauvegarde sera la même, à quelques pages de 8 ko près.

Faire une sauvegarde toutes les 5 minutes, c’est simplement découper une grosse tâche lourde en petites opérations chirurgicales et indolores. La pression des I/O est lissée : Au lieu d’avoir un pic de saturation disque massif toutes les heures (lecture de 1 Go d’un coup), vous avez des micro-lectures de quelques mégaoctets qui passent totalement inaperçues pour les utilisateurs.

La sécurité psychologique du DBA

Au-delà de l’aspect technique, passer d’un RPO de 60 minutes à 5 minutes change radicalement la gestion d’un incident. Perdre 5 minutes de saisie comptable ou de commandes clients est généralement considéré comme un incident mineur (« on va ressaisir les derniers mails »). Perdre une heure entière (ou plus) est souvent un sinistre qui nécessite une cellule de crise et une communication officielle auprès de la direction.

En résumé : N’ayez pas peur des logs

Il n’y a quasiment aucun argument technique valable pour ne pas descendre à une fréquence de 5 ou 15 minutes sur une base de production. Sur des systèmes ultra-critiques, il est même fréquent de voir des sauvegardes de journaux toutes les minutes.

Le seul « coût » est la multiplication des petits fichiers .trn sur votre stockage de sauvegarde, un problème qui se gère très facilement avec n’importe quel script de maintenance ou plan de nettoyage automatisé.

Mettre en place des sauvegardes de journaux

La commande T-SQL pour sauvegarder le journal de transactions :

-- Sauvegarde du journal de transactions avec compression
BACKUP LOG [NomDeLaBase]
TO DISK = N'D:\Backups\NomDeLaBase\log\NomDeLaBase_20260302_143000.trn'
WITH COMPRESSION, STATS = 10;

En pratique, deux solutions sont couramment utilisées pour automatiser ces sauvegardes :

1. Les plans de maintenance SQL Server Agent : configuration graphique dans SSMS, planification par l’interface. Voir ma playlist YouTube
2. Les scripts d’Ola Hallengren : solution communautaire de référence, gratuite, maintenue activement. Ces scripts gèrent la nomenclature des fichiers, la compression, la rétention et la journalisation des opérations. Voir ma vidéo YouTube

Pour vérifier que les sauvegardes de journaux s’exécutent correctement, interrogez l’historique dans msdb :

-- Dernières sauvegardes de journaux par base de données (dernières 24 heures)
SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS duree_secondes,
    bs.backup_size / 1024 / 1024                                   AS taille_mo,
    bs.compressed_backup_size / 1024 / 1024                        AS taille_compresse_mo
FROM msdb.dbo.backupset bs
WHERE bs.type = 'L' -- L = Log
  AND bs.backup_start_date >= DATEADD(DAY, -1, GETDATE())
ORDER BY bs.database_name, bs.backup_start_date DESC;

Si une base en mode FULL n’apparaît pas dans ce résultat sur les dernières 24 heures, aucune sauvegarde de journal n’a été effectuée.

Restauration avec les sauvegardes de journaux de transactions

Une autre raison pour laquelle les équipes pourraient éviter les stratégies de sauvegardes de journaux : la peur de ne pas savoir comment les restaurer.

Ce n’est pas si compliqué, d’autant plus que l’interface graphique de SSMS vous simplifie considérablement la vie.

La restauration d’une chaîne de sauvegardes (complète + journaux) suit une séquence précise. J’ai réalisé la vidéo suivante, pour vous aider en cas de besoin de restauration. Elle contient une description complète de la procédure, avec démonstration dans SSMS. SQL Server : réussir vos restaurations de bases de données pas à pas.

Séquence de restauration

La logique de la restauration est la suivante (voir la vidéo à partir de 02:40) :

1. Restaurer la sauvegarde complète en mode WITH NORECOVERY : la base reste en état de restauration, inaccessible, prête à recevoir les journaux suivants.
2. Restaurer, si disponible, la dernière sauvegarde différentielle en mode WITH NORECOVERY.
3. Restaurer les sauvegardes de journaux dans l’ordre chronologique, chacune en mode WITH NORECOVERY.
4. Sur le dernier fichier de journal, appliquer WITH RECOVERY pour mettre la base en ligne.

-- 1. Restauration de la sauvegarde complète
--    (WITH NORECOVERY : la base reste en restauration)
RESTORE DATABASE [NomDeLaBase]
FROM DISK = N'D:\Backups\NomDeLaBase\full\NomDeLaBase_20260302_060000.bak'
WITH NORECOVERY, STATS = 10;

-- 2. Restauration des journaux de transactions dans l'ordre
RESTORE LOG [NomDeLaBase]
FROM DISK = N'D:\Backups\NomDeLaBase\log\NomDeLaBase_20260302_060000.trn'
WITH NORECOVERY;

RESTORE LOG [NomDeLaBase]
FROM DISK = N'D:\Backups\NomDeLaBase\log\NomDeLaBase_20260302_061500.trn'
WITH NORECOVERY;

-- ... autres fichiers de journaux dans l'ordre ...

-- 3. Dernier journal : mise en ligne de la base
--    (WITH RECOVERY est la valeur par défaut, peut être omis)
RESTORE LOG [NomDeLaBase]
FROM DISK = N'D:\Backups\NomDeLaBase\log\NomDeLaBase_20260302_143000.trn'
WITH RECOVERY;

Si la base reste bloquée en état de restauration après la chaîne complète, utilisez :

-- Mise en ligne d'une base coincée en état de restauration
RESTORE DATABASE [NomDeLaBase] WITH RECOVERY;

Restauration à un point dans le temps (PITR)

Pour restaurer la base à un instant précis, ajoutez la clause STOPAT sur le dernier RESTORE LOG (voir la vidéo à partir de 20:48) :

-- Restauration à un instant précis
RESTORE LOG [NomDeLaBase]
FROM DISK = N'D:\Backups\NomDeLaBase\log\NomDeLaBase_20260302_091500.trn'
WITH RECOVERY, STOPAT = '2026-03-02T09:02:00';

SQL Server rejoue toutes les transactions jusqu’à 09:02:00 et s’arrête. L’état transactionnel de la base est cohérent à cet instant précis.

Utilisation de l’interface graphique SSMS

Dans SSMS, si la base existe sur le serveur et que les sauvegardes ont été effectuées depuis ce même serveur, l’historique est disponible dans msdb. L’interface lit automatiquement cet historique et propose la séquence complète sans sélection manuelle des fichiers (voir la vidéo à partir de 10:37).

La barre de chronologie dans l’interface de restauration SSMS permet de sélectionner visuellement le point de restauration. SSMS génère ensuite le script T-SQL correspondant, avec la clause STOPAT correctement calculée.

Pour une restauration vers un serveur différent ou depuis des fichiers sans historique msdb, il suffit de sélectionner manuellement les fichiers .bak et .trn dans l’interface. SSMS analyse la séquence et construit automatiquement la chaîne de restauration.

Attention à l’intégrité de la chaîne de sauvegardes

Une chaîne de journaux de transactions est continue : chaque fichier .trn doit avoir un LSN (Log Sequence Number) de fin identique au LSN de début du fichier suivant. Si un fichier manque dans la séquence, la restauration s’arrête à ce point. Les fichiers suivants ne peuvent pas être appliqués.

Ne perdez donc aucun fichier de sauvegarde intermédiaire.

Conclusion

1. Configurer le mode de récupération FULL sur toutes les bases de production et planifier des sauvegardes de journaux.
2. Appliquer une fréquence minimale de 15 minutes pour les bases transactionnelles.
3. Vérifier régulièrement que les travaux SQL Agent s’exécutent et que l’historique dans msdb est cohérent.
4. Tester la restauration complète avec chaîne de journaux en environnement de développement ou de recette, au moins une fois par an.
5. Conserver les fichiers .trn au moins aussi longtemps que la sauvegarde complète à laquelle ils se rattachent.
6. Sauvegarder les certificats de chiffrement si les sauvegardes sont chiffrées — sans le certificat, la restauration est impossible.

Utilisez les scripts d’Ola Hallengren