Восстановление БД в MS SQL Server

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

Итак, рассмотрим пошаговую процедуру восстановления БД на следующем примере:

• Необходимо восстановить 3 БД, условно назовем их: data_base_1, data_base_2 и data_base_3;
• Восстановить необходимо самый последний архив (для восстановления более позднего архива, нужно изменить имя содержащей его папки в формате даты на самую раннюю);
• Процесс восстановления должен запускаться вручную;
• Архивы для восстановления должны находиться на сетевом диске в папке с именем в формате даты.

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

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

• Просмотр каталога, где хранятся архивы;
• Поиск самого молодого архива;
• Восстановление БД.

Для осуществления автоматизации восстановления в MS SQL Server следует использовать SQL Server Agent в  Management Studio, позволяющий пошагово организовать данный процесс. Для наглядности и лучшего понимания процедуры воспользуемся графическим интерфейсом:

1. Jobs (Задания) → правая кнопка мыши → New Job (Новое задание)

2. На вкладке General (Общее) необходимо ввести имя задания в поле Name (Имя)

IMAGE

3. Процесс восстановления будет состоять всего из 3-х шагов, по шагу для каждой БД, это следует делать для диверсификации процесса восстановления (неудача восстановления одной БД никак не затронет процесс восстановления другой).

Steps (Шаги) → New (Новый) → в поле Step name указать имя шага → в поле Command (Команда) указывается текст SQL скрипта:

declare @db_name as varchar(100) = 'data_base_1';--имя базы данных
declare @table as table(SUBDIR varchar(100));          --таблица для хранения всех имен подкаталогов
declare @date as varchar(200);                                   --дата создания архива
declare @path varchar(200)                                        --путь к архиву

insert @table exec master.dbo.xp_subdirs N'\\192.168.1.50\sql_server_backups\';    --запись всех подкаталогов в таблицу
 select @date = MAX(subdir) from @table                    --поиск самого молодого архива (самой большой даты)

set @path = N'\\192.168.1.50\sql_server_backups\' + @date + '\' + @db_name                                                                   --задание пути для восстановления

RESTORE DATABASE @db_name FROM  DISK = @path WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

GO--восстановление БД с помощью команды RESTORE DATABASE, где @db_name – имя восстанавливаемой БД,

--@path – путь к архиву, FILE=1 – 1-й резервный набор данных, replace – замена БД на новую.

4. На вкладке Advanced (Подробнее) → в ниспадающем списке On failure action (В случае неудачи выполнения) выбрать действие для неудачного завершения данного шага, например, Go to the next step (Переход к следующему шагу) (чтобы неудача выполнения данного шага не стала причиной выхода из задания, а продолжилось восстановление остальных баз)→ в ниспадающем списке On success action (В случае удачного выполнения), также выбрать необходимое действие при успешном выполнении шага, например, Go to the next step (Переход к следующему шагу).

5. Создаем остальные 2 шага, как указано в пункте 3 для баз data_base_2 и data_base_3.

6. Так как восстановление может понадобиться в редких случаях, то нет необходимости создавать расписание для этого задания.

7. Jobs (Шаги) → Refresh (Обновить) → в списке заданий должно появиться наше созданное задание "data_base_restore".

8. Для проверки можно запустить задание принудительно: data_base_restore → Start Job at Step (Запустить задание с шага) → в появившемся диалоговом окне выбрать первый шаг и нажать Start (Запуск).

9. В случае если восстановление прошло успешно, должно появиться следующее окно:

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