SQL Server : lire les journaux des transactions

Partager SQL Server : lire les journaux des transactions sur Linkedin

21 novembre 2018

A l’occasion d’une perte de données sur une base de données SQL Server du fait de l’exécution d’instructions DML ou DDL tels que UPDATE, INSERT, TRUNCATE TABLE ou CREATE/ALTER TABLE, il peut être intéressant d’en connaître l’origine, avant de remonter votre sauvegarde et de rejouer vos transactions à l’aide de votre sauvegarde complète BAK et les fichiers TRN de sauvegarde des transactions.

Des logiciels inutiles

Il existe des outils commerciaux et payants pour le faire, à l’image de ApexSQL Log ou bien de SysTools SQL Log Analyzer. Ils sont inutiles ou presque ! Vous disposez, sur SQL Server, de la fonction table sys.fn_dblog – non documentée – qui vous renvoie le contenu des transactions contenues dans votre fichier LDF, associé à votre fichier de données MDF. Tant que vous ne l’avez pas tronqué à l’occasion de votre plan de maintenance et/ou d’une sauvegarde complète, vous pouvez lister les transactions effectuées, après avoir choisi la base de données concernée à l’aide de l’instruction USE.

Utilisation de la fonction table sys.fn_dblog

USE Northwind;
GO
WITH 
	BT AS
	(
		SELECT 
			[Begin Time] AS debut,
			[Transaction Name] AS type,
			Operation AS operation,
			[Transaction ID] AS numtrans
			--AllocUnitName,
		FROM
			sys.fn_dblog(NULL,NULL)	AS BT
		WHERE [Begin Time] IS NOT NULL
	),
	ET AS
	(
		SELECT
			Operation AS operation,
			[End Time] AS fin,
			[Transaction ID] AS numtrans
		FROM
			sys.fn_dblog(NULL,NULL)
		WHERE 
			[End Time] IS NOT NULL
	),
	A AS
	(
		SELECT
			[Transaction ID] AS numtrans,
			AllocUnitName AS objet
		FROM
			sys.fn_dblog(NULL,NULL)
		WHERE
			AllocUnitName IS NOT NULL
	)
SELECT DISTINCT
	BT.type,
	BT.debut,
	ET.fin,
	BT.operation,
	ET.operation,
	A.objet
FROM
	BT 
	INNER JOIN ET ON BT.numtrans=ET.numtrans
	INNER JOIN A ON ET.numtrans=A.numtrans
WHERE
	ET.operation != 'LOP_ABORT_XACT'
AND
	BT.type IN ('UPDATE','DELETE','TRUNCATE TABLE','INSERT')
ORDER BY
	debut,fin;
GO

Résultat de la requête

La requête présentée ci-dessus permet de visualiser le début et la fin de la transaction, l’instruction DML/DDL qui lui est associée ainsi que la table sur laquelle elle porte.

Utilisation de la fonction table sys.fn_dblog