Восстановление удаленных данных из таблицы SQL с помощью журнала транзакций и номера LSN

Использование операции UPDATE или DELETE без условия WHERE часто приводит к потере данных из таблиц. Удаленные строки могут быть восстановлены, если известно время их удаления. Это можно без труда сделать при помощи порядковых номеров регистрации(LSN). Это уникальный идентификатор, который присваивается каждой записи в журнале транзакций SQL Server. В следующем разделе статьи мы рассмотрим восстановления удаленных данных при помощи журнала транзакций и номеров LSN.

Восстановление данных, удаленных из таблицы SQL Server при помощи журналов транзакций

Чтобы восстанавливать строки, удаленные из таблицы, база данных SQL Server должна иметь BULK-LOGGED или FULL модель восстановления в момент первого удаления. Чтобы журналы были доступны для восстановления данных, надо выполнить следующие действия(применимо в SQL Server 2005, 2008, 2012, 2014 и 2016):

Шаг 1

Проверьте число строк в таблице базы данных, из которой были случайно удалены данные, используя следующий запрос:

SELECT * FROM Table_name

Шаг 2

Получите резервную копию журнала транзакций базы данных, используя приведенный ниже запрос:

USE DatabasenameGOBACKUP LOG [Databasename]TO DISK = N'D:DatabasenameRDDTrLog.trn'WITH NOFORMAT, NOINIT,NAME = N'Databasename-Transaction Log Backup',SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

Шаг 3

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

USE DatabasenameGOSelect [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitNameFROMfn_dblog(NULL, NULL)WHERE Operation = 'LOP_DELETE_ROWS'

В результате работы запроса мы получим идентификатор транзакции(к примеру, 000: 000001f3) для удаленных строк.

Шаг 4

Определим конкретное время, когда строки были удалены. Для этого мы используем идентификатор транзакции 000: 000001f3. Выполните следующий запрос:

USE DatabasenameGOSELECT[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name],[Transaction SID]FROMfn_dblog(NULL, NULL)WHERE[Transaction ID] = ‘000:000001f3'AND[Operation] = 'LOP_BEGIN_XACT'

В результате выполнения этого запроса мы получим значение текущего порядкового номера журнала(LSN). К примеру, 00000020: 000001d0: 0001.

Шаг 5

Сейчас запустим процесс восстановления строк таблицы SQL Server. Для этого используем приведенный ниже запрос:

USE DatabasenameGORESTORE DATABASE Databasename_COPY FROMDISK = 'D:DatabasenameRDDFull.bak'WITHMOVE 'Databasename' TO 'D:RecoverDBDatabasename.mdf',MOVE 'Databasename_log' TO 'D:RecoverDBDatabasename_log.ldf',REPLACE, NORECOVERY;GO

Шаг 6

Сейчас применим журнал транзакций для восстановления удаленных строк, используя LSN 00000020: 000001d0: 0001:

USE DatabasenameGORESTORE LOG Databasename_COPY FROM DISK = N'D:DatabasenameRDOTrLog.trn'WITH STOPBEFOREMARK = ‘lsn:0x00000020:000001d0:0001' Note: Since LSN values are in Hexadecimal form and for restoring tablesusing this LSN, we need to convert it into decimal form. For this purpose, we add 0x just before the LSN as shown above.

Шаг 7

Процесс восстановления записей успешно завершен. Проверьте, восстановлены ли удаленные записи в базе данных с именем Databasename_Copy.

USE Databasename_Copy GO Select * from Table_name

Недостатки подхода с использованием журнала транзакций

  • Трудоемкий способ восстановления данных, поскольку использует пару объемных запросов.
  • Сложный в реализации для посетителей, которые не обладают достаточными техническими знаниями.
  • Вероятность потерять данные из-за ошибок при выполнении запросов.

Заключение

Журнала транзакций поможет восстановить записи, удаленные из таблиц SQL. Но он сложен в реализации. Так что лучше использовать автоматизированные решения.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *