Sergu прислал нам статью, которую я у с удовольствием публикую на нашем сайте.

Мне очень нравиться фраза: "Кто не проводит резервное копирование, тот искушает судьбу". И как с этим не согласится!? Никакие RAID-массивы и кластеры не уберегут вас, например, от ошибки пользователя, который удалил важные данные, или от присланного разработчиками обновления, которое может привести базу данных в неработоспособное состояние. Поэтому, копии баз необходимо делать с последующим их хранением как минимум на двух физических накопителях.

Описание каждого сделанного бэкапа на сервере храниться в системной базе msdb в таблицах:

SELECT * FROM [msdb].[dbo].[backupset]
SELECT * FROM [msdb].[dbo].[backupmediafamily]
SELECT * FROM [msdb].[dbo].[backupfile]
SELECT * FROM [msdb].[dbo].[backupfilegroup]
SELECT * FROM [msdb].[dbo].[backupmediaset]

Для автоматизации системы резервного копирования используют Maintenance Plan (SQL Serevr Agent), в котором указывается какие базы бэкапятся. Мое решение реализовано на языке t-sql для копирования бэкапов на сетевой диск, оно основано на сравнении системных таблиц (в которые пишутся данные о каждом выполненном бэкапе) до и после выполнения Maintenance Plan.

Первым блоком Maintenance Plan Create table TempLastBackUp будет создание временной таблицы в которую запишутся поля [media_set_id], [physical_device_name].

--Проверяем на наличие в базе tempdb таблицы TempLastBackUp

USE tempdb
IF OBJECT_ID('TempLastBackUp') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('TempLastBackUp'), 'IsTable') = 1
--Если таблица есть то удаляем ее и снова создаем
  BEGIN
    DROP TABLE [tempdb].[dbo].[TempLastBackUp]
    CREATE TABLE [tempdb].[dbo].[TempLastBackUp]
           ([media_set_id] [int] NOT NULL,
           [physical_device_name] [nvarchar](260) NULL)
  END
ELSE
--Если ее нет создаем ее
   BEGIN
     CREATE TABLE [tempdb].[dbo].[TempLastBackUp]
            ([media_set_id] [int] NOT NULL,
            [physical_device_name] [nvarchar](260) NULL)
   END
--Вставляем данные о экапах до выполнения план
INSERT INTO [tempdb].[dbo].[TempLastBackUp]
      ([media_set_id], [physical_device_name])
SELECT mf.[media_set_id], mf.[physical_device_name] FROM [msdb].[dbo].[backupmediafamily] mf
INNER JOIN [msdb].[dbo].[backupset] bs ON mf.media_set_id=bs.media_set_id
LEFT JOIN [msdb].[dbo].[restorehistory] rh ON bs.backup_set_id = rh.backup_set_id
where rh.backup_set_id IS NULL

Вторым блоком будет выполнение бэкапирования нужных баз (к примеру возьмем все системные базы сервера):



Перед выполнением третьего блока нужно прогнать скрипт:

--Позволяет увидеть все настройки сервере
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure;

--Включает компонент для работы с файлами
USE master;
GO
EXEC sp_configure 'Ole Automation Procedures', '1';
RECONFIGURE;
EXEC sp_configure;

Третий блок – запуск задания который состоит из двух шагов, первый шаг step1_Create_bat_file_for_copy_backup сравнивает таблицу [TempLastBackUp] после выполнения резервирования базы и результат расхождений до и после прогоняя через курсор пишет в файл CopyLastBackUp.bat:



DECLARE @Date DATETIME
DECLARE @FileOut VARCHAR(255)
DECLARE @TextOut VARCHAR(255)
DECLARE @PathOut VARCHAR(255)
DECLARE @Path VARCHAR(255)
DECLARE @PathLog VARCHAR(100)

SET @PathOut='\\XXX.XXX.XXX.XXX\BackUp' --сетевая шара куда копируються бэкапы
SET @FileOut='D:\CopyLastBackUp.bat' -- собственнои сам bat файл
SET @PathLog=' >>D:\LogCopy.txt' --лог копирования файлов на сетевой диск

DECLARE @FSO int
DECLARE @hr int
DECLARE @src varchar(255)
DECLARE @desc varchar(255)
DECLARE @oFile int

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT

IF @hr <> 0
BEGIN
  EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT
  SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
  RETURN
END

EXEC @hr = sp_OAMethod @FSO, 'DeleteFile', NULL, @FileOut, 1

EXEC @hr = sp_OAMethod @FSO, 'OpenTextFile', @oFile OUT, @FileOut, 8, True
IF @hr <> 0
BEGIN
  EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT
  SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
  RETURN
END

SET @TextOut='@echo off'+ char(10)
EXEC @hr = sp_OAMethod @oFile, 'Write', NULL, @TextOut

SET @TextOut='echo ******************************' + @PathLog + char(10)
EXEC @hr = sp_OAMethod @oFile, 'Write', NULL, @TextOut

SET @TextOut='echo Start copy %date% %time%' + @PathLog + char(10)
EXEC @hr = sp_OAMethod @oFile, 'Write', NULL, @TextOut

DECLARE @PhysDevName varchar(500)
DECLARE @PhysDevName1 varchar(500)

DECLARE LastBackUpList CURSOR FOR

SELECT mf.[physical_device_name] FROM [msdb].[dbo].[backupmediafamily] mf
INNER JOIN [msdb].[dbo].[backupset] bs ON mf.media_set_id=bs.media_set_id
LEFT JOIN [msdb].[dbo].[restorehistory] rh ON bs.backup_set_id = rh.backup_set_id
where rh.backup_set_id IS NULL
and mf.[media_set_id] not in (select media_set_id from [tempdb].[dbo].[TempLastBackUp])

OPEN LastBackUpList;

FETCH NEXT FROM LastBackUpList
INTO @PhysDevName;
WHILE @@FETCH_STATUS = 0
  BEGIN
  set @PhysDevName='copy /Y ' + '"'+@PhysDevName+'"' + ' ' +'"'+ @PathOut+rtrim(SUBSTRING(@PhysDevName,10,len(@PhysDevName)))+'"' + @PathLog + char(10)
  set @PhysDevName1='echo '+ @PhysDevName
  EXEC @hr = sp_OAMethod @oFile, 'Write', NULL, @PhysDevName1 
  EXEC @hr = sp_OAMethod @oFile, 'Write', NULL, @PhysDevName
  FETCH NEXT FROM LastBackUpList
  INTO @PhysDevName;
  END;

CLOSE LastBackUpList;
DEALLOCATE LastBackUpList;

SET @TextOut='echo Stop copy %date% %time%' + @PathLog + char(10)
EXEC @hr = sp_OAMethod @oFile, 'Write', NULL, @TextOut

SET @TextOut='echo ******************************' + @PathLog+ char(10)
EXEC @hr = sp_OAMethod @oFile, 'Write', NULL, @TextOut

IF @hr <> 0
BEGIN
  EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT
  SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
  RETURN
END

EXEC @hr = sp_OADestroy @FSO
   IF @hr <> 0
   BEGIN
   EXEC sp_OAGetErrorInfo @FSO, @src OUT, @desc OUT
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
   END
EXEC @hr = sp_OADestroy @oFile
   IF @hr <> 0
   BEGIN
    EXEC sp_OAGetErrorInfo @oFile, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
   END

И соответственно второй степ задания step2_Run_bat_file запускает файл CopyLastBackUp.bat

"D:\CopyLastBackUp.bat".

Можно было сделать проще, создать сетевой ресурс и на него бэкапить:

-- Добавляем сетевой ресурс на который будет скидываться бэкап
EXEC sp_addumpdevice 'disk', 'имя шары', '\\XXX.XXX.XXX.XXX\test.bak'

-- Бэкап на сетевой ресурс
BACKUP DATABASE [TEST] TO [имя шары] WITH NOFORMAT, NOINIT, NAME = N'TESTBACKUP', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Но мне этот вариант не нравится, поскольку он подходит для маленьких баз, одновременно бэкапить и копировать базу весом больше 20 Гб опасно, причем надо копировать каждый бэкап в отдельный файл, а с помощью создания логического сетевого ресурса этого сделать нельзя. По этому я решил наваять такое решение которое работает больше года, сбоеов пока не было.