Czytanie logu transakcyjnego bazy danych SQL Server’a

Log transakcyjny SQL Server’a zawiera rekordy, opisujące modyfikacje wykonywane na bazie. Dzięki tym informacjom istnieje możliwość odtworzenia bazy do określonego punktu w czasie, możliwość ponownego wykonania modyfikacji lub jej wycofania. Powstaje jednak pytanie, jak odnaleźć określoną transakcję i ponownie ją wykonać albo jak odwrócić skutki jej działania, powstałe na przykład po przypadkowym skasowaniu rekordów w bazie.

Proste otwarcie pliku LDF lub TRN przy pomocy edytora binarnego pokazuje niezrozumiałe rekordy, których nie da się bezpośrednio przeczytać. Przykład pliku logu, otwartego w trybie binarnym widoczny jest poniżej:

Użycie fn_dblog

Jedną z możliwości przeczytania logu transakcyjnego daje funkcja fn_dblog. Jest to nieudokumentowana funkcja SQL Server’a, która umożliwia przeczytanie aktywnej części logu transakcyjnego.

Aby przeczytać log transakcyjny przy pomocy funkcji fn_dblog należy wykonać poniższe działania:

1. Wykonać zapytanie, które wykorzystuje funkcję fn_dblog.

Select * FROM sys.fn_dblog(NULL,NULL)



Zapytanie daje w wyniku 129 kolumn, więc wskazane jest wykonać je tak, aby otrzymać tylko niezbędne kolumny, a dodatkowo zawęzić liczbę otrzymanych wierszy do transakcji określonego typu, na przykład transakcji, które wstawiły rekordy do bazy.

2. Wykonać zapytanie, które uruchamia funkcję fn_dblog, zawężające liczbę kolumn i liczbę wierszy w zbiorze wynikowym.

Aby zobaczyć transakcje, które wstawiały rekordy do bazy (INSERT), należy wykonać zapytanie poniżej:

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



Aby zobaczyć transakcje, które usuwały rekordy do bazy (DELETE), należy wykonać zapytanie poniżej:

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



3. Następnie należy odnaleźć wartości wstawione lub skasowane – w tym celu przeglądamy w zbiorze wynikowym powyższego zapytania kolumny RowLog Contents 0 , RowLog Contents 1 , RowLog Contents 2 , RowLog Contents 3 , RowLog Contents 4, Description oraz Log Record.

Dane dla różnych typów operacji zapisane są w różnych kolumnach, więc niezbędna jest wiedza o tym, która kolumna zawiera informacje o konkretnym typie transakcji. Zadanie nie jest łatwe, ponieważ nie istnieje oficjalna dokumentacja dla funkcji fn_dblog.

Dodatkowo, informacje o wstawianych i kasowanych wierszach wyświetlane są w zapisie szesnastkowym. Wydobycie z tego zapisu danych w formacie zrozumiałym dla człowieka wymaga wiedzy na temat formatu zapisu, bitów statusu, liczby kolumn itd.

4. W kolejnym kroku skonwertować dane binarne na dane, które przechowywane są w tabeli, biorąc pod uwagę odpowiedni typ danych. Mechanizm konwersji jest różny dla różnych typów danych.

Funkcja fn_dblog ma swoje ograniczenia – czytanie logu dla obiektów o złożonej strukturze jest nietrywialne i zazwyczaj wymaga odtworzenia stanu kilku tabel systemowych. Dodatkowo funkcja pozwala czytać tylko aktywną część online’owego logu transakcyjnego. Funkcja nie zawsze pozwala na odtworzenie poleceń UPDATE lub danych typu BLOB.

Polecenie UPDATE jest logowane w logu transakcyjnym w sposób minimalistyczny, tzn. przechowywana jest tylko zmiana danych, a nie cała wartość oryginalna i nowa (np. SQL Server loguje, że została zmieniona litera „G” na literę „T” w określonym polu, podczas gdy faktycznie zamieniono ciąg znaków „GLOAT” na „FLOAT”). Skutkuje to koniecznością odtworzenia poprzedniego stanu danych sprzed aktualizacji, co z kolei wymaga odtworzenia wszystkich pośrednich stanów miedzy wstawieniem wiersza do strony i aktualizacją, którą próbujemy odtworzyć.

Gdy kasujemy daną typu BLOB, skasowana dana nie jest wstawiana do logu transakcyjnego, więc czytanie rekordów logu dla poleceń DELETE BLOB nie umożliwi przywrócenia skasowanej danej BLOB. Odtworzenie takiej danej jest możliwe tylko, gdy istnieje w logu rekord dla polecenia INSERT dla skasowanej danej typu BLOB, czyli tylko gdy istnieje para poleceń INSERT/DELETE będzie można odtworzyć skasowany obiekt typu BLOB przy pomocy funkcji fn_dblog.

Użycie fn_dump_dblog

Funkcja fn_dump_dblog umożliwia przeczytanie kopii zapasowej logu transakcyjnego (także skompresowanej kopii zapasowej logu), nawet jeżeli nie mamy dostępu do bazy danych online. Podobnie jak to było w przypadku funkcji fn_dblog, funkcja fn_dump_dblog nie jest udokumentowana.

1. Uruchom funkcję fn_dump_dblog na określonej kopii zapasowej logu transakcyjnego. Dla funkcji fn_dump_dblog trzeba podać wszystkie 63 parametry.

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



Podobnie jak w przypadku funkcji fn_dblog, w wyniku dostaniemy 129 kolumn, więc wskazane jest wyspecyfikowanie tylko tych kolumn, które są niezbędne.

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

Analogicznie jak poprzednio, należy odczytać dane zapisane w systemie szesnastkowym, aby dotrzeć do poszukiwanych informacji.



Tak jak to było w przypadku funkcji fn_dblog, jesteśmy w punkcie wyjścia – trzeba odtworzyć wszystkie wartości ręcznie, wszystkie stany w łańcuchu wykonanych poleceń UPDATE, poleceń na danych typu BLOB itd.

Jeśli jednak nie chcemy wydobyć pojedynczych transakcji z kopi zapasowej logu transakcyjnego, ale odtworzyć bazę do punktu w czasie, zanim pojawiła się konkretna niepożądana operacja, to możemy:

2. Określić numer LSN dla transakcji.
3. Przekonwertować numer LSN na format używany w parametrze WITH STOPBEFOREMARK = ‘<mark_name>’ , np. wartość 00000070:00000011:0001 powinna zostać przekształcona w wartość 112000000001700001.
4. Odtworzyć pełną kopię logu aż do momentu zdefiniowanego w WITH STOPBEFOREMARK = ‘<mark_name>’, który określa odpowiednia transakcję

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

Użycie DBCC PAGE

Kolejną interesującą ale nadal nieudokumentowaną komendą jest DBCC PAGE. Można ją wykorzystać do czytania zawartości plików MDF i LDF bazy danych. Składnia komendy widoczna jest poniżej:

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

Aby przeczytać pierwszą stronę danych logu transakcyjnego bazy AdventureWorks2012 należy wykonać polecenie poniżej:

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

W wyniku otrzymamy:

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

Wynik działania komendy domyślnie nie jest wyświetlany w SQL Server Management Studio. Aby go zobaczyć należy najpierw ustawić flagę 3604:

DBCC TRACEON (3604, -1)

I ponownie wykonać:

DBCC PAGE (AdventureWorks2012, 2, 0, 2)

W wyniku otrzymamy szesnastkowe dane dla odczytanego logu transakcyjnego:

Wynik, podobnie jak w przypadku opisanych powyżej funkcji nie jest czytelny, chociaż daje dostęp do danych online.

Użycie ApexSQL Log

ApexSQL Log jest narzędziem do czytania logu transakcyjnego , które umożliwia czytania logów online, logów odłączonych oraz kopii zapasowych logu transakcyjnego (także skompresowanego). Jeżeli istnieje taka potrzeba, ApexSQL Log umożliwia czytanie kopii zapasowych bazy danych, aby pozwolić na właściwe odtworzenie danych. Można ponownie wykonać transakcje i zmiany danych wykonane wcześniej na bazie, włączając te, które zostały wykonane zanim ApexSQL Log został zainstalowany. W przeciwieństwie do metod opisanych wcześniej, ApexSQL Log przedstawia informacje o działaniach wykonanych na bazie, zmienionych obiektach, oryginalnych i zmienionych wartościach danych w sposób czytelny dla człowieka.

1. Należy uruchomić ApexSQL Log

2. Podłączyć się do bazy, której log transakcyjny chcemy przeczytać:

   

3. W kroku wyboru logu do analizy wybrać logi, które należy przeczytać. Trzeba oczywiście wybrać wszystkie logi w pełnym łańcuchu logów.

   

4. Dodać kopie zapasowe logów lub odłączone pliki LDF przyciskiem Add.

5. Użyć opcji Filter setup do zawężenia zakresu czasu, typu operacji, tabel i innych opcji dla czytanych transakcji.

   

6. Kliknąć na przycisk Open.

   W pełni czytelny rezultat działań będzie widoczny oknie ApexSQL Log

   Dla każdej operacji będzie można zobaczyć typ operacji, czas jej wykonania (czas rozpoczęcia i czas zakończenia), schemat oraz nazwę obiektu, dla którego dane były zmienione, nazwę użytkownika, który wykonał operację, nazwę komputera oraz aplikację wykorzystaną do wykonania działania. Dodatkowo, dla poleceń UPDATE, będzie widać wartość oryginalną i wartość zmienioną.

   

Narzędzie ApexSQL Log. umożliwia uniknięcie korzystania z nieudokumentowanych funkcji, konieczności czytania wartości szesnastkowych, czytania długich zapytań, skomplikowanej procedury odtwarzania danych, niekompletnych poleceń UPDATE i niemożności odtworzenia danych typu BLOB. ApexSQL Log czyta log transakcyjnym i prezentuje dane w sposób czytelny dla człowieka. Dodatkowo, możliwe jest utworzenie skryptów undo i redo przy pomocy jednego kliknięcia.

Tłumacz: Anna Lesniak