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

SQL Server propose trois modes de récupération pour chaque base de données.

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.

Pour vérifier le mode de récupération de vos bases et l’état du journal :

-- Mode de récupération et raison du blocage du journal
SELECT
    name                 AS base_de_donnees,
    recovery_model_desc  AS mode_de_recuperation,
    log_reuse_wait_desc  AS attente_reutilisation_journal
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Si la colonne log_reuse_wait_desc affiche LOG_BACKUP, le journal est en attente d’une sauvegarde pour libérer de l’espace. Si elle affiche NOTHING, le journal peut être tronqué normalement.

Pour voir la taille des journaux et leur taux de remplissage :

-- Taille et remplissage des journaux de transactions
DBCC SQLPERF (LOGSPACE);

Voir aussi l’article Identifier et régler les problèmes de journaux de transactions et Pourquoi le disque des journaux de transaction est-il plein ?.

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

Deux raisons distinctes expliquent pourquoi une stratégie de sauvegarde basée uniquement sur des sauvegardes de VM est insuffisante pour des bases SQL Server en mode FULL.

Croissance non maîtrisée du journal. Veeam (ou tout équivalent) sauvegarde la VM à un instant T. Entre deux sauvegardes VM, aucune commande BACKUP LOG n’est exécutée. Le journal SQL Server continue donc de grossir. À terme, il remplit le disque et provoque l’arrêt de la base. C’est indépendant de la fréquence des sauvegardes VM.

Granularité de restauration. Une sauvegarde VM donne un état complet à un instant T, généralement la nuit. Un incident survenu en cours de journée entraîne la perte de toutes les transactions depuis cette sauvegarde. Avec des sauvegardes de journaux toutes les 15 minutes, la perte maximale est de 15 minutes. Sur une base de production transactionnelle, cette différence est déterminante.

Il existe par ailleurs un risque de cohérence : une sauvegarde VM prise pendant une transaction active peut produire une image incohérente de la base, difficile à restaurer correctement.

Les avantages des sauvegardes de journaux de transactions

Limitation de la perte de données (RPO)

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.

// CLAUDE : Bien expliquer ici pourquoi ce n’est pas un problème de faire des sauvegardes de journaux de transactions, même toutes les cinq minutes, voire même toutes les minutes. Beaucoup de gens ont peur de faire trop de sauvegardes et de troubler les performances de SQL Server, alors que des sauvegardes de cinq minutes sont très légères. Ça ne fait pas de différence de faire une sauvegarde toutes les cinq minutes ou une sauvegarde toutes les quinze minutes, car à la fin le temps et le volume seront les mêmes.

Restauration à un point dans le temps

Avec une chaîne de journaux intacte, il est possible de restaurer une base à n’importe quel instant couvert par cette chaîne. Ce mécanisme est particulièrement utile pour annuler une modification erronée — un UPDATE sans WHERE, une suppression accidentelle — en revenant à l’état juste avant l’opération.

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.

Stabilité du fichier de journal

Des sauvegardes régulières maintiennent le fichier .ldf à une taille stable. L’espace libéré par chaque BACKUP LOG est réutilisé par SQL Server pour les nouvelles transactions, sans que le fichier n’ait besoin de croître.

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

// CLAUDE : Faire référence à 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.

Problème courant : le propriétaire du travail SQL Agent

Une situation fréquente : des sauvegardes de journaux correctement configurées qui ne s’exécutent pas silencieusement. La cause la plus courante est un propriétaire de travail SQL Agent invalide — un compte Windows ou SQL supprimé, ou un login qui n’a plus les droits nécessaires.

-- Lister les travaux SQL Agent de sauvegarde avec leur propriétaire
SELECT
    j.name           AS nom_travail,
    j.enabled        AS actif,
    l.name           AS proprietaire,
    j.date_modified  AS date_modification
FROM msdb.dbo.sysjobs j
LEFT JOIN sys.server_principals l ON j.owner_sid = l.sid
WHERE j.name LIKE '%backup%'
   OR j.name LIKE '%sauvegarde%'
   OR j.name LIKE '%log%'
ORDER BY j.name;

-- Corriger le propriétaire d'un travail
EXEC msdb.dbo.sp_update_job
    @job_name        = N'Nom du travail de sauvegarde',
    @owner_login_name = N'sa';

Si l.name est NULL, le propriétaire du travail n’existe plus sur le serveur. Le travail est inactif même s’il est marqué comme activé.

Restauration avec les sauvegardes de journaux de transactions

La restauration d’une chaîne de sauvegardes (complète + journaux) suit une séquence précise. Une description complète de la procédure, avec démonstration dans SSMS, est disponible dans la vidéo 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.

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

Pour détecter les ruptures de chaîne de sauvegarde avant qu’un incident ne survienne :

-- Vérifier la continuité de la chaîne de sauvegardes (dernières 48 heures)
SELECT
    bs.database_name,
    bs.type                    AS type_sauvegarde,
    bs.backup_start_date,
    bs.first_lsn,
    bs.last_lsn,
    bs.database_backup_lsn
FROM msdb.dbo.backupset bs
WHERE bs.database_name = N'NomDeLaBase'
  AND bs.backup_start_date >= DATEADD(HOUR, -48, GETDATE())
ORDER BY bs.backup_start_date;

Les colonnes first_lsn et last_lsn permettent de vérifier que la chaîne est continue. Chaque first_lsn doit être inférieur ou égal au last_lsn de la sauvegarde précédente.

À faire

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 d’une heure pour les bases de production standard ; 15 minutes pour les bases transactionnelles critiques.
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.

À éviter

1. Utiliser uniquement des sauvegardes de VM comme stratégie de sauvegarde pour des bases SQL Server en mode FULL.
2. Laisser une base en mode FULL sans sauvegarde de journal planifiée.
3. Passer une base de production en mode SIMPLE pour résoudre le problème du journal plein — cela supprime toute possibilité de PITR.
4. Supprimer des fichiers .trn anciens sans vérifier qu’ils ne font pas partie d’une chaîne de restauration encore active.
5. Planifier des sauvegardes de journaux sur une base en mode SIMPLE — BACKUP LOG échoue sur ce mode.
6. Ignorer les erreurs silencieuses des travaux SQL Agent — un travail marqué comme actif peut ne pas s’exécuter si son propriétaire est invalide.