Архивация БД в MS SQL Server с заданным временем жизни: Шаги 1 - 3

Архивация БД – один из способов сохранения данных в случае возникновения сбоя в работе базы. Настройка архивации в MS SQL Server довольно проста и удобна, однако необходимо учитывать, что она не обладает достаточным набором возможностей, как, например, удаление старых архивов из хранилища. Здесь Вы узнаете как это сделать.

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

Пример

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

1. Архивировать необходимо 3 БД, условно назовем их: data_base_1, data_base_2, data_base_3;
2. Архивация БД должна происходить ежесуточно в 2 часа ночи;
3. Архивы должны храниться в течение 7 дней со дня создания и за каждый последний день месяца;
4. Архивы должны находиться на сетевом диске в папке с именем в формате даты.

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

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

1. Просмотр содержимого папки, где хранятся архивы, анализ даты одного из архивов;
2. Сравнение дата выбранного архива с необходимыми временными условиями, как представлено на изображение ниже;
3. В случае если дата выбранного архива старше времени жизни (по условиям примера – 7 дней с момента создания) или соответствует текущей дате, а также не является последним днем месяца, то такой архив удаляется;
4. Повтор действий в соответствии с пп. 2 и 3 с остальными  архивами в папке;
5. Создание архива за текущую дату.

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

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

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

   

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

   

3. На 1-м шаге будет происходить удаление архивов, не удовлетворяющих заданным временным условиям;

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

declare @dir_name as date; --имя папки хранения архивов в формате
                                              --даты
declare @current_date as date = GETDATE(); --текущая дата
declare @temp_dir_name as varchar(200); --имя следующей папки архива
declare @sql as varchar(200); --sql-команда
declare @expire_date as date = GETDATE() - 7; --время жизни архива
declare @table as table(SUBDIR varchar(100)); --таблица для хранения имен архивов
declare @last_day as int; --последний день месяца

--запись в @table всех имен папок, содержащихся на \\192.168.1.50\sql_server_backups\ при
--помощи хранимой процедуры xp_subdirs
insert @table exec master.dbo.xp_subdirs N'\\192.168.1.50\sql_server_backups\';

--цикл, в котором происходит считывание имени папки с архивами, при помощи курсора
DECLARE next_item CURSOR FOR select * from @table
open next_item;
WHILE (@@FETCH_STATUS = 0)
                begin

FETCH NEXT FROM next_item into @temp_dir_name; --считывание следующего каталога
set @dir_name = CONVERT(varchar, @temp_dir_name, 120); --конвертация имени каталога в формат даты, понятный серверу (*на каждом сервере свое отображение даты)
set @last_day = day(DATEADD(day,1,@dir_name)); --прибавление 1-го дня к дате архива и, если очередной день месяца равен 1 – значит архив был создан в последний день предыдущего месяца

IF ((@dir_name 1)
               begin

              --удаление папки с архивами при помощи команды командной строкиRD

                 set @sql = N'rd /s /q \\192.168.1.50\sql_server_backups\ ' + CONVERT(varchar,
                 @dir_name, 104);

              -- xp_cmdshell – расширенная хранимая процедура, передающая команды
                  операционной системе
                                             exec master.dbo.xp_cmdshell @sql;
                             end
end
CLOSE next_item;
DEALLOCATE next_item;