-- пути до бэкапов
declare @FullPath varchar(500) = 'E:\ReserveCopySQL'
— архивируемые и исключенные из архивации БД
declare @IncludeBase varchar(500) = » — если не пусто, то только эти минус исключенные, если пусто то все минус исключенные
declare @ExcludeBase varchar(500) = ‘model, tempdb, msdb’
— дни недели для полных бэкапов
declare @FullDay varchar(13) = ‘1,2,3,4,5,6,7’
— сколько минимум хранить архивов
declare @MinFull int = 30
— включим сжатие
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
— определяем список БД для архивации
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’ order by name
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 @description varchar(100) = »
— проверяем, какой сегодня нам создать архив
declare @today bit = 0
declare @notexistfull bit = 0
if CHARINDEX(CAST(DATEPART(weekday,getdate()) as varchar(1)),@FullDay)>0
set @today = 1
open BaseList
fetch next from BaseList into @BaseName
while @@FETCH_STATUS = 0
begin
—создаем папку для БД
set @tempcmd= ‘md ‘+@FullPath+’\\’+@BaseName
exec xp_cmdshell @tempcmd, no_output
—определим имя последнего архива
declare @last_backup varchar(500) = »
declare @backup_exist bit = 0
declare @backup_type varchar(10)=’log’
declare last_bases cursor for
select — s.backup_set_id, d.backup_finish_date, d.database_name,
mf.physical_device_name from (select max(d.backup_finish_date) as backup_finish_date,d.database_name from msdb.dbo.backupset as d where database_name = @BaseName group by database_name) as d
left join msdb.dbo.backupset as s on d.backup_finish_date=s.backup_finish_date and d.database_name = s.database_name
inner join msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
open last_bases
fetch next from last_bases into @last_backup
close last_bases
deallocate last_bases
set @tempname = @FullPath+’\\’+@BaseName+’\\’+@BaseName+’_’+CONVERT(varchar(8), GETDATE(), 112)+’.bak’
if @tempname = @last_backup set @backup_exist=1 else set @backup_exist = 0
if @today = 1 and @backup_exist=0 set @backup_type=’full’
set @description = @BaseName + @backup_type + ‘ backup’
if @backup_type=’full’ or @BaseName = ‘master’
begin
— full backup
backup database @BaseName to disk = @tempname WITH NOFORMAT, INIT, NAME = @description, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
end
else
begin
— backup logs
backup log @BaseName to disk = @last_backup WITH NOFORMAT, NOINIT, NAME = @description, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
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
)
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 информацию о старых архивах (свыше 30 дней)
declare @oldest DATETIME
SET @oldest = DATEADD(DAY, -120, GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest