Ouvrir un fichier LDF et afficher son contenu

Chaque base de données SQL Server contient un ensemble de fichiers stockés dans le système d’exploitation. Ces fichiers stockent les données et les informations de journalisation. Les fichiers sont utilisés individuellement par une seule base de données et les informations de journalisation et les données ne sont jamais mélangées dans le même fichier. Alors que les données sont stockées dans un fichier MDF, toutes les transactions, les modifications de la base de données SQL Server effectuées par chaque transaction sont stockées dans un fichier LDF – le fichier de journalisation des transactions qui est une composante essentielle de la base de données. D’un point de vue conceptuel, le fichier de journalisation est une série d’enregistrements du journal. Physiquement, les enregistrements de journalisation sont stockés dans un ou plusieurs fichiers LDF physiques qui constituent le journal des transactions.

L’objet principal d’un fichier LDF est de supporter le concept d’ACID (Atomicité, Cohérence, Isolation, Durabilité).

• Atomicité : si une partie de la transaction échoue, la transaction entière échoue et l’état de la base de données est laissée inchangée ;
• Cohérence : n’importe quelle transaction fait en sorte que la base de données passe d’un état valide à un autre état valide ;
• Isolation : l’exécution de transactions concurrentes conduit la base de données à un fonctionnement comme si les opérations étaient sérialisées, les unes après les autres ;
• Durabilité : une fois terminée, la transaction doit être conservée, même en cas d’erreurs, perte, ou des pannes d’alimentation.

Un fichier LDF stocke suffisamment d’informations pour rejouer et pour annuler une modification ou pour récupérer la base de données à un point spécifique dans le temps. Donc, en raison de diverses exigences d’audit ou de récupération, il est souvent nécessaire d’ouvrir le fichier LDF et d’afficher son contenu. Mais, afficher le contenu d’un fichier LDF n’est pas une tâche aisée.

Il existe dans de SQL Server plusieurs fonctions ou commandes (par exemple : fn_dblog, fn_dump_dblog et DBCC PAGE) offrant potentiellement un moyen d’afficher le contenu d’un fichier LDF. Cependant, une connaissance significative en T-SQL est requise pour les utiliser, certaines ne sont pas documentées et les résultats qu’ils fournissent sont difficiles à convertir en un format lisible par l’homme. Voici les exemples d’affichage de contenu d’un fichier LDF en utilisant les commandes et les fonctions de SQL Server:

1. Exemple avec fn_dblog pour lire un journal de transactions en ligne, avec un résultat de 129 colonnes (seulement 7 sont montrés ici):

   

2. La fonction fn_dump_dblog est utilisée pour lire le journal des transactions ou les sauvegardes compressées. Le résultat est similaire:

   

   Malheureusement, aucune documentation officielle n’est disponible pour les fonctions fn_dblog et fn_dump_dblog. Pour comprendre les colonnes, vous devez être familier avec la structure interne et le format de données, les indicateurs et leur nombre total par rapport aux enregistrements de données retournés.

3. DBCC PAGE est utilisée pour lire le contenu des fichiers de la base de données en ligne (fichiers MDF et LDF). Il en résulte une sortie hexadécimale et sauf si vous avez un éditeur ad hoc, cela sera à difficile à interpréter:

   

Utilisation d’ApexSQL Log comme lecteur de fichiers LDF

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. En tant qu’afficheur de LDF, il se concentre sur les opérations (DML et DDL, 45 au total), et sur ce qui a été changé par l’exécution de ces opérations.

En plus de montrer le contenu logique d’un fichier LDF, ApexSQL Log fournit également quelques fonctionnalités supplémentaires comme la création de scripts Undo/Redo, un historique des enregistrements pour les opérations DML, et plus.

Pour ouvrir et afficher un fichier LDF en utilisant ApexSQL Log :

1. Se connecter à la base de données à laquelle le fichier LDF appartient:

   

2. L’étape suivante consiste à ajouter des sauvegardes de fichiers LDF et/ou fichiers LDF détachés, contenant les informations que vous avez besoin d’afficher. Veillez à ce qu’ils forment une chaîne complète de journaux. Une chaîne de journaux est une séquence continue de sauvegardes du journal des transactions. Il commence par une sauvegarde complète suivie par toutes les sauvegardes de journal successives jusqu’au point de vérification. Si cela n’est pas respecté, seules les transactions dans les journaux jusqu’à la dernière sauvegarde avant celle manquante, peuvent être montrées avec des informations complètes (par exemple un nom de schéma et d’objet, ou un historique de ligne).

   À la différence des opérations INSERT et DELETE, qui sont entièrement enregistrées dans les fichiers LDF, les opérations UPDATE sont enregistrées au minimum – seules les modifications qui sont apportées sont enregistrées, mais les valeurs anciennes et nouvelles ne le sont pas. Quand les opérations UPDATE sont enregistrées, SQL Server n’enregistre pas complètement l’état des lignes avant et après, mais seulement le changement qui s’est produit sur les lignes. Par exemple, si un mot « log » a été mis à jour avec le mot « blog » SQL Server n’enregistrera, en général, que l’ajout de la lettre « b » à l’index 0. Cela suffit pour s’assurer que le principe ACID soit respecté, mais pas assez pour montrer facilement le changement d’état des lignes. Donc, afin de comprendre les changements qui ont réellement eu lieu, ApexSQL Log doit reconstituer le contexte dans lequel le changement est survenu à partir le reste du journal des transactions et/ou des données de la base de données sauvegardées et celles en ligne.

   Pour y parvenir, cela nécessite de déployer différentes techniques complémentaires de reconstruction de l’état des lignes et une de ces reconstructions utilise la chaîne complète de journaux. En spécifiant la chaîne complète de journaux, associant la sauvegarde complète la plus récente et les sauvegardes différentielles, cela permet à ApexSQL Log de reconstituer le contexte de l’opération UPDATE à partir de l’état des lignes mises à jour au moment de la dernière sauvegarde et de reconstruire toutes les opérations qui a eu lieu depuis lors. Dans notre exemple, ApexSQL Log aurait trouvé l’état de la ligne au moment de la dernière sauvegarde, en constatant que la valeur du champ était « log » et de là, en conclure qu’avec le changement enregistré de l’ajout d’un « b » à l’index 0, cela a transformé la valeur du champ en « blog ».

   En outre, une chaîne de journaux permet de lire les transactions plus rapidement en utilisant seulement quelques fichiers de sauvegarde de journaux de transaction au lieu d’un unique journal des transactions en ligne qui peut être nettement plus grand que toutes les sauvegardes de journaux de transactions utilisées. De plus, un fichier LDF détaché peut également être utilisé comme une source de lecture– ApexSQL Log analysera toutes les sources fournies afin de fournir des renseignements exacts sur les transactions enregistrées dans les fichiers LDF.

   Pour faire cela, utiliser le bouton Add à l’étape Select SQL Server logs to analyse:

   

3. L’utilisation de l’écran Database backups permet de préciser la sauvegarde complète de la base de données qui sera utilisée comme point de départ d’où la chaîne complète des transactions doit commencer:

   

4. À l’aide de la section Time range dans l’étape d’options Filter setup, spécifier la période relative aux opérations exécutées qui vous intéressent. Cela permettra d’accélérer le processus en réduisant les opérations requises:

   

5. Lorsque tout cela est fait, utiliser le bouton Open pour démarrer le processus de lecture des fichiers LDF.

Toutes les transactions, d’après les sources fournies et selon les filtres précisés, seront affichées dans la grille principale d’ApexSQL Log, une fois le processus terminé.

Conclusion à propos du lecteur de fichier LDF

Comme indiqué ci-dessus, il existe différentes façons d’ouvrir un fichier LDF, et la plupart d’entre elles font exactement cela, c’est à dire « ouvrir ». Il est difficile d’obtenir des informations lisibles par un être humain et d’en avoir un usage aisé.

En revanche, en plus d’ouvrir simplement un fichier LDF, ApexSQL Log ajoute de la valeur à ce processus. Vous serez capable de lire les journaux de transactions pour voir le type d’opération, le nom de schéma et l’objet de l’objet touché, le moment où l’opération a été exécutée, le nom de l’utilisateur qui a exécuté l’opération, etc.

Les principaux avantages du traitement d’un fichier LDF avec ApexSQL Log face aux outils qui les affichent juste, sont la capacité de :

• Afficher des informations humainement lisibles des données (conversion des valeurs en hexadécimal et binaire) de 129 colonnes nativement
• Enchaîner différents fichiers LDF en un seul
• Combiner des fichiers en ligne ou sauvegardés (base de données et fichiers LDF), afin de fournir plus de détails sur chaque transaction
• Reconstruire complètement des opérations UPDATE, y compris l’ancienne valeur (avant) et la nouvelle (après)
• Afficher l’historique complet des changements de lignes pour les opérations DML, y compris la connexion des utilisateurs qui ont exécuté les opérations ainsi que le temps d’exécution de la transaction
• Pré-filtrer des informations qui seront lues afin d’économiser les ressources et d’accélérer le processus
• Référencer des IDs des anciennes tables (effacées) pour permettre la récupération des données des tables qui n’existent plus
• Traiter et représenter du contenu du fichier LDF sans avoir besoin d’avoir des connaissances en scripts T-SQL Server

Au-delà de la lecture, ApexSQL Log fournit également :

• Une grille avec tri, regroupement, filtrage et recherche avancée pour manipuler les données en d’une multiple façon
• En plus du rendu dans une grille, ApexSQL Log peut convertir des données en CSV, HTML, XML, ou SQL Server, aider à gérer l’exportation et sauvegarder le contenu de la grille pour une analyse ultérieure
• Création de scripts SQL pour annuler ou rejouer des transactions SQL Server. Cela peut être utile dans un scénario de récupération de base de données lorsque des données manquantes doivent être restaurées, ou lorsqu’un schéma/des données modifiées doivent être annulées, et cela sans faire une restauration complète de la base de données
• Documentation officielle pour toutes les fonctionnalités
• Support technique et plus

Article traduit par Philippe Geiger
Consultant certifié MCSE Data Platform et Business Intelligence et formateur certifié MCT.
Blog:http://blog.pgeiger.net
Twitter:@pgeiger