Архівація баз даних 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) = '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 'страт', 1
RECONFIGURE WITH OVERRIDE

set datefirst 1
declare @tempcmd varchar(500) ="
declare @tempname varchar(500) ="

-- створення шляхів
set @tempcmd= 'md '+@FullPath
exec страт @tempcmd, no_output
set @tempcmd= 'md '+@DiffPath
exec страт @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 страт @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 страт @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 @DATETIME oldest 
SET @oldest = DATEADD(DAY, -120, GETDATE())

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest

Даний скрипт написаний під конкретні завдання, обговорювати, що в продакшені так не можна і інше думаю не має сенсу, публікується з метою дати можливість непідготовленим людям отримати готове рішення.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.