Реклама

Важность регулярного резервного копирования журналов транзакций невозможно переоценить. Резервные копии не только защищают от катастрофического сбоя, регулярное создание резервных копий T-Log для баз данных с полным восстановлением или восстановлением с неполным протоколированием поможет сохранить их в «хорошей форме». В большинстве случаев достаточно назначить уведомление, отправляемое при сбое заданий резервного копирования T-Log, чтобы сразу узнать о случившейся ошибке.

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

  • UNC-путь или назначение, на которое указывает предложение MIRROR TO (единственная функция Enterprise Edition, позволяющая сохранять резервные копии в двух местах — локально и в другом месте вне компьютера), замедляет работу настолько, что резервное копирование хотя и не прекращается, но не выполняется по расписанию, и может пройти более часа с момента полноценного резервного копирования баз данных.
  • В некоторых сторонних решениях резервного копирования SQL Server предусмотрена превосходная функция, которая позволяет в случае неудачи повторять резервное копирование N раз с интервалом X секунд между попытками. Это очень полезная функция, если только не возникли неполадки; не приходится слишком долго ждать между попытками и нет многочисленных повторов, направленных ко многим базам данных, в результате чего задание резервного копирования хоть и не завершается отказом, но «застывает» на целые часы. Например, если нужно организовать резервное копирование T-Log для 20 баз данных через каждые 10 минут и из-за неполадок повторные попытки для одной базы данных занимают 20 минут, то задание перейдет к следующей базе данных и т. д. Это будет происходить до тех пор, пока вы не потратите многие часы из-за логики повторов, саботирующей цепочку резервного копирования, даже не получив сообщения об ошибке или предупреждения.

Для подобных случаев и ситуаций, когда важно регулярно выполнять резервное копирование T-Log, я подготовил простой сценарий (см. листинг), который можно использовать как для проверки задания, выполняющего резервное копирование (оно должно быть успешно завершено в течение последних N минут), так и для проверки дат последнего резервного копирования баз данных с полным восстановлением.

Очевидное ограничение, связанное с этим заданием, состоит в том, что оно выполняется через агент SQL Server. Таким образом, в случае сбоя агента SQL Server (обрабатывающего резервное копирование) этот «сторож» тоже не может работать. И все же следует отметить, что за годы работы с SQL Server мне не приходилось сталкиваться с серьезными сбоями или невозможностью запуска агента SQL Server, кроме случаев, когда его отключал предприимчивый пользователь. Поэтому, если такие опасения существуют, можно запустить данный сценарий через какой-нибудь другой механизм планирования, вместо того чтобы просто поместить его в новое задание агента SQL Server и назначить обычное расписание.

В остальном сценарий довольно прост. Достаточно указать желаемый диапазон проверок и имя задания для резервного копирования T-Log. Если заданий много, то это легко учесть в предложении IN (). Затем укажите имя оператора и профиль компонента Database Mail для отправки предупреждений, а все остальное сделает сценарий, после того как будет настроен на регулярное выполнение.

Листинг. Проверки резервного копирования
SET NOCOUNT ON;

-- Переменные:
DECLARE @NumberOfPastMinutesToCheck int = 30;
DECLARE @LogBackupJobName sysname = N'Customer Databases -
  TLOG';

-- Подробности предупреждений:
DECLARE @OperatorName sysname = ‘Alerts’;
DECLARE @DbMailProfileName sysname = 'General';

------------------------------------------------------------------------------
-- Логика/реализация:

DECLARE @JobID uniqueidentifier;
SELECT @JobID = job_id
FROM msdb..sysjobs
WHERE
        name = @LogBackupJobName;


DECLARE @Subject nvarchar(200);
DECLARE @Body nvarchar(200);

-- В начале просмотр задания резервного копирования - проверка,
   что оно
--              выполнено (успешно) в последней 
   @NumberOfPastMinutesToCheck
DECLARE @ago datetime = DATEADD(n, - @NumberOfPastMinutesToCheck, GETDATE());

DECLARE @DateAsInt int;
DECLARE @TimeAsInt int;

SELECT @DateAsInt = CAST(CONVERT(varchar(20), @ago, 112) AS int);
SELECT @TimeAsInt = CAST(REPLACE(CONVERT(varchar(20),
   @ago, 108),':','') AS int);

DECLARE @states TABLE (
        run_date int,
        run_time int,
        run_status int
);

INSERT INTO @states
SELECT
        run_date,
        run_time,
        run_status
FROM
        msdb..sysjobhistory
WHERE
        run_date >= @DateAsInt
        AND run_time >= @TimeAsInt
        AND job_id = @JobId
        AND step_id = 0 -- результат выполнения задания (только)
        AND run_status = 1; -- успешно (только)

DECLARE @successCounts int;
SELECT @successCounts = COUNT(*) FROM @states;

IF @successCounts < 1 BEGIN
        SET @Subject =
                '[SQL Server Backups] Transaction Log Backup Failure'
        SET @Body = 'The Transaction Backup Job (with ID '
                + CAST(@JobId AS varchar(36))
                + ' has not successfully completed in the last '
                + CAST(@NumberOfPastMinutesToCheck AS varchar(20))
                + ' minutes.';

        EXEC msdb..sp_notify_operator
                @profile_name = @DbMailProfileName,
                @name = @OperatorName,
                @subject = @Subject,
                @body = @Body;
END

------------------------------------------------------------------
-- Проверка, есть ли базы данных, для которых не выполнено
--              резервное копирование в последней
   @NumberOfPastMinutesToCheck:

DECLARE @databases TABLE (
        [name] sysname
);

INSERT INTO @databases
SELECT
        name
FROM
        master.sys.databases
WHERE 
        recovery_model_desc = 'FULL'
        AND name NOT IN ('model')
        AND name NOT IN (SELECT
                DISTINCT database_name
                FROM msdb.dbo.backupset
                WHERE [type] = 'L' AND backup_finish_date > @ago);

DECLARE @failedDbs nvarchar(2000) = '';

SELECT @failedDbs = @failedDbs + name + ','
        FROM @databases ORDER BY name;

IF @failedDbs != '' BEGIN
        SET @Subject = '[SQL Server Backups] Transaction
                Log Backup Failure';
        SET @Body = 'The following databases have not been backed
                up within the last '
                + CAST(@NumberOfPastMinutesToCheck AS varchar(20))
                                + ' minutes: '
                + CHAR(13) + CHAR(10) + CHAR(9) + @failedDbs + '.';

        EXEC msdb..sp_notify_operator
                @profile_name = @DbMailProfileName,
                @name = @OperatorName,
                @subject = @Subject,
                @body = @Body;
END
GO
Купить номер с этой статьей в PDF