Восстановление на момент времени в SQL Server
Пересказ статьи Greg Robidoux. SQL Server point in time recovery
В какой-то момент вредоносная команда может быть выполнена на одной из ваших баз данных, и вам потребуется восстановить потерянные данные. Имеется несколько вариантов действий, которые могут восстановить потерянные данные, но какой из них сделает это лучшим образом? Одним из вариантов, которые предлагает SQL Server, является возможность восстановление ваших данных на заданный момент времени, для того чтобы восстановить вашу базу к состоянию, непосредственно предшествовавшему выполнению вредоносной команды. Итак, какие шаги следует предпринять для восстановления на момент времени?
Возможность восстановления на любой момент времени имеет ключевое значение, когда вы хотите восстановить данные на момент, предшествующий транзакции, которая вызвала проблему. Для того, чтобы воспользоваться этой функциональностью, необходимо выполнить некоторые действия.
Чтобы иметь возможность восстановления на момент времени, ваша база данных должна находиться в режиме модели полного восстановления (Full) или в режиме c неполным протоколированием (Bulk-Logged). Если ваша база использует простую модель восстановления (Simple), данная функциональность работать не будет. Чтобы установить вашу базу в режим восстановления Full или Bulk-Logged, можно воспользоваться GUI или окном запросов:
Выполните одну из этих команд, чтобы поменять модель восстановления для вашей базы данных:
Чтобы установить модель восстановления для базы данных с помощью GUI, щелкните правой кнопкой на имени базы данных и выберите Properties (свойства).
Если база данных установлена в режим полного восстановления, у вас есть возможность восстановления на момент времени для всех резервных копий журнала транзакций. Для модели восстановления с неполным протоколированием, если у вас имеются какие-либо операции с неполным протоколированием в конкретном журнале транзакций, который вы пытаетесь использовать для восстановления на момент времени, процесс восстановления не удастся, а вы получите одну из приведенных ниже ошибок. Это связано с тем, что транзакции с неполным протоколированием таких операций, как create index и bulk insert, могут минимально журнализироваться и, поскольку не все данные этих операций присутствуют в журнале транзакций, восстановление на определенный момент времени невозможно.
Если попытаться использовать восстановление на момент времени и существуют операции с неполным протоколированием, вы получите такие сообщения об ошибках:
Для восстановления базы данных вы можете использовать окно запросов или административные инструменты SQL Server.
Вот команды для восстановления базы данных и одного журнала транзакций на Dec 19, 2022 11:26:05 AM. Первая полная резервная копия восстанавливается при использовании опции NORECOVERY, поэтому база данных остается в состоянии загрузки, и могут быть восстановлены дополнительные резервные копии. Вторая команда восстанавливает журнал транзакций на момент времени = 'Dec 19, 2022 11:26:05 AM'. Кроме того, мы используем опцию RECOVERY, чтобы перевести базу данных обратно в рабочее состояние после восстановления журнала транзакций.
Если нам нужно восстановить дополнительные журналы транзакций, это можно сделать с использованием опции NORECOVERY для каждого из последующих журналов транзакций, а параметр должен использоваться только для последнего восстанавливаемого файла.
При использовании SQL Server Management Studio GUI выполните нижеприведенные действия. Сначала необходимо выбрать все файлы резервных копий, которые вы хотите восстановить, а затем использовать опцию "Timeline" для восстановления к конкретному моменту времени.
На этом экране вы можете выбрать момент времен для восстановления.
Это почти все, что нужно сделать для восстановления на заданный момент времени. Осталось только выяснить к какому моменту времен восстанавливать журнал транзакций. В некоторых обстоятельствах вы можете знать точное время, когда что-то пошло не так, но в других случаях это может быть вам неизвестно. Тут могут помочь инструменты чтения журналов транзакций. Эти инструменты позволят вам увидеть все транзакции и время, когда они произошли, так что вы сможете определить точное время для восстановления вашей базы данных.
Ссылки по теме
Модель восстановления
Чтобы иметь возможность восстановления на момент времени, ваша база данных должна находиться в режиме модели полного восстановления (Full) или в режиме c неполным протоколированием (Bulk-Logged). Если ваша база использует простую модель восстановления (Simple), данная функциональность работать не будет. Чтобы установить вашу базу в режим восстановления Full или Bulk-Logged, можно воспользоваться GUI или окном запросов:
Команды T-SQL
Выполните одну из этих команд, чтобы поменять модель восстановления для вашей базы данных:
- ALTER DATABASE имя_базы_данных SET RECOVERY BULK_LOGGED
- ALTER DATABASE имя_базы_данных SET RECOVERY FULL
SQL Server Management Studio
Чтобы установить модель восстановления для базы данных с помощью GUI, щелкните правой кнопкой на имени базы данных и выберите Properties (свойства).
Если база данных установлена в режим полного восстановления, у вас есть возможность восстановления на момент времени для всех резервных копий журнала транзакций. Для модели восстановления с неполным протоколированием, если у вас имеются какие-либо операции с неполным протоколированием в конкретном журнале транзакций, который вы пытаетесь использовать для восстановления на момент времени, процесс восстановления не удастся, а вы получите одну из приведенных ниже ошибок. Это связано с тем, что транзакции с неполным протоколированием таких операций, как create index и bulk insert, могут минимально журнализироваться и, поскольку не все данные этих операций присутствуют в журнале транзакций, восстановление на определенный момент времени невозможно.
Если попытаться использовать восстановление на момент времени и существуют операции с неполным протоколированием, вы получите такие сообщения об ошибках:
Msg 4341, Level 16, State 1, Line 4
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
(Эта резервная копия журнала содержит изменения массовой загрузки. Ее нельзя использовать для остановки в произвольный момент времени.)
Msg 4338, Level 16, State 1, Line 4
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
(Предложение STOPAT указывает слишком раннюю точку для восстановления этого набора резервных копий. Выберите другую точку остановки или используйте RESTORE DATABASE WITH RECOVERY для восстановления на текущую точку.)
Msg 3013, Level 16, State 1, Line 4
RESTORE LOG is terminating abnormally.
(RESTORE LOG завершается аварийно.)Процесс восстановления
Для восстановления базы данных вы можете использовать окно запросов или административные инструменты SQL Server.
Вот команды для восстановления базы данных и одного журнала транзакций на Dec 19, 2022 11:26:05 AM. Первая полная резервная копия восстанавливается при использовании опции NORECOVERY, поэтому база данных остается в состоянии загрузки, и могут быть восстановлены дополнительные резервные копии. Вторая команда восстанавливает журнал транзакций на момент времени = 'Dec 19, 2022 11:26:05 AM'. Кроме того, мы используем опцию RECOVERY, чтобы перевести базу данных обратно в рабочее состояние после восстановления журнала транзакций.
RESTORE DATABASE DBUtil FROM DISK = 'C:\Backup\dbutil.bak' WITH NORECOVERY
RESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log.trn' WITH RECOVERY, STOPAT = 'Dec 19, 2022 11:26:05 AM' Если нам нужно восстановить дополнительные журналы транзакций, это можно сделать с использованием опции NORECOVERY для каждого из последующих журналов транзакций, а параметр должен использоваться только для последнего восстанавливаемого файла.
RESTORE DATABASE DBUtil FROM DISK = 'C:\Backup\dbutil.bak' WITH NORECOVERY
RESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log_1.trn' WITH NORECOVERY
RESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log_2.trn' WITH NORECOVERY
RESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log_3.trn' WITH RECOVERY, STOPAT = 'Dec 19, 2022 11:26:05 AM' При использовании SQL Server Management Studio GUI выполните нижеприведенные действия. Сначала необходимо выбрать все файлы резервных копий, которые вы хотите восстановить, а затем использовать опцию "Timeline" для восстановления к конкретному моменту времени.
На этом экране вы можете выбрать момент времен для восстановления.
Это почти все, что нужно сделать для восстановления на заданный момент времени. Осталось только выяснить к какому моменту времен восстанавливать журнал транзакций. В некоторых обстоятельствах вы можете знать точное время, когда что-то пошло не так, но в других случаях это может быть вам неизвестно. Тут могут помочь инструменты чтения журналов транзакций. Эти инструменты позволят вам увидеть все транзакции и время, когда они произошли, так что вы сможете определить точное время для восстановления вашей базы данных.
Ссылки по теме
- Примеры резервного копирования базы данных SQL Server
- Основы журнала транзакций в SQL Server
- 5 типов резервных копий в SQL Server
- Архитектура журнала транзакций SQL Server
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой