Archiving Microsoft SQL Server Databases

On duty, I come across MSSQL database servers. Often it is necessary to quickly set up a database backup, on test servers, and in production as well. At the same time, you can find many separate monosyllabic sources in the network, as it is necessary or not necessary to archive, but nowhere are there any more or less universal ready-made solutions. At the new place of work I again encountered this problem. For certain reasons, all the databases in the company (for now) are in the simple recovery model mode, because the solution given in the text is not complete, but judging by the questions on the forums, beginners and just developers and administrators far from these tasks, it is quite suitable decision, well, in the process, everyone can add it himself.

The script archives the specified databases, taking into account on which days of the week to make full copies, how much to store the last full and differential archives. Designed for archiving databases with a simple recovery model, if you need to archive logs too, then by analogy you can easily add the script yourself. Perhaps, when I need it, I will add this script. At the end of archiving, obsolete database archives are deleted from the disk.

This code can be immediately added to the assigned task and run it once a day or more often, as you like, while setting some parameters, the script is sufficiently commented, I’ll stop only briefly:

Specify the paths where we will store our archives, at the moment of starting the archiving in these folders, subfolders will be created for each archived database:
')

--    declare @FullPath varchar(500) = 'D:\Work\Full' declare @DiffPath varchar(500) = 'D:\Work\Diff' 

If we do not specify a list of specific databases in @IncludeBase for archiving, then all databases are taken and the databases specified in @ExcludeBase are excluded from them:

 --       declare @IncludeBase varchar(500) = '' --   ,     ,       declare @ExcludeBase varchar(500) = 'master, model, tempdb' 

Here we specify the days of the week separated by commas for the full archives:

 --      declare @FullDay varchar(13) = '7' 

How many last copies for each type of archives to leave on the disk:

 --     declare @MinFull int = 3 declare @MinDiff int = 3 

When the script is running on the server, compression of archives is enabled and the ability to run the xp_cmdshell procedure. The service must have read / write / delete permissions to directories with archives.

Script text:

 --    declare @FullPath varchar(500) = 'D:\Work\Full' declare @DiffPath varchar(500) = 'D:\Work\Diff' --       declare @IncludeBase varchar(500) = '' --   ,     ,       declare @ExcludeBase varchar(500) = 'model, tempdb' --      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 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='' 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 @type bit = 0 declare @notexistfull 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 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' ) set @notexistfull=0 else set @notexistfull=1 --    if @type=1 OR @BaseName='master' OR @notexistfull=1 set @tempcmd= 'md '+@FullPath+'\'+@BaseName else set @tempcmd= 'md '+@DiffPath+'\'+@BaseName exec xp_cmdshell @tempcmd, no_output if @type=1 OR @BaseName='master' OR @notexistfull=1 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 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 ) 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 not s.backup_set_id in ( SELECT TOP (@MinDiff) 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='I' 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     ( 120 ) declare @oldest DATETIME SET @oldest = DATEADD(DAY, -120, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest 

This script is written for specific tasks, it’s impossible to discuss that in production, and so on, I think it makes no sense, it’s published in order to give unprepared people an opportunity to get a ready-made solution.

I ’ll add from the ideatum user note that the xp_cmdshell procedure is disabled by default in Microsoft SQL Server, for security reasons.

UPDATE
The script is modified to create a full backup for the database at startup, if it is missing, even if today is not the day to create it

UPDATE2
Added check on the state of the database (ONLINE)