Важность регулярного резервного копирования журналов транзакций невозможно переоценить. Резервные копии не только защищают от катастрофического сбоя, регулярное создание резервных копий 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