По долгу службы сталкиваюсь с серверами баз данных 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) = '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 Данный скрипт написан под конкретные задачи, обсуждать, что в продакшене так нельзя и прочее думаю не имеет смысла, публикуется с целью дать возможность неподготовленным людям получить готовое решение.
ссылка на оригинал статьи https://habrahabr.ru/post/316658/
Добавить комментарий