В статье Recover deleted SQL data from a backup or from online database files, мы рассмотрели два сценария восстановления: один из полной резервной копии, а другой из файла данных SQL Server – mdf-файла. Но когда дело доходит до восстановления данных вы можете использовать ещё один ресурс – это журнал транзакций.
Любые изменения, совершённые в БД, помещаются в журнал транзакций в виде отдельных записей. Если ваша транзакция на удаление находится в активном журнале транзакций, в отсоединённом (detached) файле журнала транзакций или в резервной копии журнала транзакций, то вы можете прочитать эту транзакцию и, при желании, даже отменить.
Рассмотри разные модели восстановления:
- При полном режиме восстановления (Full recovery model) транзакция на удаление содержится в активном журнале транзакций, либо в резервной копии журнала транзакций, если эта резервная копия была сделана уже после операции удаления.
- В режиме восстановления c неполным протоколированием (Bulk_logged recovery model) транзакция полностью записывается в журнал транзакций за исключением BLOB-данных. Т.к. при этом режиме восстановления удаление BLOB-объектов не журналируется, то и восстановить их будет невозможно. Все прочие операции удаления можно прочитать либо из активного журнала транзакций, либо из резервных копий журнала, сделанных после удаления.
- Простая модель восстановления (Simple recovery model). Если в БД большая активность и после вашей операции удаления произошло достаточно большое количество операций над данными, то ваша транзакция в журнале транзакций уже будет перезаписана и восстановить данные будет невозможно. Поэтому чем раньше вы обратитесь к данным в активной части журнала транзакций, тем больше шансов вернуть удалённые данные.
ApexSQL Log
Для чтения активных журналов транзакций, отдельных журналов транзакций, резервных копий журналов транзакций (как сжатых, так и обычных) используйте приложение ApexSQL Log. Это именно та программа, которая умеет не только читать данные в журналах транзакций, но и повторно выполнять команды, которые попали в эти журналы, а также отменять их, даже те команды, которые были выполнены в БД до установки ApexSQL Log. Кроме того, эта утилита может показать из какого приложения произошли изменения в БД или получить имя пользователя совершившего изменения.
- Запустите ApexSQL Log
-
Подключитесь к БД, в которой произошло удаление.
- Если после удаления данных создавались резервные копии журнала транзакций, то с помощью кнопки Add необходимо все их добавить на следующем шаге. Обратите внимание, что вы должны сформировать всю цепочку из резервных копий, если ваши данные содержат BLOB-объекты (более подробно будет рассмотрено ниже).
-
Если у вас есть отсоединённый (detached) журнал транзакций, который содержит удалённые записи (например, вы после удаления перевели БД в offline и скопировали LDF-файл), то вы так же можете добавить его в поиск:
-
Чтобы сузить поиск на шаге Filter setup в разделе Time range укажите временной интервал, когда произошла операция удаления.
-
В разделе Operations укажите, что поиск идёт только среди транзакций на удаление. Для этого снимите выделение с операций Insert row и Update row в списке Data operations (DML).
-
По возможности, укажите таблицы из которых произошло удаление, если нет, то оставьте все таблицы
- Нажмите открыть (Open).
-
Все удалённые записи будут представлены в виде таблицы. Всё, что вам теперь нужно, это выбрать все или конкретные записи, которые вы хотите восстановить и нажать в меню Create Undo Script, чтобы создать сценарий отмены удаления.
-
Чтобы создать сценарий отмены для всех записей в таблице нажмите в меню Create undo script. Чтобы создать сценарий только для некоторых записей – выберите их в сводной таблице и в контекстном меню выберите пункт Create undo script.
После этого будет создан сценарий, который восстановит удалённые записи.
- Чтобы выполнить скрипт восстановления в меню нажмите кнопку
Быстрое создание сценария отмены с помощью ApexSQL Log
Описанный способ с результатами, показанными в таблице является приемлемым только для небольшого количества записей. Когда дело доходит до миллионов удаленных записей, мы рекомендуем сразу создать сценарий отмены, вместо того чтобы показывать результаты в сводной таблице. Шаги с 1 по 7 остаются такие же, как выше
-
Вместо того, чтобы открыть результат в виде таблицы (Open results in grid) нажмите экспортировать результат в файл (Export results to a file)
-
Выберите создание сценария отката (Generate rollback (undo) script) и укажите путь и имя файла
- Нажмите ОК.
Сценарий отмены, который вернёт все удалённые записи, будет создан.
ApexSQL Recover
Ещё один инструмент ApexSQL, который умеет работать с журналами транзакций SQL Server, как активными, так и отдельными LDF-файлами, умеет читать резервные копии журналов транзакций обычные и сжатые. ApexSQL Recover восстанавливает удалённые данные (delete, truncate) и объекты (drop). Кроме того, этот инструмент может восстановить удалённые BLOB-данные. Процесс восстановления очень похож на тот, что описан здесь Recover deleted SQL data from a backup or from online database files – Use ApexSQL Recover
- Запустите ApexSQL Recover
- Выберите восстановление данных после операции удаления (Recover lost data due to a DELETE operation).
- Укажите экземпляр SQL Server, учётные данные и БД, где произошло удаление.
- Укажите источники для восстановления (Several sources are available for the recovery).
- Активный журнал транзакций, все резервные копии журналов транзакций, а также полная резервная копия БД будут выбраны автоматически. Если у вас есть дополнительные журналы транзакций или резервные копии, то вы так же можете их добавить с помощью кнопки Add. Обратите внимание, что вам необходимо сформировать полную цепочку из резервных копий для успешного восстановления потерянных данных.
- Укажите время, когда было совершено удаление, если вы не знаете, когда это произошло, то укажите Date and time are unknown.
- Используйте фильтр, чтобы сузить поиск, указав названия конкретных таблиц, если это возможно.
- Нажмите восстановить (Recover).
- Сценарий восстановления будет создан и вам будет достаточно его только выполнить.
Хорошая новость заключается в том, что у вас есть шанс восстановить удалённые данные, даже без полной резервной копии или без резервной копии журнала транзакций, т.е. даже если у вас нарушена цепочка из резервных копий. При этот, обратите внимание, что восстановление больших двоичных данных (BLOB) невозможно без полной цепочки из резервных копий. Это связано с особенностью работы с BLOB-данными, т.к. при удалении сами данные не помещаются в журнал транзакций, а лишь делается пометка о их удалении. Для восстановления нужно пройти по всей последовательности резервных копий данных к самому началу цепочки. Если цепочка нарушена, то невозможно восстановить всю картину событий. Например, если информация об обновлении BLOB-данных была потеряна, то после удаления мы восстановим первоначально созданное значение, без внесённых в середине цепочки обновлений.
Даже если у вас простая модель восстановления у вас есть шанс вернуть потерянные данные. В простой модели восстановления все новые транзакции перезаписывают в журнале транзакций старые операции. Например, у вас журнал транзакций размером 100 Гб, при этом дневной объём изменений 1 Гб, то полностью журнал будет перезаписан только через 100 дней. Это означает, что вы даже при простой модели восстановления сможете восстановить данные удалённые 100 дней назад.
Конечно же это не означает, что вы должны полагаться только на это и можно полностью забыть о вашей стратегии аварийного восстановления SQL Server.
ApexSQL имеет два прекрасных инструмента, которые могут помочь при работе с журналами транзакций. ApexSQL Log позволяет более детально отфильтровать искомые записи в журнале транзакций. Помимо фильтров по времени и имени объектов, которые присутствуют в обоих инструментах, ApexSQL Log может фильтровать по системным объектам, на базе значения определённого столбца и даже указать пользователя, который совершал транзакцию. А если в момент совершения транзакции был запущен ApexSQL Log Connection monitor, то и фильтр по приложению (application) и хосту (host name).
ApexSQL Recover в своём арсенале имеет меньше фильтров, но зато это более мощный инструмент для восстановления. Помимо создания сценария на T-SQL для восстановления удалённых данных, это приложение умеет создавать новую базу данных и восстанавливать удалённые записи прямо в неё.
Переводчик: Алексей Князев
November 20, 2015
