Восстановление удаленных данных из таблицы 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 Databasename
GO
BACKUP LOG [Databasename]
TO DISK = N'D:DatabasenameRDDTrLog.trn'
WITH NOFORMAT, NOINIT,
NAME = N'Databasename-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Шаг 3

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

USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation  = 'LOP_DELETE_ROWS'

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

Шаг 4

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

USE Databasename
GO
SELECT
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name],
[Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f3'
AND
[Operation] = 'LOP_BEGIN_XACT'

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

Шаг 5

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

USE Databasename
GO
RESTORE DATABASE Databasename_COPY FROM
DISK = 'D:DatabasenameRDDFull.bak'
WITH
MOVE 'Databasename' TO 'D:RecoverDBDatabasename.mdf',
MOVE 'Databasename_log' TO 'D:RecoverDBDatabasename_log.ldf',
REPLACE, NORECOVERY;
GO

Шаг 6

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

USE  Databasename
GO
RESTORE 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 tables
using 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. Но он сложен в реализации. Поэтому лучше использовать автоматизированные решения.

Пожалуйста, оставьте ваши мнения по текущей теме материала. За комментарии, лайки, дизлайки, подписки, отклики огромное вам спасибо!