Auslesen einer SQL Server Transaktionsprotokolldatei

Das SQL Server Transaktionsprotokoll enthält Einträge zu allen Änderungen, die in einer Datenbank gemacht wurden. Diese Einträge enthalten die nötigen Informationen, damit eine Datenbank zu jedem beliebigen Zeitpunkt wiederhergestellt werden kann. Ist es möglich, diese Informationen in einem lesbaren Format darzustellen? Nun, so einfach ist es in der Tat nicht, egal ob man sich eine Protokolldatei im Onlinezustand oder als Protokollsicherung anschauen möchte.

Wenn man eine LDF- oder TRN-Datei in einem Hex-Editor öffnet, sieht man eine Reihe unlesbarer Einträge, wie dieses Beispiel einer LDF-Datei zeigt:

Verwendung von “fn_dblog”

fn_dblog ist eine nicht dokumentierte SQL Server Funktion, die Teile des aktiven Transaktionsprotokolls auslesen kann.

Wir werden hier die Schritte auflisten, um mit fn_dblog ein Transaktionsprotokoll auszulesen:

1. Führe die Funktion “fn_dblog” aus:

Select * FROM sys.fn_dblog(NULL,NULL)



Die Funktion liefert 129 Spalten zurück. Hier ist es ratsam, die Spalten auf das Nötigste zu reduzieren. Zudem sollte man überlegen, ob auf eine bestimmte Transaktion gefiltert werden kann.

2. Um nur die eingefügten Datensätze zu sehen, führen Sie folgendes aus:

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

   

   Um die gelöschten Einträge zu sehen, führen Sie Folgendes aus:

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

   

3. Um die Spalten, die die eingefügten bzw. gelöschten Datensätze enthalten, zu finden, nutzen Sie die Spalten “RowLog Contents 0″,”RowLog Contents 1″,”RowLog Contents 2″,”RowLog Contents 3″,”RowLog Contents 4″,”Description” und “Log Record”

Die Datensätze sind in unterschiedlichen Spalten gespeichert, je nachdem welcher Befehl ausgeführt wurde. Demnach müssen Sie die Transaktionstype kennen, um die richtigen Spalten auszulesen. Da es keine offizielle Dokumentation dazu gibt, ist es ziemlich kompliziert, diese Information zu ermitteln.

Die eingefügten und gelöschten Datensätze werden als hexadezimale Werte angezeigt. Um diese Werte umzuwandeln, müssen Sie deren Format, die Status-Bitwerte sowie die Anzahl der Spalten usw. kennen.

4. Bei der Konvertierung der Binärdaten in die Originaldaten werden die Konvertierungsoptionen je nach Zieldatentyp unterschieden.

fn_dblog ist eine mächtige und kostenfreie Funktion, allerdings mit einigen Einschränkungen. Das Auslesen von Objektstrukturänderungen ist recht komplex, da einige Systemtabellen und deren Status rekonstruiert werden müssen. Zudem kann nur der aktive Teil des Transaktionsprotokolles ausgelesen werden, und zu gute Letzt können weder UPDATEs noch BLOBs rekonstruiert werden.

UPDATEs werden nur minimal protokolliert, d.h. es wird nur das festgehalten, was verändert wurde. (Z.B. SQL Server schreibt, dass “R” in “L” geändert wurde, wenn eigentlich “GRAS” in “GLAS” geändert wurde). Folglich müsste man händisch alle Zwischenschritte seit dem Ursprungs-Insert bis zur letzten Aktualisierung nachtragen, um die Änderungshistorie komplett wiederherstellen zu können.

Wenn man BLOBs löscht, werden nur die Befehle in die Transaktionsprotokolle notiert und nicht die gelöschten Datensätze. Es wäre nur möglich diese Datensätze auszulesen, wenn ein dazu passendes INSERT zu finden ist und diese zwei Befehle auch noch ordnungsgemäß vereint werden können.

Verwendung von fn_dump_dblog

Um eine Transaktionsprotokollsicherung zu lesen (ob mit nativer Komprimierung oder nicht), nutzen Sie die Funktion “fn_dump_dblog”. Diese Funktion ist, genau wie “fn_dblog”, nicht offiziell dokumentiert.

1. Führen Sie die Funktion auf eine passende Transaktionsprotokollsicherungsdatei aus. Dabei müssen *alle* 63 Spalten angegeben werden.

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);



Genau wie fn_dblog, werden hier auch 129 Spalten ausgegeben. Das Einschränken auf sinnvolle Spalten ist ratsam:

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);

Hier müssen wieder die Binärdaten ausgelesen bzw. entziffert werden.



Und nun sind wir wieder am Anfang. Alle Werte und Datensätze müssen manuell rekonstruiert und UPDATEs müssen mühevoll aus INSERT und Zwischenschritte zusammengesucht werden. BLOBs zu rekonstruieren ist nur sehr eingeschränkt möglich.

Falls Sie nur eine Datenbank zu einem bestimmten Zeitpunkt wiederherstellen wollen, weil Sie z.B. ein DELETE ausgeführt haben und die Datenbank bis vor dem Löschvorgang zurück haben wollen, müssen Sie

2. die LSN für die Transaktion herausfinden, die irrtümlicherweise ausgeführt wurde.
3. diese LSN umformatieren, damit sie zu der WITH STOPBEFOREMARK = ‘<mark_name>’ Befehl passt. Z.B. 00000070:00000011:0001 müsste in 112000000001700001 umformatiert werden.
4. alle Datenbank- und Transaktionsprotokollsicherungen in die richtige Sicherungskettenreihenfolge wiederherstellen, bis Sie den ausgesuchten Befehl erreichen:

   RESTORE LOG AdventureWorks2012
   FROM
       DISK = N'E:\ApexSQL\backups\AW2012_05232013.trn'
   WITH
       STOPBEFOREMARK = 'lsn:112000000001700001',
       NORECOVERY;

Verwendung von DBCC PAGE

Ein weiterer, wieder nicht dokumentierter, Befehl lautet DBCC PAGE. Dieser Befehl kann die Inhalte von Datenbankdateien, sowohl Datendateien als auch Transaktionsprotokolldateien, auslesen.

Die Syntax von DBCC PAGE lautet:

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

Um die erste Datenseite des Transaktionsprotokolls aus der AdventureWorks2012 Datenbank auszulesen, führen Sie Folgendes aus:

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

Die Ausgabe sieht wie folgt aus:

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

SQL Server gibt die Ergebnisse von DBCC PAGE nicht standardmäßig aus, daher müssen Sie zuerst Trace Flag 3604 einschalten:

DBCC TRACEON (3604, -1)

Und dann DBCC PAGE erneut ausführen:

DBCC PAGE (AdventureWorks2012, 2, 0, 2)

Es kommen zuerst einige Fehlermeldungen, die Sie ignorieren können, die dann von Binärdaten gefolgt werden. Diese sind die Daten aus dem Transaktionsprotokoll!

Die Binärdaten sehen aus wie die Daten in einem Hex-Editor, nur nicht ganz so komfortabel! Wenigstens haben Sie so auch noch die aktiven Daten.

Verwendung von ApexSQL Log

ApexSQL Log ist ein Programm zum Auslesen von Transaktionsprotokolldateien. Es kann sowohl Onlinedateien als auch Offlinedateien und zusätzlich noch Sicherungsdateien (komprimiert und nicht komprimiert) auslesen. ApexSQL Log ist ebenfalls in die Lage – falls notwendig – weitere Sicherungsdateien auszulesen, um alle Daten zu rekonstruieren. ApexSQL Log kann Daten- und Objektänderungen auslesen, selbst wenn diese Änderungen ausgeführt wurden, bevor ApexSQL Log installiert wurde. Anders als die oben erwähnten, nicht dokumentierten Funktionen, kann ApexSQL Log die ausgelesenen Datensätze auf einer einfachen Oberfläche präsentieren. So ist es leicht nachzuvollziehen, was und wann passiert ist, als auch wie die Daten vorher und nachher aussahen.

1. Starten Sie ApexSQL Log

2. Verbinden Sie sich mit der Datenbank, die ausgelesen werden soll:

   

3. Über Select SQL logs to analyze können Sie die Transaktionsprotokolle auswählen (Onlinedateien sowie Sicherungsdateien). Wählen Sie alle Protokolle und Sicherungen aus, die zu einer Protokollkette gehören

   

4. Um Transaktionsprotokollsicherungen und Offlinedateien hinzuzufügen, nutzen Sie die “Add” Taste.

5. Sie können die zu suchenden Transaktionen einschränken, indem Sie die Filter Setup Option nutzen. Hier können Sie über Zeitspanne, Befehlstyp, eine bestimmte Tabelle oder andere Filterkriterien die Suche eingrenzen.

   

6. Klicken Sie Open

   Alle Ergebnisse werden nun in ApexSQL Log angezeigt.

   Sie können sehen wann ein Befehl begonnen und beendet wurde, welchen Befehlstyp er hat, die Objekt- und Schemanamen des geänderten Objektes, der Benutzer, der den Befehl ausgeführt hat sowie den Rechner- und Applikationsnamen, der den Befehl ausgeführt hat. Bei UPDATE-Befehlen werden zudem die alten und neuen Werte angezeigt.

   

Um Binärdaten, nicht dokumentierte Funktionen, unklare Rückgabewerte und Spalten, lang laufende Abfragen, komplexe Wiederherstellungsschritte und unvollständige UPDATE und BLOB Werte zu vermeiden, nutzen Sie ApexSQL Log. So bekommen Sie die Ergebnisse im “Klartext”. Zudem können Skripte per Mausklick erzeugt werden, um Änderungen rückgängig zu machen bzw. Änderungen wieder durchzuführen.

Übersetzer: William Durkin