Настройка автоматической архивации баз данных 1С на MS SQL Server

Как создать скрипт для архивации баз 1С. Как настроить расписание выполнения скрипта на сервере MS SQL. Как обеспечить надежное хранение архивов баз данных.

Текст скрипта:

-- пути до бэкапов
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

Facebook
LinkedIn
WhatsApp
Telegram
Комментарии