блог о bi, №1 в рунете
Описание и настройка резервного копирования для транзакционных баз на MS SQL Server
Введение
На практике администраторы баз данных часто сталкиваются с ситуацией, когда необходимо восстановить БД. Такая потребность может возникнуть как в результате аварии на сервере, так и в результате банальной ошибки разработчика БД, который неаккуратным запросом очистил таблицы с критически важными для бизнеса данными. Чтобы обеспечить выход из подобных ситуаций с минимальными потерями, необходимо правильно настроить регулярное бэкапирование. И если в случае хранилищ данных достаточно иметь на руках ежедневные бэкапы, так как в течение дня данные меняются незначительно, то в случае транзакционных БД этого будет мало.
Дело в том, что транзакционные БД, в отличие от хранилищ данных, характеризуются непрерывной обработкой большого количества транзакций и постоянными изменениями в данных. Для таких БД потеря данных за полдня будет весьма критичной, а в некоторых случаях недопустима даже потеря данных за минуту. В этой ситуации требуется иметь возможность восстановить наиболее актуальные данные, максимально приближенные по времени к моменту аварии. Иными словами, необходимо реализовать такую стратегию резервного копирования, которая позволит восстановить БД на определённый произвольный момент времени и минимизировать потери данных.
Мы, как компания, которая имеет дело с большим количеством корпоративных БД на MS SQL Server, озаботились этим вопросом, и, изучая документацию Microsoft, а также иные источники, сформировали свою инструкцию по настройке резервного копирования транзакционных баз и процессу восстановления на определенный момент времени.
Настройка задания по резервному копированию
Подготовка базы данных
Для настройки резервного копирования, позволяющего восстановить БД на определенный момент времени, модель восстановления БД должна быть полной.
Чтобы узнать модель восстановления вашей БД, необходимо запустить следующий скрипт:
Мы, как компания, которая имеет дело с большим количеством корпоративных БД на MS SQL Server, озаботились этим вопросом, и, изучая документацию Microsoft, а также иные источники, сформировали свою инструкцию по настройке резервного копирования транзакционных баз и процессу восстановления на определенный момент времени.
Настройка задания по резервному копированию
Подготовка базы данных
Для настройки резервного копирования, позволяющего восстановить БД на определенный момент времени, модель восстановления БД должна быть полной.
Чтобы узнать модель восстановления вашей БД, необходимо запустить следующий скрипт:
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'YourDatabaseName';
Если модель восстановления вашей БД отлична от FULL, то необходимо переключить её на FULL с помощью следующего скрипта:
USE [master];
GO
ALTER DATABASE [YourDataBaseName]
SET RECOVERY FULL;
GO
Так как переключение на модель полного восстановления вступит в силу только после создания первой резервной копии данных, сразу после переключения требуется сделать полную РК, например, с помощью следующего скрипта:
BACKUP DATABASE [YourDatabaseName] TO DISK = N'C:\Backup\Full\YourDatabaseName\YourDatabaseName_backup_YYYY_MM_DD_HHMMSS.bak' WITH NOFORMAT, NOINIT, STATS = 10;
GO
Настройка задания по созданию РК
Для баз данных с полной моделью восстановления необходимо настроить задание по регулярному резервному копированию. В задание необходимо включить создание полных РК и РК транзакционного журнала. В противном случае журнал транзакций начнет разрастаться до тех пор, пока не переполнит диск. Один из вариантов настройки задания по РК приведен ниже.
Для настройки задания по резервному копированию требуется создать план обслуживания:
1) Подключиться к SQL Server через SQL Server Management Studio;
2) Открыть редактор планов обслуживания (Management -> Maintenance Plans -> New Maintenance Plan);
Для баз данных с полной моделью восстановления необходимо настроить задание по регулярному резервному копированию. В задание необходимо включить создание полных РК и РК транзакционного журнала. В противном случае журнал транзакций начнет разрастаться до тех пор, пока не переполнит диск. Один из вариантов настройки задания по РК приведен ниже.
Для настройки задания по резервному копированию требуется создать план обслуживания:
1) Подключиться к SQL Server через SQL Server Management Studio;
2) Открыть редактор планов обслуживания (Management -> Maintenance Plans -> New Maintenance Plan);
3) Настроить подплан для ежедневного полного бэкапирования:
- Задать расписание для подплана – каждый день в определенное время;
- Добавить задание «BackUp Database Task» из панели инструментов. Параметры задания следующие:
- Добавить задание «Maintenance Cleanup Task» для отчистки директории с полными РК в зависимости от выбранной глубины хранения полных РК. Например, в случае необходимости хранить последние 6 бэкапов, параметры задания будут следующими:
Задание должно выполняться только в случае успешного выполнения задания по созданию полного бэкапа;
- Добавить задание «Maintenance Cleanup Task» для отчистки директории с бэкапами транзакционного журнала в зависимости от выбранной глубины хранения РК транзакционного журнала. Например, в случае необходимости восстановления данных на произвольный момент времени за последние 2 дня, параметры задания будут следующими:
4) Настроить подплан для ежечасного создания РК транзакционного журнала:
- Задать расписание для подплана – каждый день ежечасно. В зависимости от потребностей бизнеса можно делать бэкапы ТЖ чаще, вплоть до ежеминутного запуска подплана;
- Добавить задание «BackUp Database Task» из панели инструментов. Параметры задания следующие:
Восстановление БД из РК на определенный момент времени
Подготовка к восстановлению БД
Для восстановления БД на определенный момент времени наше решение предлагает воспользоваться адаптированной версией процедуры sp_DatabaseRestore авторства Brent Ozar. Для того, чтобы это сделать, необходимо в некоторой существующей БД (master или специальная БД, предназначенная для обслуживания SQL сервера), создать следующие объекты:
Таблица [dbo].[CommandLog] для логирования выполняемых команд:
Процедура [dbo].[CommandExecute] для выполнения TSQL команд с логированием;
Процедура [dbo].[DatabaseRestore] для восстановления БД на определенный момент времени.
Скрипт для создания вышеперечисленных объектов доступен по ссылке:
Подготовка к восстановлению БД
Для восстановления БД на определенный момент времени наше решение предлагает воспользоваться адаптированной версией процедуры sp_DatabaseRestore авторства Brent Ozar. Для того, чтобы это сделать, необходимо в некоторой существующей БД (master или специальная БД, предназначенная для обслуживания SQL сервера), создать следующие объекты:
Таблица [dbo].[CommandLog] для логирования выполняемых команд:
Процедура [dbo].[CommandExecute] для выполнения TSQL команд с логированием;
Процедура [dbo].[DatabaseRestore] для восстановления БД на определенный момент времени.
Скрипт для создания вышеперечисленных объектов доступен по ссылке:
Также необходимо обеспечить нахождение всех полных бэкапов и бэкапов транзакционных журналов в папках, доступных для SQL Server. Названия бэкапов должны соответствовать шаблону YourDatabaseName_backup_YYYY_MM_DD_HHMMSS_NNNNNNN, так как в таком виде генерируются названия для РК при использовании планов обслуживания.
Запуск процедуры sp_DatabaseRestore
Пример запуска процедуры [dbo].[DatabaseRestore] для восстановления БД на определенный момент времени (2023-08-13 13:49:00):
Запуск процедуры sp_DatabaseRestore
Пример запуска процедуры [dbo].[DatabaseRestore] для восстановления БД на определенный момент времени (2023-08-13 13:49:00):
exec [dbo].[sp_DatabaseRestore]
@Database = 'YourDatabaseName',
@BackupPathFull = 'C:\Backup\Daily\YourDatabaseName\',
@BackupPathLog = 'C:\Backup\Transaction Logs\YourDatabaseName\',
@RunRecovery = 1,
@StopAt = '20230903134900',
@Execute = 'Y';
Примечание: Для проверки корректности сгенерированных команд по восстановлению БД первый раз рекомендуется запустить процедуру с параметром @Execute = ‘N’ – в этом случае все команды по восстановлению БД будут выведены в консоль, но не будут выполнены.
В примере ниже можно убедиться, что команды, сгенерированные процедурой, корректны, следовательно можно запускать процедуру с параметром @Execute = ‘Y’ для восстановления БД YourDatabaseName на момент времени 03.09.2023 13:49:00.
В примере ниже можно убедиться, что команды, сгенерированные процедурой, корректны, следовательно можно запускать процедуру с параметром @Execute = ‘Y’ для восстановления БД YourDatabaseName на момент времени 03.09.2023 13:49:00.
Описание некоторых параметров процедуры
@Database – название БД-источника;
@RestoreDatabaseName – название для восстановленной БД. По умолчанию NULL. Если параметр равен NULL, то имя восстановленной БД будет совпадать с именем БД-источника;
@BackupPathFull – полный путь к директории, где хранятся полные бэкапы;
@BackupPathLog – полный путь к директории, где храняться бэкапы транзакционного журнала;
@MoveFiles – параметр, указывающий, будет ли использован для хранения файлов восстановленной БД тот же адрес, что и для БД-источника (=0) или нет (=1). По умолчанию 0;
@ContinueLogs – параметр, указывающий, восстанавливаются ли бэкапы только транзакционного журнала в случае, если полный бэкап уже был восстановлен ранее с параметром NORECOVERY (=1) или нет (=0). По умолчанию 0;
@RunRecovery – параметр, указывающий, необходимо ли выполнить восстановление с параметром RECOVERY, чтобы восстановленная БД была готова к использованию (если равен 1, то добавляет в конце скрипта команду RESTORE DATABASE [YourDatabaseName] WITH RECOVERY). По умолчанию 0;
@StopAt – текстовый параметр, содержащий дату и время момента, на который производится восстановление БД. Формат даты и времени – ‘ГГГГММДДЧЧММСС’. По умолчанию NULL, в этом случае БД восстанавливается на самый поздний момент времени из возможных;
@Execute – параметр, указывающий, выполнять ли сгенерированные команды (=’Y’) или нет (=’N’). По умолчанию ‘Y’.
@Database – название БД-источника;
@RestoreDatabaseName – название для восстановленной БД. По умолчанию NULL. Если параметр равен NULL, то имя восстановленной БД будет совпадать с именем БД-источника;
@BackupPathFull – полный путь к директории, где хранятся полные бэкапы;
@BackupPathLog – полный путь к директории, где храняться бэкапы транзакционного журнала;
@MoveFiles – параметр, указывающий, будет ли использован для хранения файлов восстановленной БД тот же адрес, что и для БД-источника (=0) или нет (=1). По умолчанию 0;
@ContinueLogs – параметр, указывающий, восстанавливаются ли бэкапы только транзакционного журнала в случае, если полный бэкап уже был восстановлен ранее с параметром NORECOVERY (=1) или нет (=0). По умолчанию 0;
@RunRecovery – параметр, указывающий, необходимо ли выполнить восстановление с параметром RECOVERY, чтобы восстановленная БД была готова к использованию (если равен 1, то добавляет в конце скрипта команду RESTORE DATABASE [YourDatabaseName] WITH RECOVERY). По умолчанию 0;
@StopAt – текстовый параметр, содержащий дату и время момента, на который производится восстановление БД. Формат даты и времени – ‘ГГГГММДДЧЧММСС’. По умолчанию NULL, в этом случае БД восстанавливается на самый поздний момент времени из возможных;
@Execute – параметр, указывающий, выполнять ли сгенерированные команды (=’Y’) или нет (=’N’). По умолчанию ‘Y’.