При обслуживании баз данных 1С на платформе Microsoft SQL Server администраторы нередко сталкиваются с необходимостью отката состояния информационной базы на определенный момент времени (Point-in-Time Recovery). Однако попытка восстановить данные за произвольный час при наличии только полных резервных копий часто приводит к ошибкам последовательности номеров транзакций (LSN). Разберем, как устроены цепочки бэкапов в MS SQL, почему большой размер файла журнала (LDF) не гарантирует возможность отката и как правильно организовать резервное копирование.
Рассмотрим распространенное заблуждение: если база данных работает в полной модели восстановления (Full Recovery Model) и файл журнала транзакций (LDF) занимает десятки гигабайт, предполагается, что в нем хранятся все изменения за длительный период. На практике это не так.
Проанализируем физическую структуру файла LDF. Журнал транзакций состоит из множества виртуальных файлов журнала — VLF (Virtual Log Files). Механизм работы журнала в СУБД функционирует следующим образом:
VLF помечаются как неактивные.Full ни разу не настраивалось регулярное создание резервных копий журнала (BACKUP LOG), СУБД работает в псевдо-простом режиме: при возникновении контрольных точек (Checkpoints) неактивные VLF усекаются и перезаписываются по кругу.Для восстановления базы данных на произвольную секунду или минуту критически важно иметь непрерывную цепочку бэкапов журнала транзакций (файлы .trn), связывающую последний полный бэкап с целевым моментом времени.
При попытке последовательно накатить резервную копию лога на полный бэкап администратор может получить сообщение об ошибке: «Журнал в этом резервном наборе данных начинается с номера LSN, который еще не может применяться к базе данных».
Выясним причину такого поведения СУБД. Каждый шаг изменения данных в SQL Server нумеруется уникальным последовательным значением — LSN (Log Sequence Number). Чтобы восстановить базу на промежуточную точку, последовательность LSN должна быть строго непрерывной:
.trn содержит только текущие активные VLF, и его начальный LSN оказывается значительно больше, чем конечный LSN недельного бэкапа. СУБД не может применить такие данные из-за отсутствия промежуточной истории.Разберем классическую схему восстановления цепочки резервных копий MS SQL Server. В штатном режиме цепочка состоит из полной копии (Full), разностных копий (Differential) и резервных копий журнала транзакций (Log):
WITH NORECOVERY, чтобы база оставалась в состоянии ожидания накатывания следующих архивов.WITH NORECOVERY)..trn, созданные после восстанавливаемой дифференциальной копии. При восстановлении последнего фрагмента указывается целевое время (STOPAT) и параметр WITH RECOVERY, переводящий базу в рабочий режим.Посмотрим на пример T-SQL скрипта для правильного восстановления базы с откатом на определенное время:
RESTORE DATABASE [BUH_RESTORE]
FROM DISK = 'D:\Backup\sql_buh_full.bak'
WITH NORECOVERY, REPLACE;
RESTORE DATABASE [BUH_RESTORE]
FROM DISK = 'D:\Backup\sql_buh_diff.bak'
WITH NORECOVERY;
RESTORE LOG [BUH_RESTORE]
FROM DISK = 'D:\Backup\sql_buh_log1.trn'
WITH NORECOVERY;
RESTORE LOG [BUH_RESTORE]
FROM DISK = 'D:\Backup\sql_buh_log2.trn'
WITH STOPAT = '2017-12-19 04:30:00', RECOVERY;
Частая ошибка системных администраторов — создание внепланового полного бэкапа перед проведением опасных операций без дополнительных параметров. Обычный BACKUP DATABASE сбрасывает флаги измененных страниц и становится новой основой (Base) для всех последующих разностных бэкапов, что может нарушить автоматический план обслуживания.
Рассмотрим, как правильно создавать внеплановые резервные копии. Для создания изолированной копии, которая не сбивает цепочку дифференциальных архивов и не влияет на последовательность LSN, обязательно используется параметр WITH COPY_ONLY:
BACKUP DATABASE [sql_buh]
TO DISK = 'D:\Backup\sql_buh_manual_copy.bak'
WITH COPY_ONLY, COMPRESSION, STATS = 10;
Если цепочка LSN разорвана, а восстановить данные на нужный момент времени на уровне SQL невозможно, проанализируем доступные альтернативные варианты действий:
sys.fn_dblog: с помощью негласной системной функции можно проверить, сохранились ли в живом файле LDF записи за нужный период, и оценить возможность генерации компенсирующих запросов (UNDO) через специализированные утилиты анализа логов.Чтобы гарантировать возможность отката базы данных на любую точку во времени, необходимо придерживаться следующих правил администрирования:
BACKUP LOG [sql_buh]
TO DISK = 'D:\Backup\sql_buh_tail.trn'
WITH NORECOVERY;