Lire un journal de transactions SQL Server

Le journal de transactions SQL Server contient des enregistrements décrivant les modifications apportées à une base de données. Ils stockent suffisamment d’informations pour récupérer la base de données à un point précis dans le temps, de rejouer ou annuler une modification. Mais, comment voir son contenu, trouver une transaction spécifique, voir ce qui s’est passé et annuler les changements comme par exemple la récupération de lignes supprimées accidentellement

Pour voir ce qui est stocké dans un journal de transactions en ligne, ou une sauvegarde de journal de transactions n’est pas si simple. Ouvrir des fichiers LDF ou TRN dans un éditeur binaire ne permet pas une lecture explicite des données. Par exemple, voici un extrait d’un fichier LDF:

Opening LDF and TRN files in a binary editor

Utilisation de fn_dblog

fn_dblog est une fonction SQL Server non documentée permettant de lire la partie active d’un journal de transactions en ligne. Regardons les étapes que vous devez effectuer et la façon dont les résultats sont présentés:

  1. Exécutez la fonction fn_dblog
    2. Select * FROM sys.fn_dblog(NULL,NULL)

    Results set returned by fn_dblog function

    Comme la fonction renvoie 129 colonnes, il est recommandé de sélectionner uniquement celles dont vous avez besoin et de réduire le résultat à un type de transaction si cela est possible.

  2. Dans l’ensemble de résultat retourné par fn_dblog, trouver les transactions que vous souhaitez analyser. Pour voir les transactions pour les lignes insérées, exécutez:

    3. SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
       [Begin time]
       FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_INSERT_ROWS');

    Transactions for inserted rows

    Pour voir les transactions pour les enregistrements supprimés, exécutez:

    SELECT [begin time], 
       [rowlog contents 1], 
       [Transaction Name], 
       Operation
  FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_DELETE_ROWS');

    Transactions for deleted rows

  3. Trouver la colonne qui stocke la valeur insérée ou supprimée – Consultez les colonnes RowLog Contents 0, RowLog Contents 1, RowLog Contents 2, RowLog Contents 3, RowLog Contents 4, Description et Log Record.

    4. Les données des enregistrements sont stockées dans différentes colonnes et cela pour différents types d’opérations.

    Pour être en mesure de voir exactement ce dont vous avez besoin en utilisant la fonction fn_dblog, vous devez connaître le contenu de la colonne pour chaque type de transaction. Comme il n’y a pas de documentation officielle pour cette fonction, ce n’est pas facile. Les lignes insérées et supprimées sont affichées en valeurs hexadécimales. Pour être en mesure de les découper en champs, vous devez connaitre le format utilisé, comprendre leurs status bits, connaître le nombre total de colonnes, etc…

  4. Convertissez les données binaires présentes dans la table en prenant en compte le type de données de la colonne de la table. Notez que les mécanismes de conversion sont différents en fonction du type de données.

fn_dbLog est puissant et gratuit, mais possède quelques limitations : la lecture des enregistrements de journaux concernant des changements de structure est complexe car elle implique généralement la reconstruction de l’état de plusieurs tables système. Seule la partie active d’un journal de transactions en ligne est lu, et il n’y a pas de reconstruction pour les opérations UPDATE et BLOB.

L’opération UPDATE est peu enregistrée dans le journal de transactions. Les valeurs anciennes ou nouvelles ne sont pas complètement enregistrée mais uniquement ce qui a changé pour l’enregistrement (par exemple SQL Server peut enregistrer que “G” a été changé en “F”, alors qu’en réalité, la valeur “GLOAT” a été changé en “FLOAT”). Vous devez reconstruire manuellement l’état antérieur à la mise à jour qui consiste à reconstruire tous les états intermédiaires entre l’insertion initiale de la ligne dans la page et la mise à jour que vous essayez de reconstruire.

Lors de la suppression de BLOBs, les BLOBs supprimés ne sont pas insérés dans un journal de transactions, la lecture du journal de transaction pour le BLOB supprimé ne permet donc pas la récupération du BLOB. S’il y a eu un INSERT enregistré dans le journal pour le BLOB supprimé, et que vous parvenez à les associer, vous serez en mesure de récupérer le BLOB supprimé à partir du journal de transactions à l’aide de fn_dblog.

Utilisation de fn_dump_dblog

Pour lire journaux de transactions ou des backups compressés nativement, même sans la base de données en ligne, vous pouvez utiliser la fonction de fn_dump_dblog. Encore une fois, cette fonction n’est pas documentée.

  1. Exécutez la fonction fn_dump_dblog sur une sauvegarde spécifique du journal de transactions. Notez que vous devez spécifier tous les 63 paramètres.
    2. SELECT *
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT);

    fn_dump_dblog function output

    Comme pour la fonction fn_dbLog, 129 colonnes sont retournées, il est donc recommandé de les sélectionner spécifiquement.

    SELECT [Current LSN], 
       Operation, 
       Context, 
       [Transaction ID], 
     [transaction name],
       Description
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\ApexSQL\backups\AdventureWorks2012_05222013.trn', 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
DEFAULT);

    Encore une fois, vous devez déchiffrer les valeurs hexadécimales pour obtenir les informations que vous cherchez.

    Returning specific columns using fn_dump_dblog function

    De retour à la case départ comme avec l’utilisation de fn_dblog – vous avez besoin de reconstituer toutes les valeurs d’enregistrements manuellement, vous devez reconstituer des chaînes entières pour les opérations d’UPDATE, les valeurs de type BLOB et ainsi de suite…

    Si vous ne voulez pas extraire toutes les transactions de la sauvegarde du journal des transactions, mais restaurer la base de données à un point dans le temps avant une opération spécifique, vous pouvez:

  2. Déterminer le LSN de cette transaction
  3. Convertir le LSN dans le format utilisé dans la clause WITH STOPBEFOREMARK = ‘<mark_name>’
    Par exemple 00000070: 00000011: 0001 devrait être transformé en 112000000001700001
  4. Restaurer la chaîne des sauvegardes du journal jusqu’à ce que vous atteigniez le moment où les transactions ont eu lieu. Utilisez la clause WITH STOPBEFOREMARK = ‘<mark_name>’ pour spécifier le LSN de la transaction référencée 
    RESTORE LOG AdventureWorks2012
FROM
    DISK = N'E:\ApexSQL\backups\AW2012_05232013.trn'
WITH
    STOPBEFOREMARK = 'lsn:112000000001700001',
    NORECOVERY;

Utilisation de DBCC PAGE

Une autre commande utile, mais encore une fois non documentée est DBCC PAGE. Utilisez la pour lire le contenu des fichiers en lignes de base de données MDF et LDF. La syntaxe est la suivante:

DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Pour afficher la première page du fichier du journal de transactions en ligne de la base de données AdventureWorks2012, utilisez:

SELECT FILE_ID ('AdventureWorks2012_Log') AS 'File ID' 
-- to determine Log file ID = 2
DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Vous obtenez:

DBCC execution completed. If DBCC printed error messages, 
contact your system administrator.

Par défaut, la sortie ne s’affiche pas. Si vous voulez une sortie dans SQL Server Management Studio, activez l’indicateur de trace 3604 avant:

DBCC TRACEON (3604, -1)

Et ré exécutez:

DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Vous aurez un tas d’erreurs et un mauvais en tête, vous pouvez ignorer cela. A la fin vous aurez une sortie hexadécimale du fichier LDF en ligne:

Hexadecimal output from the online LDF file

Ceci n’est pas la présentation la plus compréhensible pour lire les données, elle se rapproche de l’affichage dans un éditeur hexadécimal, mais au moins vous aurez accès aux données en ligne.

Utilisation de ApexSQL Log

ApexSQL Log est un lecteur de journal de transaction de SQL Server qui lit les journaux des transactions en ligne, les journaux des transactions détachés et les sauvegardes de journaux des transactions, nativement compressées ou non. Si nécessaire, il permettra également de lire les sauvegardes de bases de données pour obtenir suffisamment de données afin de réussir une reconstruction. Il peut rejouer des modifications sur des données ou des objets concernés, y compris celles qui ont eu lieu avant que ApexSQL Log ne soit installé. Contrairement aux fonctions non documentées et non prises en charge décrites ci-dessus, vous obtiendrez des informations tout à fait compréhensibles sur ce qui s’est passé, sur quel objet, ainsi que des informations sur les anciennes et nouvelles valeurs.

  1. Démarrez ApexSQL Log

  2. Connectez-vous à la base de données dont vous souhaitez lire les journaux de transactions

    Connecting to the database to read the transaction logs from

  3. Dans l’étape Select SQL logs to analyze sélectionnez les journaux que vous souhaitez lire. Assurez-vous qu’ils forment une chaîne complète

    Selecting the transaction logs to read from

  4. Pour ajouter des sauvegardes des journaux de transactions et des fichiers LDF détachés, utilisez le bouton Add

  5. Utilisez les options Filter setup pour affiner les transactions lues en spécifiant l’intervalle de temps, le type d’opération, table et autres filtres disponibles

    Filtering the transactions read

  6. Cliquez sur Open

    Les résultats complets et compréhensibles seront présentés dans la grille ApexSQL Log

    Vous serez en mesure de voir le moment où l’opération a commencé et s’est terminée, le type d’opération, le schéma et le nom de l’objet concerné, le nom de l’utilisateur qui a exécuté l’opération, l’ordinateur et l’application utilisée pour exécuter cette opération. Pour les UPDATEs, vous verrez l’ancienne et la nouvelle valeur des champs mis à jour.

    Fully comprehensive results shown in the ApexSQL Log grid

Pour éviter des valeurs hexagonales, des fonctions non documentés, des contenus de colonnes non explicites, de longues requêtes, des étapes complexes, des UPDATE incomplet et des reconstructions de BLOB lors de la lecture des journaux de transactions deSQL Server, utilisez ApexSQL Log. Il lira les journaux de transactions pour vous et présentera des résultats “en langage clair”. En plus de cela, les scripts d’annulation et de réexécution sont à portée de clic

Article traduit par Romain Casteres
Consultant en informatique décisionnelle et en Big Data chez Dcube. Il est MVP SQL Server, MCSE Data Platform et Business Intelligence.
Blog : http://pulsweb.fr
Twitter : @PulsWeb

Related posts:

  1. Récupérer les données SQL d’une table supprimée sans sauvegarde
  2. Ouvrir un fichier LDF et afficher son contenu
  3. Comment récupérer des données SQL Server suite à des mises à jour accidentelles lorsque vous ne disposez pas de sauvegardes
  4. Comment récupérer des données SQL Server après un UPDATE / DELETE accidentel
  5. Récupérer les données de SQL supprimés à partir des journaux de transaction
October 9, 2014