В продолжение своей предыдущей статьи по архивации БД MSSQL и негативному отклику в связи с отсутствием возможности архивации логов транзакций, работаю я теперь в компании, где понадобилось автоматизировать этот момент для баз в том числе с полной моделью восстановления.
Скрип работает универсально для баз с различной моделью восстановления, в начале скрипта добавлены настройки для относительно гибкого формирования расписания. Скрипт можно поставить с SQL Agent и удобным интервалом (у меня, например, 1 раз час), первый запуск в сутках будет проверять, надо создавать или нет полную или разностную копию сегодня и далее в течении дня для БД с полной моделью восстановления будут создаваться бэкапы лога журнала транзакций.
-- пути до бэкапов
declare @FullPath varchar(500) = 'D:\SQLBackup'
declare @DiffPath varchar(500) = 'D:\SQLBackup'
declare @LogPath varchar(500) = 'D:\SQLBackup'
-- архивируемые и исключенные из архивации БД
declare @IncludeBase varchar(500) = 'master, msdb' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные
declare @ExcludeBase varchar(500) = 'tempdb'
-- настроки для полного бэкапа
-- минимум хранить дней
declare @MinFullDay int = 181
-- выполнять каждые дней
declare @MinFullExecDay int = 7
-- настроки для разностного бэкапа
-- минимум хранить дней
declare @MinDiffDay int = 181
-- выполнять каждые дней
declare @MinDiffExecDay int = 1
-- настроки для бэкапа журнала
-- минимум хранить дней
declare @MinLogDay int = 35
-- как часто создавать master (дни)
declare @MinDayMaster int = 1
-- сколько хранить информацию об архивах в msdb (дни)
--declare @DayHistory int = @MinFullDay + 30
-- включим сжатие
EXEC sp_configure 'show advanced options', 1; EXEC sp_configure 'backup compression default', 1; RECONFIGURE WITH OVERRIDE;
-- включим xp_cmdshell (нужно для удаления старых бэкапов)
EXEC sp_configure 'show advanced options', 1; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE;
declare @tempcmd varchar(500) =''
declare @tempname varchar(500) =''
-- создание путей
set @tempcmd= 'md '+@FullPath
exec xp_cmdshell @tempcmd, no_output
set @tempcmd= 'md '+@DiffPath
exec xp_cmdshell @tempcmd, no_output
set @tempcmd= 'md '+@LogPath
exec xp_cmdshell @tempcmd, no_output
-- определяем список БД для архивации
declare @BaseListIncl table (name varchar(200))
declare @BaseListExcl table (name varchar(200))
if @IncludeBase=''
insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE'
else
while len(@IncludeBase)>0
begin
if CHARINDEX (',',@IncludeBase)>0
begin
insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@IncludeBase,1, CHARINDEX (',',@IncludeBase)-1)
set @IncludeBase=LTRIM(RTRIM(SUBSTRING(@IncludeBase,CHARINDEX (',',@IncludeBase)+1, LEN(@IncludeBase))))
end
else
begin
insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = @IncludeBase
set @IncludeBase=''
end
end
if @ExcludeBase<>''
while len(@ExcludeBase)>0
begin
if CHARINDEX (',',@ExcludeBase)>0
begin
insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@ExcludeBase,1, CHARINDEX (',',@ExcludeBase)-1)
set @ExcludeBase=LTRIM(RTRIM(SUBSTRING(@ExcludeBase,CHARINDEX (',',@ExcludeBase)+1, LEN(@ExcludeBase))))
end
else
begin
insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = @ExcludeBase
set @ExcludeBase=''
end
end
-- итоговый список БД для бэкапа
delete from @BaseListIncl
where name in (select name from @BaseListExcl)
declare BaseList cursor for
select name from @BaseListIncl
declare @BaseName varchar(500) =''
declare @BackupType varchar(10) = ''
open BaseList
fetch next from BaseList into @BaseName
while @@FETCH_STATUS = 0
begin
-- получаем тип бэкапа, который надо сделать (для мастера специальные условия, он не может ни каких кроме FULL)
SET @BackupType =
( CASE
WHEN @BaseName = 'master' AND
(EXISTS (SELECT * FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE s.database_name=@BaseName and s.TYPE='D' and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinDayMaster))
THEN ''
WHEN @BaseName = 'master' AND
(NOT EXISTS (SELECT * FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE s.database_name=@BaseName and s.TYPE='D' and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinDayMaster))
THEN 'FULL'
WHEN (NOT EXISTS (SELECT * FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE s.database_name=@BaseName and s.TYPE='D' and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinFullExecDay))
THEN 'FULL'
WHEN (NOT EXISTS (SELECT * FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE s.database_name=@BaseName and s.TYPE IN ('I','D') and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinDiffExecDay))
THEN 'DIFF'
WHEN (EXISTS (select * from sys.databases as a where a.recovery_model_desc = 'FULL' and a.name = @BaseName))
THEN 'LOG'
ELSE ''
END
)
IF @BackupType <> ''
begin
--создаем папку для БД
SET @tempcmd =
(CASE
WHEN @BackupType='FULL' OR @BaseName='master' THEN 'md '+@FullPath+'\'+@BaseName
WHEN @BackupType='DIFF' THEN 'md '+@DiffPath+'\'+@BaseName
ELSE 'md '+@LogPath+'\'+@BaseName
END)
exec xp_cmdshell @tempcmd, no_output
if @BackupType='FULL'
begin
-- full backup
set @tempname = @FullPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_FULL.BAK'
backup database @BaseName to disk = @tempname
end
else if @BackupType='DIFF'
begin
-- diff backup
set @tempname = @DiffPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_DIFF.BAK'
backup database @BaseName to disk = @tempname with differential
end
else
begin
-- log backup
set @tempname = @LogPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_LOG.TRN'
BACKUP LOG @BaseName to disk = @tempname
end
-- удаляем истекшие бэкапы
declare @delpath varchar(500)=''
declare delbackup cursor for
SELECT mf.physical_device_name
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE s.database_name=@BaseName and s.TYPE='D'
and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) > @MinFullDay
and is_copy_only = 0
union all
SELECT mf.physical_device_name
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE s.database_name=@BaseName and s.TYPE='I'
and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) > @MinDiffDay
and is_copy_only = 0
union all
SELECT mf.physical_device_name
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE s.database_name=@BaseName and s.TYPE='L'
and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) > @MinLogDay
and is_copy_only = 0
open delbackup
fetch next from delbackup into @delpath
while @@FETCH_STATUS = 0
begin
set @tempcmd= 'del /f /q '+QUOTENAME(@delpath,'"')
exec xp_cmdshell @tempcmd, no_output
fetch next from delbackup into @delpath
end
close delbackup
deallocate delbackup
end
fetch next from BaseList into @BaseName
end
close BaseList
deallocate BaseList
-- чистим в MSDB информацию о старых архивах
--declare @oldest DATETIME
--SET @oldest = DATEADD(DAY, -@DayHistory, GETDATE())
--EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest
Так же добавлю скрипт для создания бэкапов "длительного хранения". Создаются с опцией ONLY_COPY и не участвуют в общей цепочке восстановления, я обычно использую раз - два в месяц для снятия полной копии или по необходимости
-- ========== СОЗДАЕМ ТОЛЬКО ПОЛНЫЕ БЭКАПЫ С ОПЦИЕЙ ONLY_COPY (не учавствуют в общей цепочке)
-- пути до бэкапов (для типовых)
declare @Path varchar(500) = 'I:\SQLBackupOnlyCopy'
-- архивируемые и исключенные из архивации БД
declare @IncludeBase varchar(500) = 'buh, zup' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные
declare @ExcludeBase varchar(500) = 'tempdb'
-- включим сжатие
EXEC sp_configure 'show advanced options', 1; EXEC sp_configure 'backup compression default', 1; RECONFIGURE WITH OVERRIDE;
-- включим xp_cmdshell (нужно для удаления старых бэкапов)
EXEC sp_configure 'show advanced options', 1; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE;
declare @tempcmd varchar(500) =''
declare @tempname varchar(500) =''
-- создание путей
set @tempcmd= 'md '+@Path
exec xp_cmdshell @tempcmd, no_output
-- определяем список БД для архивации
declare @BaseListIncl table (name varchar(200))
declare @BaseListExcl table (name varchar(200))
if @IncludeBase=''
insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE'
else
while len(@IncludeBase)>0
begin
if CHARINDEX (',',@IncludeBase)>0
begin
insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@IncludeBase,1, CHARINDEX (',',@IncludeBase)-1)
set @IncludeBase=LTRIM(RTRIM(SUBSTRING(@IncludeBase,CHARINDEX (',',@IncludeBase)+1, LEN(@IncludeBase))))
end
else
begin
insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' and name = @IncludeBase
set @IncludeBase=''
end
end
if @ExcludeBase<>''
while len(@ExcludeBase)>0
begin
if CHARINDEX (',',@ExcludeBase)>0
begin
insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = SUBSTRING(@ExcludeBase,1, CHARINDEX (',',@ExcludeBase)-1)
set @ExcludeBase=LTRIM(RTRIM(SUBSTRING(@ExcludeBase,CHARINDEX (',',@ExcludeBase)+1, LEN(@ExcludeBase))))
end
else
begin
insert into @BaseListExcl select name from sys.databases where state_desc='ONLINE' and name = @ExcludeBase
set @ExcludeBase=''
end
end
-- итоговый список БД для бэкапа
delete from @BaseListIncl
where name in (select name from @BaseListExcl)
declare BaseList cursor for
select name from @BaseListIncl
declare @BaseName varchar(500) =''
declare @BackupType varchar(10) = ''
open BaseList
fetch next from BaseList into @BaseName
while @@FETCH_STATUS = 0
begin
--создаем папку для БД
SET @tempcmd = 'md '+@Path+'\'+@BaseName
exec xp_cmdshell @tempcmd, no_output
--создаем архив
set @tempname = @Path+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_FULL_CopyOnly.BAK'
backup database @BaseName to disk = @tempname WITH COPY_ONLY
fetch next from BaseList into @BaseName
end
close BaseList
deallocate BaseList