MS SQL: Автоматизируем бэкап БД

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

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

Схема предлагается такая — вешаем на 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 часов ! Посмотрим, что изменится после этих параметров.

28.04.2010 · 13-ый · 8 комментариев
Метки: , , , ,  · Рубрики: 13-ый, MS SQL

8 комментариев

  1. Ktf - 28.04.2010

    В моем варианте нет настроечных таблиц, т.к. все бэкапы кладутся в одну папку, и жму я все базы RAR-ом.

    код см. ниже…

    CREATE procedure dbo.local_backup_all
    as
    
    set nocount on
    
    DECLARE @db_name sysname
    
    DECLARE DBLIST CURSOR FOR 
    SELECT name FROM 
      master.dbo.sysdatabases 
    WHERE 
      (name<>'tempdb') and (name<>'msdb') and (name<>'model') and (not name like '%TEST%')
    ORDER BY dbid
    
    OPEN DBLIST
    FETCH NEXT FROM DBLIST INTO @db_name
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
       exec local_backup_db @db_name, 1
       FETCH NEXT FROM DBLIST INTO @db_name
    END
    
    CLOSE DBLIST
    DEALLOCATE DBLIST
    GO
    
    CREATE procedure dbo.local_backup_db
    	@DB_NAME varchar(25),
    	@DO_TRUNCATE int
    as
    
    set nocount on
    
    DECLARE @BACKUP_DATE varchar(20)
    DECLARE @DB_DEVICE varchar(50)
    DECLARE @BACKUP_DIR varchar(260)
    DECLARE @RAR_EXE varchar(260)
    DECLARE @DB_FILE varchar(260)
    
    DECLARE @hr varchar(2)
    DECLARE @mn varchar(2)
    
    set @hr = DATEPART(hour, GETDATE())
    set @hr = REPLICATE('0',2-DATALENGTH(@hr))+@hr
    set @mn = DATEPART(minute, GETDATE())
    set @mn = REPLICATE('0',2-DATALENGTH(@mn))+@mn
    set @BACKUP_DATE = CONVERT(varchar(20), GETDATE(), 102) + '_' + @hr + '-' + @mn
    
    set @DB_DEVICE = @BACKUP_DATE + '_' + replace(@@SERVERNAME, '\', '_') + '_' + @DB_NAME
    set @RAR_EXE = 'C:\Windows\System32\Rar.exe'
    set @BACKUP_DIR = 'E:\BACKUP\'
    set @DB_FILE = @BACKUP_DIR + @DB_DEVICE + '.BAK'
    
    EXEC sp_addumpdevice 'disk', @DB_DEVICE, @DB_FILE
    
    BACKUP DATABASE @DB_NAME TO @DB_DEVICE WITH NOINIT, NAME = @DB_DEVICE, DESCRIPTION = @DB_DEVICE
    
    EXEC sp_dropdevice @DB_DEVICE
    
    DECLARE @CMD_TEXT varchar(260)
    DECLARE @result int
    DECLARE @ERR_TEXT varchar(260)
    
    SET @CMD_TEXT = @RAR_EXE + ' a -y -cfg- -df -inul -mt2 -s -ep -o+ -t '+
      @BACKUP_DIR+@DB_DEVICE+'.rar '+@DB_FILE
    
    EXEC @result = xp_cmdshell @CMD_TEXT, NO_OUTPUT
    IF NOT (@result = 0) BEGIN
    	SET @ERR_TEXT = 'Ошибка при архивировании бэкапа базы данных '+@DB_DEVICE
    	RAISERROR(@ERR_TEXT, 16, -1) WITH LOG
    END
    ELSE
    BEGIN
    	if @DO_TRUNCATE = 1 BEGIN
    		BACKUP LOG @DB_NAME WITH TRUNCATE_ONLY
    		DBCC shrinkdatabase(@DB_NAME,  TRUNCATEONLY )
    	END
    END
    GO
    
  2. 13-ый - 28.04.2010

    ну у тебя они мелкие и кучей сразу бэкапятся, а у нас большие (от 15 до 110гб) и поэтому важно ужать их поболее (7z) и разнести по времени, чтоб и сервак не лёг и другие задачи ночью выполнялись.

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

  3. Хантер - 29.04.2010

    У меня зоопарк разных баз по филиалам и периодам. Базы за старые периоды (старше 2-х лет) не особо нужны, они архивируются и хранятся на отдельных дисках в сейфе. Кроме того, обмен данными с филиалами ведется на флешках в одностороннем порядке, то есть филиалы каждую неделю архивируют всю свою базу на флешку и привозят в центр.

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

  4. Gelios - 06.05.2010

    блин, как все сложно и запутанно :)
    то ли дело в оракле —
    1. задал политику хранения бэкапов (кол-во, дни, все)
    2. задал уровень параллелизма и тип бэкапа
    3. создал ряд джобов (в зависимости от выбранной политики бэкапирования — full/incremental/archivelog/all) с указанием времени запуска и периодичности.
    4. можно идти пить пиво :)

    если база растет быстро, то остается периодически следить за местом…

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

  5. 13-ый - 06.05.2010

    а что, оракл сам архивирует свои бэкапы?

    всё остальное и так на ура делается и в мс скл

  6. Gelios - 07.05.2010

    да, архивирует. какие в этом проблемы? :)
    а попутно еще на «физические» коррапт-блоки проверяет

  7. andrey - 01.12.2011

    уууууууу

  8. Лаптев Станислав - 30.05.2013

    Отличный пост, спасибо!

    Как вариант — найти путь к 7-зип в реесте

    DECLARE
    @PathTo7zip varchar(1000) = ‘not found’

    — Ищем в реестре путь к 7z.exe
    EXEC xp_regread ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\7-Zip’,’Path’, @value=@PathTo7zip OUTPUT

    IF @PathTo7zip = ‘not found’
    BEGIN
    — Только sysadmin или пользователи с разрешениями ALTER TRACE могут указывать ключевое слово WITH LOG
    RAISERROR (‘Registry key «Path» in HKLM\SOFTWARE\7-Zip not found’,20,1) WITH LOG
    END
    ELSE
    BEGIN
    SET @PathTo7zip = @PathTo7zip + ‘7z.exe’
    END

Написать комментарий