Архивация баз данных Microsoft SQL Server

По долгу службы сталкиваюсь с серверами баз данных MSSQL. Часто необходимо быстро настроить архивацию БД, на тестовых серверах, да и в продакшене. При этом в сети можно найти много разрозненных односложных источников, как надо или не надо архивировать, но нигде нет каких то более или менее универсальных готовых решений. На новом месте работы опять столкнулся с данной проблемой. В силу определенных причин все БД в компании (пока) находятся в режиме простой модели восстановления, потому решение, приведенное в тексте является не полным, но судя по вопросам на форумах, начинающим и просто разработчикам и администраторам, далеким от данных задач, вполне подойдет как решение, ну а в процессе каждый может дополнить его сам.

Скрипт выполняет архивацию указанных БД учитывая, по каким дням недели делать полные копии, сколько хранить последних полных и разностных архивов. Предназначен для архивации баз данных с простой моделью восстановления, если вам необходимо архивировать ещё и логи, то по аналогии вполне можете дополнить скрипт сами. Возможно, когда мне понадобится, я допишу данный скрипт. По окончанию архивации устаревшие архивы БД удаляются с диска.

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

Указываем пути, где будем хранить наши архивы, в момент запуска архивации в данных папках создадутся подпапки по каждой архивируемой БД:

-- пути до бэкапов
declare @FullPath varchar(500) = 'D:WorkFull'
declare @DiffPath varchar(500) = 'D:WorkDiff'

Если не указываем список конкретных БД в @IncludeBase для архивации, то берутся все БД и из них исключаются базы указанные в @ExcludeBase:

-- архивируемые и исключенные из архивации БД 
declare @IncludeBase varchar(500) = '' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные
declare @ExcludeBase varchar(500) = 'master, model, tempdb'

Тут укажем через запятую дни недели для полных архивов:

-- дни недели для полных бэкапов
declare @FullDay varchar(13) = '7'

Сколько последних копий для каждого типа архивов оставлять на диске:

-- сколько минимум хранить архивов
declare @MinFull int = 3
declare @MinDiff int = 3

При работе скрипта на сервере включается сжатие архивов и возможность запуска процедуры xp_cmdshell. У службы должны быть права на чтение/запись/удаление в каталоги с архивами.

Текст скрипта:

-- пути до бэкапов
declare @FullPath varchar(500) = 'D:WorkFull'
declare @DiffPath varchar(500) = 'D:WorkDiff'

-- архивируемые и исключенные из архивации БД 
declare @IncludeBase varchar(500) = '' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные
declare @ExcludeBase varchar(500) = 'master, model, tempdb, msdb'

-- дни недели для полных бэкапов
declare @FullDay varchar(13) = '7'

-- сколько минимум хранить архивов
declare @MinFull int = 3
declare @MinDiff int = 3

-- включим сжатие 
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

set datefirst 1
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

-- определяем список БД для архивации
declare @BaseListIncl table (name varchar(200))
declare @BaseListExcl table (name varchar(200))

if @IncludeBase='' 
		insert into @BaseListIncl select name from sys.databases
	else
		while len(@IncludeBase)>0
		begin
			if CHARINDEX (',',@IncludeBase)>0
				begin
					insert into @BaseListIncl select name from sys.databases where 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 name = @IncludeBase
					set @IncludeBase=''
				end
		 end

if @ExcludeBase='' 
		insert into @BaseListIncl select name from sys.databases
	else
		while len(@ExcludeBase)>0
		begin
			if CHARINDEX (',',@ExcludeBase)>0
				begin
					insert into @BaseListExcl select name from sys.databases where 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 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 @type bit = 0
if CHARINDEX(CAST(DATEPART(weekday,getdate()) as varchar(1)),@FullDay)>0
	set @type=1


open BaseList
fetch next from BaseList into @BaseName  

while @@FETCH_STATUS = 0  
	begin  
		--создаем папку для БД
		if @type=1 OR @BaseName='master' set @tempcmd= 'md '+@FullPath+'' + @BaseName else set @tempcmd= 'md ' + @DiffPath + '' + @BaseName
		exec xp_cmdshell @tempcmd, no_output

		
		if @type=1 OR @BaseName='master'
			begin
				-- full backup
				set @tempname = @FullPath+''+@BaseName+''+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'.FULL'
				backup database @BaseName to disk = @tempname	
			end
		else
			begin
				-- diff backup
				set @tempname = @DiffPath+''+@BaseName+''+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'.DIFF'
				backup database @BaseName to disk =	@tempname with differential
			end


		-- удаляем лишние бэкапы
		declare @delpath varchar(500)=''
		declare delbackup cursor for
		select physical_device_name 
				from msdb..backupfile as bf join msdb..backupmediafamily as bmf	on bf.backup_set_id=bmf.media_set_id
				join msdb..backupset bs	on bf.backup_set_id=bs.backup_set_id
				where bf.file_type='D' and type = 'D' and database_name=@BaseName
					and not bmf.media_set_id in (
						select top (@MinFull) bmf.media_set_id
							from msdb..backupfile as bf join msdb..backupmediafamily as bmf	on bf.backup_set_id=bmf.media_set_id
							join msdb..backupset bs	on bf.backup_set_id=bs.backup_set_id
							where bf.file_type='D' and type = 'D' and database_name=@BaseName
							order by backup_finish_date desc
						)	
		union all
		select physical_device_name 
				from msdb..backupfile as bf join msdb..backupmediafamily as bmf	on bf.backup_set_id=bmf.media_set_id
				join msdb..backupset bs	on bf.backup_set_id=bs.backup_set_id
				where bf.file_type='D' and type = 'I' and database_name=@BaseName
					and not bmf.media_set_id in (
						select top (@MinDiff) bmf.media_set_id
							from msdb..backupfile as bf join msdb..backupmediafamily as bmf	on bf.backup_set_id=bmf.media_set_id
							join msdb..backupset bs	on bf.backup_set_id=bs.backup_set_id
							where bf.file_type='D' and type = 'I' and database_name=@BaseName
							order by backup_finish_date desc
						)	



		 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

		fetch next from BaseList into @BaseName  
    end
  
close BaseList  
deallocate BaseList 


-- чистим в MSDB информацию о старых архивах (свыше 120 дней)
declare @oldest DATETIME 
SET @oldest = DATEADD(DAY, -120, GETDATE())

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest

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

Автор: Isiirk

Источник