Microsoft SQL Server database backup script with full recovery model
In continuation of my previous article on archiving MSSQL databases and the negative response due to the lack of the possibility of archiving transaction logs, I now work in a company where it was necessary to automate this moment for databases, including those with a full recovery model.
The script works universally for databases with different recovery models; at the beginning of the script, settings have been added for relatively flexible scheduling. The script can be installed with SQL Agent and a convenient interval (for example, I have 1 time per hour), the first run in a day will check whether or not a full or differential copy should be created today and then backups will be created for a database with a full recovery model during the day transaction log log.
-- пути до бэкапов
declare @FullPath varchar(500) = 'D:\SQLBackup'
declare @DiffPath varchar(500) = 'D:\SQLBackup'
declare @LogPath varchar(500) = 'D:\SQLBackup'
-- архивируемые и исключенные из архивации БД
declare @IncludeBase varchar(500) = 'master, msdb' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные
declare @ExcludeBase varchar(500) = 'tempdb'
-- настроки для полного бэкапа
-- минимум хранить дней
declare @MinFullDay int = 181
-- выполнять каждые дней
declare @MinFullExecDay int = 7
-- настроки для разностного бэкапа
-- минимум хранить дней
declare @MinDiffDay int = 181
-- выполнять каждые дней
declare @MinDiffExecDay int = 1
-- настроки для бэкапа журнала
-- минимум хранить дней
declare @MinLogDay int = 35
-- как часто создавать master (дни)
declare @MinDayMaster int = 1
-- сколько хранить информацию об архивах в msdb (дни)
--declare @DayHistory int = @MinFullDay + 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;
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
set @tempcmd= 'md '+@LogPath
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"
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<>''
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 @BackupType varchar(10) = ''
open BaseList
fetch next from BaseList into @BaseName
while @@FETCH_STATUS = 0
begin
-- получаем тип бэкапа, который надо сделать (для мастера специальные условия, он не может ни каких кроме FULL)
SET @BackupType =
( CASE
WHEN @BaseName="master" AND
(EXISTS (SELECT * 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 DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinDayMaster))
THEN ''
WHEN @BaseName="master" AND
(NOT EXISTS (SELECT * 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 DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinDayMaster))
THEN 'FULL'
WHEN (NOT EXISTS (SELECT * 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 DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinFullExecDay))
THEN 'FULL'
WHEN (NOT EXISTS (SELECT * 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 IN ('I','D') and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) < @MinDiffExecDay))
THEN 'DIFF'
WHEN (EXISTS (select * from sys.databases as a where a.recovery_model_desc="FULL" and a.name = @BaseName))
THEN 'LOG'
ELSE ''
END
)
IF @BackupType <> ''
begin
--создаем папку для БД
SET @tempcmd =
(CASE
WHEN @BackupType="FULL" OR @BaseName="master" THEN 'md '+@FullPath+'\'+@BaseName
WHEN @BackupType="DIFF" THEN 'md '+@DiffPath+'\'+@BaseName
ELSE 'md '+@LogPath+'\'+@BaseName
END)
exec xp_cmdshell @tempcmd, no_output
if @BackupType="FULL"
begin
-- full backup
set @tempname = @FullPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_FULL.BAK'
backup database @BaseName to disk = @tempname
end
else if @BackupType="DIFF"
begin
-- diff backup
set @tempname = @DiffPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_DIFF.BAK'
backup database @BaseName to disk = @tempname with differential
end
else
begin
-- log backup
set @tempname = @LogPath+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_LOG.TRN'
BACKUP LOG @BaseName to disk = @tempname
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 DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) > @MinFullDay
and is_copy_only = 0
union all
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='I'
and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) > @MinDiffDay
and is_copy_only = 0
union all
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='L'
and DATEDIFF(day, CAST(s.backup_finish_date AS DATE), CAST(GETDATE() AS DATE)) > @MinLogDay
and is_copy_only = 0
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
end
fetch next from BaseList into @BaseName
end
close BaseList
deallocate BaseList
-- чистим в MSDB информацию о старых архивах
--declare @oldest DATETIME
--SET @oldest = DATEADD(DAY, -@DayHistory, GETDATE())
--EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldestI will also add a script for creating “long-term storage” backups. They are created with the ONLY_COPY option and do not participate in the general recovery chain, I usually use it once or twice a month to make a full copy or as needed
-- ========== СОЗДАЕМ ТОЛЬКО ПОЛНЫЕ БЭКАПЫ С ОПЦИЕЙ ONLY_COPY (не учавствуют в общей цепочке)
-- пути до бэкапов (для типовых)
declare @Path varchar(500) = 'I:\SQLBackupOnlyCopy'
-- архивируемые и исключенные из архивации БД
declare @IncludeBase varchar(500) = 'buh, zup' -- если не пусто, то только эти минус исключенные, если пусто то все минус исключенные
declare @ExcludeBase varchar(500) = 'tempdb'
-- включим сжатие
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;
declare @tempcmd varchar(500) =''
declare @tempname varchar(500) =''
-- создание путей
set @tempcmd= 'md '+@Path
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"
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<>''
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 @BackupType varchar(10) = ''
open BaseList
fetch next from BaseList into @BaseName
while @@FETCH_STATUS = 0
begin
--создаем папку для БД
SET @tempcmd = 'md '+@Path+'\'+@BaseName
exec xp_cmdshell @tempcmd, no_output
--создаем архив
set @tempname = @Path+'\'+@BaseName+'\'+@BaseName+'_'+CONVERT(varchar(8), GETDATE(), 112)+ '-' + REPLACE(CONVERT(varchar, GETDATE(),114),':','') +'_FULL_CopyOnly.BAK'
backup database @BaseName to disk = @tempname WITH COPY_ONLY
fetch next from BaseList into @BaseName
end
close BaseList
deallocate BaseList 
