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

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

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

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

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

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

Если не указываем список конкретных БД в @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:\Work\Full'
declare @DiffPath varchar(500) = 'D:\Work\Diff'

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

-- дни недели для полных бэкапов
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 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='' 
		insert into @BaseListIncl select name from sys.databases where state_desc='ONLINE' 
	else
		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 @type bit = 0
declare @notexistfull 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 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' )
			set @notexistfull=0 else set @notexistfull=1



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

		
		if @type=1 OR @BaseName='master' OR @notexistfull=1
			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 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 not s.backup_set_id in 
						(
							SELECT TOP (@MinFull) s.backup_set_id 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' 
								ORDER BY s.backup_finish_date desc
						) 
		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 not s.backup_set_id in 
						(
							SELECT TOP (@MinDiff) s.backup_set_id 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'
								ORDER BY s.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

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

Добавлю из замечания пользователя ideatum, что процедура xp_cmdshell по умолчанию отключена в Microsoft SQL Server, по соображениям безопасности.

UPDATE
Скрип доработан для создания полного бэкапа для БД при запуске, если он отсутсвует, даже если сегодня не день для его создания

UPDATE2
Добавлена проверка на состояние БД (ONLINE)