Custom cleanup procedure for backup files

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

GO
----------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



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
for
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
begin 
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
begin
insert into master.dbo.dba_Cleanup_history
values(@folder,@filename,getdate())
end

fetch next from cur_del into @filename
end 


End


--select * from master.dbo.dba_Cleanup_history