В продолжение своей предыдущей статьи по архивации БД 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