Technical Article

Custom cleanup procedure for backup files


There are times when maintenance plan is too simple to do the job, and SSIS is unreachable for you. Here is a simple stored procedure that will make use of xp_cmdshell stored procedure to cleanup backup files from a folder. You can add any additional logic relevant to your setup.

  • Disclaimer: You know database backups are like life support for DBA in times of crisis. Mess up with it at your own risk. Test well and use it with caution.
  • Note: Enabling xp_cmdshell makes your server open to threats. Please see BOL on this topic. Disable it immediately after use.


The procedure:
Enough warnings!! Now lets see what the procedure does. The procedure assumes that the backup filename starts with the database name eg. Mydb_someformat.bak. The backup date is calculated connecting to the msdb database. This procedure will work only on the corresponding server.

The procedure uses two parameters.A) The folder to check and b) Number of days retention ie, If you need to cleanup files older than 10 days, your second parameter is 10.

The advantage of the procedure is that you can add any custom logic to the procedure to suit your requirement.

A table is created to keep track of all the deletes.


USE [master]
create table dba_Cleanup_history
 id int identity(1,1) primary key,
 location varchar(500),
 filename varchar(100),
 date datetime



CREATE procedure [dbo].[USP_DBA_BACKUP_Cleanup]
@P_Database varchar(100),
@P_Folder varchar(1000),
@P_Days int
as begin

set nocount on
declare @Days_todelete int 
declare @database varchar(100)
declare @folder varchar(1000)
declare @SQL varchar(1000)
declare @filename varchar(100)
set @Days_todelete=@P_Days
set @database=@P_Database
set @folder=@P_Folder

declare @backups_in_folder table
location varchar(500)

declare @backups_to_delete table
location varchar(500)

set @sql='dir "'+@folder+'"'

insert into @backups_in_folder
execute xp_cmdshell @SQL

--Delete unwanted detailes
--You can also change ‘%.bak%’ to ‘%.trn%’ here.

delete from @backups_in_folder where 
isnull(location,'') not like '%.bak%'

update  @backups_in_folder set location=substring(location,patindex('%'+@database+'%',location),len(location)) 
--I told you, the backup name should start with database name
--Else the the names wont get trimmed correctly

-- Now here you can add any additional logic if required

insert into @backups_to_delete
select substring(physical_device_name,patindex('%'+@database+'%',physical_device_name),len(physical_device_name))
from msdb.dbo.backupset b left join msdb.dbo.backupmediafamily m
on b.media_set_id =m.media_set_id
where b.database_name=@database
and backup_finish_date<dateadd(day,-1*@Days_todelete,getdate())
and substring(physical_device_name,patindex('%'+@database+'%',physical_device_name),len(physical_device_name)) in (select location from @backups_in_folder)
order by backup_start_date;

declare  cur_del cursor
select D.location from @backups_to_delete D
inner join @backups_in_folder F
ON F.location=D.Location
Declare @command_output int
open cur_del
fetch next from cur_del into @filename

while @@fetch_status=0
set @sql='del '+@folder+@filename

-- I have commented the code that deletes the file and written a print 
-- instead Play with it enough before deleteing 

print @sql
--exec @command_output=master.dbo.xp_cmdshell @sql,no_output
if @command_output=0
insert into master.dbo.dba_Cleanup_history

fetch next from cur_del into @filename


--select * from master.dbo.dba_Cleanup_history


5 (1)

You rated this post out of 5. Change rating




5 (1)

You rated this post out of 5. Change rating