Просмотр содержимого LDF-файла

Любая база данных SQL Server состоит из нескольких файлов операционной системы. Это файлы с данными и файлы журнала транзакций. Отдельные файлы могут относиться только к одной базе данных, при этом информация из файлов данных никогда не смешивается с информацией из журнала транзакций в одном физическом файле. Все данные БД хранятся в файлах MDF, а все транзакции и изменения в базе данных фиксируются в LDF-файла, т.е. в файлах журнала транзакций, который является очень важным компонентом базы данных. По сути, файл журнала транзакций представляет собой строку записей журнала. Физически записи журнала хранятся в одном или нескольких LDF-файлах, которые и образуют журнал транзакций.

Основная цель файла LDF – обеспечить концепцию ACID (атомарность, согласованность, изолированность, долговечность).

• Атомарность (Atomicity) – Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной.
• Согласованность (Consistency) – Cистема находится в согласованном состоянии до начала транзакции и должна остаться в согласованном состоянии после завершения транзакции.
• Изолированность (Isolation) – Во время выполнения транзакции другие процессы не должны видеть данные в промежуточном состоянии. Параллельные транзакции приводят базу данных в состояние, как будто транзакции совершались последовательно, одна за другой.
• Долговечность (Durability) – Если пользователь получил подтверждение от системы, что транзакция выполнена, он может быть уверен, что сделанные им изменения не будут отменены из-за какого-либо сбоя.

LDF-файл содержит в себе достаточно информации, чтобы воспроизвести или отменить изменения или, даже, восстановить базу данных на определённый момент времени. Поэтому, в рамках аудита или с целью восстановления, может возникнуть потребность открыть LDF-файл и просмотреть его содержимое. Но просмотр содержимого LDF-файла не является простой задачей.

Есть несколько функций и команд SQL Server (например, fn_dblog, fn_dump_dblog и DBCC PAGE), которые потенциально обеспечивают возможность просмотра содержимого файла LDF. Тем не менее, чтобы использовать их вам потребуются хорошие знания по T-SQL, некоторые из функций являются недокументированными и тот набор данных, который они возвращают очень трудно перевести в человеческий вид. Ниже приведены примеры просмотра содержимого файла LDF с использованием функций и команд SQL Server:

1. Вот пример использования функции fn_dblog для просмотра активного журнала транзакций, которая в качестве результата возвращает 129(!) столбцов (только 7 из них представлены на рисунке ниже).

   

2. Функция fn_dump_dblog используется для чтения резервной копии журнала транзакций в обычном и сжатом виде. Результат аналогичен:

   

   К сожалению, официальная документация не доступна для функций fn_dblog и fn_dump_dblog. Чтобы перевести столбцы в читабельный вид, вы должны быть знакомы с внутренней структурой и форматом данных, знать служебные флаги и алгоритмом хранения.

3. DBCC PAGE используется для чтения содержимого базы данных – файлов MDF и LDF. На выходе шестнадцатеричное представление, которое так же очень трудно интерпретировать

   

Чтение содержимого журнала транзакций с помощью ApexSQL Log

ApexSQL Log – это инструмент SQL Server, который может читать активные журналы транзакций, отдельные LDF-файлы, и резервные копии журналов транзакций, как обычных, так и хранящихся в сжатом виде. Инструмент просматривает все операции (DML и DDL, общее количество 45) и анализирует, какие именно данные были изменены с помощью этих операций. Кроме того, вы можете просмотреть логическое содержание LDF-файла, создавать Undo/Redo скрипты, историю модификации любой строки в базе данных (DML) и многое другое.

Чтобы с помощью ApexSQL Log открыть LDF-файл и просмотреть его содержимое:

1. Подключитесь к БД, которой принадлежит LDF-файл.

   

2. На следующем шаге необходимо добавить все журналы транзакций и отдельные LDF-файлы, информацию которых вы хотите прочитать. Убедитесь, что они образуют полную цепочку журнала. Цепочкой является непрерывная последовательность ВСЕХ резервных копий журнала транзакций. Она начинается с полной резервной копии базы данных и дополняется всеми резервными копиями журнала. Если эта цепочка будет нарушена, то вы сможете получить полную информацию только от момента создания полной резервной копии до точки разрыва (такой информацией может быть история изменения записи в таблице, изменение структуры объекта и т.д.).

   В отличии от операций INSERT и DELETE, которые полностью логируются, операции UPDATE записываются в журнал транзакций минимально, только то, что изменилось, т.е. в журнале нет всей строки до и после операции UPDATE. Например, при изменении значения “log” на “blog” в журнал запишется только значение “b” с указанием позиции 0. Этого достаточно, чтобы обеспечить требования ACID, но недостаточно, чтобы показать строку до и после изменений. Для того, чтобы наглядно получить изменения, которые произошли в БД по журналу транзакций (активной его части и резервным копиям), ApexSQL Log должна реконструировать все события в контексте произошедших изменений.

   Для достижения этой цели ApexSQL Log необходимо пройтись по всей цепочке журнала транзакций, включая последнюю полную резервную копию всей БД и последнюю разностную копию БД. Проглядеть все резервные копии журнала транзакций и другие доступные файлы журнала транзакций и достать оттуда все операции, связанные с одной конкретной операцией UPDATE. Найти, когда значение было “log” и в какой момент в позиции 0 был добавлен символ “b”, после чего образовалось новое значение “blog”.

   Кроме того, если цепочка образована из достаточного количества резервных копий журнала транзакций, то поиск информации может быть осуществлён быстрее, чем по одному большому куску активного журнала транзакций. ApexSQL Log умеет читать и отдельные LDF-файлы, которые вы можете указать, за счёт этого может быть гарантирована дополнительная целостность и неразрывность цепочки.

   Для этого используйте кнопку ADD на шаге Select SQL logs to analyze

   

3. На вкладке Database backups укажите полную резервную копию базы данных, именно от неё начнется цепочка транзакций.

   

4. На шаге Filter setup укажите временной интервал (Time range), когда ваша операция произошла. Это поможет ускорить поиск.

   

5. Когда все настройки установлены, нажмите кнопку Open, чтобы начать чтение LDF-файлов.
   Когда процесс закончится, то в сводной таблице ApexSQL Log появится информация о всех транзакциях из указанных источников и с учётом всех фильтров, которые были установлены.

   

Вместо заключения:

Существует несколько способов чтобы открыть и просмотреть содержимое LDF-файлов. Но все их объединяет одно – информацию тяжело использовать, т.к. она хранится в плохочитаемой структуре.

Но благодаря инструменту ApexSQL Log вы можете получить информацию в человеческом виде: получить полный список транзакций, весь перечень операций, имена и схемы объектов, точное время, когда произошла транзакция, кто её выполнил и многое другое.

Основные преимущество ApexSQL Log:

• Наглядная информация, полученная и конвертированная из 129 «родных» колонок с шестигранными и двоичными значениями.
• Объединение разных источников с данными журнала транзакций в один общий документ.
• Объединение активного журнала транзакций с его резервными копиями и отдельными файлами LDF, для получения более подробной информации.
• Полностью реконструирует операции UPDATE, в том числе выводит строку до и после операции изменения.
• Показывает историю всех операций DML над строками, включая время и пользователя, который вносил изменения.
• Различные фильтры обеспечивают быстрый поиск информации.
• Позволяет получить идентификатор старой таблицы, которую уже удалили, чтобы восстановить записи.
• Чтение и работа с LDF-файлами не требует знаний T-SQL.

Что ещё умеет ApexSQL Log:

• Табличное представление позволяет сортировать, фильтровать и осуществлять поиск по уже полученному списку транзакций
• Кроме таблицы данные можно получить и в других форматах (CSV, HTML, XML или SQL). Выгрузить и сохранить для последующего анализа.
• Позволяет создавать сценарий отката или повторного применения сценария. Это может быть полезно, когда требуется восстановить изменённые или удалённые данные, без восстановления всей БД из резервной копии.
• Есть официальная документация с описанием функционала
• Техническая поддержка и многое другое

Автор: Иван Станкович

Переводчик: Алексей Князев