[Из песочницы] Архивация баз данных Microsoft SQL Server
По долгу службы сталкиваюсь с серверами баз данных MSSQL. Часто необходимо быстро настроить архивацию БД, на тестовых серверах, да и в продакшене. При этом в сети можно найти много разрозненных односложных источников, как надо или не надо архивировать, но нигде нет каких то более или менее универсальных готовых решений. На новом месте работы опять столкнулся с данной проблемой. В силу определенных причин все БД в компании (пока) находятся в режиме простой модели восстановления, потому решение, приведенное в тексте является не полным, но судя по вопросам на форумах, начинающим и просто разработчикам и администраторам, далеким от данных задач, вполне подойдет как решение, ну, а в процессе каждый может дополнить его сам.
Скрипт выполняет архивацию указанных БД учитывая, по каким дням недели делать полные копии, сколько хранить последних полных и разностных архивов. Предназначен для архивации баз данных с простой моделью восстановления, если вам необходимо архивировать ещё и логи, то по аналогии вполне можете дополнить скрипт сами. Возможно, когда мне понадобится, я допишу данный скрипт. По окончанию архивации устаревшие архивы БД удаляются с диска.
Если не указываем список конкретных БД в @IncludeBase для архивации, то берутся все БД и из них исключаются базы указанные в @ExcludeBase:
Тут укажем через запятую дни недели для полных архивов:
Сколько последних копий для каждого типа архивов оставлять на диске:
При работе скрипта на сервере включается сжатие архивов и возможность запуска процедуры xp_cmdshell. У службы должны быть права на чтение/запись/удаление в каталоги с архивами.
Данный скрипт написан под конкретные задачи, обсуждать, что в продакшене так нельзя и прочее думаю не имеет смысла, публикуется с целью дать возможность неподготовленным людям получить готовое решение.
Скрипт выполняет архивацию указанных БД учитывая, по каким дням недели делать полные копии, сколько хранить последних полных и разностных архивов. Предназначен для архивации баз данных с простой моделью восстановления, если вам необходимо архивировать ещё и логи, то по аналогии вполне можете дополнить скрипт сами. Возможно, когда мне понадобится, я допишу данный скрипт. По окончанию архивации устаревшие архивы БД удаляются с диска.
Данный код можно сразу добавить в назначенное задание и запускать его раз в сутки или чаще, кому как нравится, при этом установив некоторые параметры, скрипт достаточно прокомментирован, остановлюсь лишь вкратце:
Указываем пути, где будем хранить наши архивы, в момент запуска архивации в данных папках создадутся подпапки по каждой архивируемой БД:
-- пути до бэкапов
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
Данный скрипт написан под конкретные задачи, обсуждать, что в продакшене так нельзя и прочее думаю не имеет смысла, публикуется с целью дать возможность неподготовленным людям получить готовое решение.
Комментарии (2)
1 декабря 2016 в 19:41
0↑
↓
Велосипед давно уже изобретен — https://ola.hallengren.com/sql-server-backup.html.1 декабря 2016 в 22:04 (комментарий был изменён)
+1↑
↓
Я еще в конце сжимаю rar кусками по 20Mb и через FTP отправляю на другой континент. И так лет 5 уже.
Так как не знаю где будет нанесёс первый удар.
