La fin d’un mythe : « je n’ai pas besoin de la restauration à un point dans le temps donc je n’ai pas besoin du fichier du journal des transactions. »
Or c’est ce seul fichier qui garantit l’intégrité de la base de données en cas de crash du serveur …

Le fichier du journal des transaction n’est pas comme un « log » d’application dont on se sert pour débugger celle-ci.
S’il est vrai que celui-ci enregistre tous les changements effectués sur les données de la base de données, il est le seul garant de la conservation des propriétés ACID de la base de données.

Une instance de SGBDR contient des tables, et les données de ces tables sont stockées dans des pages.
Ces pages sont stockées dans les fichiers de données de la base de données (par défaut ils portent l’extension .mdf et .ndf).

Lorsqu’on effectue une modification de données dans une table, les pages qui contiennent les lignes qui sont affectées par cette mise à jour sont, dans le pire des cas, lues à partir du disque et placées dans le cache de données : quand elle n’y sont pas déjà, elles sont presque directement modifiées : l’intégralité des données nécessaires à défaire ou refaire la transaction est d’abord écrit dans le fichier du journal des transactions.

Les pages de données sont ensuite modifiées en mémoire SEULEMENT, c’est à dire pas encore persistées dans les fichiers de données de la base de données.

C’est un processus, nommé LAZYWRITER, qui les écrit dans les fichiers de données, par défaut toutes les minutes (configurable, à vos risques et périls, par l’option recovery interval (min) avec la procédure stockée sp_configure).
Vous pouvez voir ce processus sous SQL Server 2005 ou 2008 en requêtant la vue de gestion dynamique sys.dm_exec_requests.

On peut alors penser super! c’est exactement ce dont j’ai besoin … mais à votre avis que se passe-t-il si le LAZYWRITER intervient au milieu d’une transaction ? et pire, si un crash se produit pendant la transaction ?

Lorsque le processus de restauration de SQL Server redémarre une instance de SQL Server et ses bases de données, il scrute le fichier du journal des transactions à la recherche des mises à jour de données qui ont été enregistrés dans ce fichier, mais pour lesquels la transaction n’a pas été terminée.
Ces changements sont alors défaits (ROLLBACK) de façon à replacer la base de données dans un état parfaitement consistant avant de se mettre à l’écoute des connexions utilisateurs.

Voilà pourquoi il est absolument nécessaire de conserver et de disposer du fichier du journal des transactions lors d’une restauration de base de données, car sans celui-ci, l’intégrité de la base de données n’est plus garantie.

ElSuket