Задача — автоматизировать бэкап баз данных с уведомлением о проблемах, при этом очень хотелось бы что бы бэкапы архивировались, а то на них места не напасешься :)

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

Схема предлагается такая — вешаем на Job вызов процедуры, которая будет бэкапить базу, в качестве имени файла будем брать база+дата, потом вызывать архиватор для сжатия бэкапа и удалять исходный файл.

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

CREATE TABLE [BackupSetup](
[DBName] [nvarchar](50) NOT NULL,
[SourcePath] [nvarchar](max) NULL,
[ArchivPath] [nvarchar](max) NULL,
CONSTRAINT [PK_Backup] PRIMARY KEY CLUSTERED
(
[DBName] ASC
)

Теперь по поводу самого бэкапа:


BACKUP DATABASE @DBName TO DISK = @SourceFile -- делает бэкап базы
BACKUP LOG @DBName WITH NO_LOG --  делает бэкап логов
DBCC SHRINKDATABASE (@DBName,20) -- обрезает неиспользуемое место в БД, уменьшая его до 20%

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

Версия для командной строки обзывается — 7z.exe, наш синтаксис —

7z.exe  a «имя исходного файла» «имя архива» -bd -y , где

-bd — не показывать индикатор прогресса, -y — отвечать положительно на все вопросы (иначе в случае ошибки программа будет ждать от нас указаний, а Job будет висеть ждать пока закончится выполнение программы, итог — через несколько суток job отвалится с тайм- аутом :) )

по завершении работы программы архивации анализируем возвращенный ей код —

/*
0 No error
1 Warning (Non fatal error(s)). For example, one or more files were locked by some other application, so they were not compressed.
2 Fatal error
7 Command line error
8 Not enough memory for operation
255 User stopped the process
*/

Если код ошибки отличен от 0, то вызываем RaisError  уровня 19 (job должен запускаться из под юзера с ролью sysadmin), что бы job выдал ошибку. Сообщить иначе job-у что хп выполнилась с ошибкой я не смог. А это нам понадобится, что бы сработало стандартное уведомление ‘Job Failed’.

Ну и если всё прошло удачно, то удаляем исходный файл командой Del /Q , параметр Q отключает запрос на подтверждение удаления.

Так как у нас используется несколько серверов и путь установки программы 7-zip может быть разным, то я вынес путь к файл в настройки, чтоб не городить дополнительную таблицу, я добавил запись в таблицу BackupSetup c именем базы Arhive_Path и полным именем программы в поле SourcePath (типа такого — F:\Program Files\7-Zip\7z.exe).

Собственно сам код процедуры —

CREATE PROCEDURE [dbo].[USR_BackupDB]
-- Add the parameters for the stored procedure here
@DBName nvarchar(50),     -- Имя БД
@ArchiveSourceFile bit,   -- 0: Backup с сжатием, 1: Backup без сжатия
@DelSourceFile bit        -- 1: удалить исходный файл после сжатия, 0: не удалять
AS
BEGIN
DECLARE @SourceFile nvarchar(100),
@BackupFile nvarchar(100),
@RunBackupCommand nvarchar(150),
@BackupPath nvarchar(100),
@SourcePath nvarchar(100),
@RunDeleteCommand nvarchar(150),
@ResultStr varchar(400),
@Patch nvarchar(max);

-- получаем путь куда класть бэкап
 SET @SourcePath = isnull((SELECT SourcePath FROM ServerSetup.dbo.BackupSetup SS WHERE SS.DBName=@DBName),'');

-- путь к exe архиватору, включая имя файла
Set @Patch = isnull((SELECT top 1 SourcePath FROM ServerSetup.dbo.BackupSetup SS WHERE SS.DBName='Arhive_Path'),'');
if @Patch=''
begin
raiserror ('Не задан путь к архиватору, создайте в таблице ServerSetup.dbo.BackupSetup запись с DBName=Arhive_Path и SourcePath=путь',19,0) WITH LOG;
return 1;
end;

-- задаем имя файла  с именем базы + дата
SET @SourceFile = @SourcePath+@DBName+'_'+CONVERT(nvarchar, GETDATE(), 112)+'.bak'
-- бэкап
BACKUP DATABASE @DBName TO DISK = @SourceFile
BACKUP LOG @DBName WITH NO_LOG
DBCC SHRINKDATABASE (@DBName,20)

DECLARE @returnstatus int;
SET @returnstatus = NULL;

-- если надо, то архивируем
IF @ArchiveSourceFile = 1
BEGIN
SET @BackupPath = isnull((SELECT ArchivPath FROM ServerSetup.dbo.BackupSetup SS WHERE SS.DBName=@DBName),'');       
SET @BackupFile = @BackupPath+@DBName++'_'+CONVERT(nvarchar, GETDATE(), 112)
SET @RunBackupCommand = '""'+@Patch+'" '+' a "'+@BackupFile+'" "'+ @SourceFile+'" -bd -y "';
EXEC @returnstatus = master.dbo.xp_cmdshell @RunBackupCommand, no_output;
-- анализируем результат вызова, обратите внимание, если указан не верный путь к z7.exe то результат будет 1 и сообщение будет не верным.
if @returnstatus>0
begin
set @ResultStr=(case @returnstatus when 1 then 'Warning (Non fatal error(s)). For example, one or more files were locked by some other application, so they were not compressed.'
when 2 then 'Fatal error' when 7 then 'Command line error' when 8 then 'Not enough memory for operation'
when 255 then 'User stopped the process' else 'а хрен его знает!' end);
raiserror (@ResultStr,19,0) with log;
return 1;
end;

-- в случае неудачи архивирования файл бэкапа сохранится, т.к из-за raiserror эта ветка не выполнится
-- если надо, то удаляем исходный файл
IF @DelSourceFile = 1
BEGIN
SET @RunDeleteCommand = 'del /Q '+@SourceFile
EXEC master.dbo.xp_cmdshell @RunDeleteCommand
END

END
END

Уведомление об ошибке решим стандартным способом, повесим уведомление на св-ва Job-а , на закладке Notificaton поставим галочку Email и укажем оператора, которого надо известить. Как настроить SQL Server, чтоб отправлялись уведомления, я описывал раньше.

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

А как у Вас организованы бэкапы БД ?

зы: в опции архиватора можно добавить параметры:
1) -slp — разрешает использовать большие страницы памяти, ускоряет архивацию для больших данных
2) a mt:Q — где Q — количество потоков, позволяет ускорить архивацию в мультипроцессорных системах за счет распараллеливания
Как влияет на архивацию по факту пока не знаю, буду экспериментировать, сейчас время выполнения бэкапа — почти 9 часов ! Посмотрим, что изменится после этих параметров.