"Универсальный" скрипт для копирования бэкапов на сетевой диск
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 Гб опасно, причем надо копировать каждый бэкап в отдельный файл, а с помощью создания логического сетевого ресурса этого сделать нельзя. По этому я решил наваять такое решение которое работает больше года, сбоеов пока не было.