Пример реализации автоматизированного процесса резервного копирования и восстановления баз данных встроенными средствами

В Интернете можно найти достаточно много примеров по созданию резервных копий баз данных, а также по их восстановлению. Приведем еще один пример встроенными средствами в MS SQL Server.

В данном примере будут собраны сразу несколько подходов-от проверки целостности базы данных перед созданием резервной копии до восстановления этой базы по уже созданной ранее резервной копии.

Сначала приведем общий алгоритм по созданию резервной копии:

1) Определяем для каких баз данных нужно сделать резервную копию 2) Проверяем каждую выбранную базу данных на целостность 3) Создаем для каждой выбранной базы данных резервную копию (полную или разностную (дифференциальную), или журнала транзакций) 4) Проверяем полученные резервные копии 5) Сжимаем журналы транзакций отработанных баз данных (по необходимости)

Далее приведем пример реализации данного выше алгоритма.

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

В первом столбце указывается идентификатор базы данных, в FullPathBackup содержится полный путь для создания полных резервных копий (например, 'диск:\. \'), а в DiffPathBackup и LogPathBackup полные пути для создания разностных резервных копий и резервных копий журналов транзакций соответственно. Если столбец DiffPathBackup или LogPathBackup будет пустым, то база данных не будет участвовать в создании разностной резервной копии или резервной копии журнала транзакций соответственно.

Также можно создать представление на основе этой таблицы:

Данное представление дает возможность быстро увидеть какие базы данных участвуют в резервном копировании.

Теперь создадим представление, которое выводит информацию по файлам БД из системного представления sys.master_files:

Для создания полных резервных копий реализуем хранимую процедуру:

По коду видно, что данная хранимая процедура сразу решает все оставшиеся пункты алгоритма по созданию полных резервных копий.

Аналогично реализуются хранимые процедуры по созданию разностных резервных копий и резервных копий журналов транзакций:

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

Т к резервная копия журнала транзакций обычно делается достаточно часто, а проверка целостности БД – достаточно ресурсоемкая задача, то обычно не проверяют целостность БД перед созданием резервной копии журнала транзакций.

Также необходимо помнить о том, что периодически нужно делать полные резервные копии БД master, msdb и model.

Для автоматизации процесса по созданию резервных копий, достаточно поместить вызов реализованных выше хранимых процедур в Планировщик заданий Windows, в задачи Агента или в любой другой доступный сервис.

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

Наиболее общий подход:

1) Создание полной резервной копии 1 раз в день 2) Создание разностных резервных копий каждые 2-4 часа 3) Создание резервных копий журнала транзакций каждые 5-60 минут

Также важно помнить о том, что обычно базы данных участвуют в системе отказоустойчивости и быстрой доступности. И если в последнем используются резервные копии журналов транзакций, то важно не мешать этому процессу (т е нельзя допускать создания резервных копий журнала транзакций БД разными процессами, т к тогда будет потеряна последовательность для восстановления из этих резервных копий).

Здесь были приведены примеры последовательной обработки каждой БД. Но в производстве вполне возможно распараллелить обработку, делая несколько резервных копий одновременно. Это можно сделать разными способами. Например, вызовом следующей хранимой процедуры:

Здесь асинхронность достигается путем динамического создания заданий Агента с последующим их выполнением и удалением.

Теперь приведем общий алгоритм по восстановлению базы данных из созданных ранее резервных копий (в другой или тестовой среде):

1) Определяем какие базы данных нужно восстановить, а также место нахождения резервных копий 2) Восстанавливаем базы данных 3) Проверяем целостность восстановленных баз данных

Далее приведем пример реализации алгоритма по восстановлению базы данных из полной резервной копии. Для разностной процедура аналогичная, но сначала восстанавливается полная резервная копия, а затем разностная.

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

Здесь назначения колонок аналогичны назначениям колонок для таблицы [srv].[BackupSettings] с той лишь разницей, что по полному пути будут не создаваться резервные копии, а браться существующие для восстановления.

Данная таблица нужна, чтобы определить полные названия файлов восстанавливаемой БД для последующего переноса (например, [SourcePathRestore]='Логическое имя файла' и [TargetPathRestore]= 'диск:\. \Физическое имя файла', а [Ext]= 'Расширение файла'.

На самом деле здесь можно определить логические имена файлов БД по следующему запросу:

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

Теперь приведем пример реализации хранимой процедуры по восстановлению БД из полной резервной копии с последующей проверкой на целостность данных:

Здесь, чтоб определить, какую полную резервную копию нужно восстанавливать, берётся имя файла, которое формируется следующим образом: <название БД>_Full_backup_<год>_<номер_месяца_в_году>_<номер_дня_в_месяце>.bak

Для автоматизации процесса по восстановлению БД из полных резервных копий, достаточно поместить вызов реализованной выше хранимой процедуры в Планировщик заданий Windows, в задачи Агента или в любой другой доступный сервис.

Просмотреть последние сделанные резервные копии БД можно с помощью следующего представления:

В данной статье был рассмотрен пример реализации автоматизированного процесса резервного копирования на одном сервере с последующим восстановлением на другом сервере (например, тестовом).

Данный метод позволяет автоматизировать процесс создания резервных копий, проверить резервные копии методом восстановления, а также тонко настроить приведенные выше процессы.