Delete old backup

-------------------------------------------------------------------------------------------
--                            Declare all the variables to be used
-------------------------------------------------------------------------------------------
Declare @FileName varchar(100),
        @DynDelete varchar(100),
        @path varchar(100),
        @DifDays varchar (100)=1  --specify number of days

-------------------------------------------------------------------------------------------
--                            enable the xp_cmdshell 
-------------------------------------------------------------------------------------------
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure

-------------------------------------------------------------------------------------------
--                            Create temp tables
-------------------------------------------------------------------------------------------
create table #dir 
(dir varchar(255))

create table #tobdelted
(nameoffile varchar(255),filedate datetime)

create table #tobdelted2
(nameoffile varchar(255),filedate datetime)


-------------------------------------------------------------------------------------------
--                            Insert files found into temp table (Please change path)
-------------------------------------------------------------------------------------------
insert into #dir exec master..xp_cmdshell 'dir "C:\Program Files\Microsoft SQL Server\MSSQL11.MATDATACENTRE\MSSQL\Backup"' 
set @path= '"C:\Program Files\Microsoft SQL Server\MSSQL11.MATDATACENTRE\MSSQL\Backup\"'
--change the folder path, do not forget the "\" at the end of the path 
-------------------------------------------------------------------------------------------
--                            Insert files to be deleted into appropriate table
-------------------------------------------------------------------------------------------
insert into #tobdelted
            (filedate,nameoffile)
select convert(date,(select SUBSTRING(dir,1,10)),110),( SELECT SUBSTRING(
(SELECT SUBSTRING(
(SELECT SUBSTRING(
(SELECT SUBSTRING(DIR, CHARINDEX(' ', DIR) + 1, 255)),
CHARINDEX(' ', DIR),255)),
CHARINDEX(' ', DIR),255)),
(CHARINDEX(' ', DIR)-2),255))
from #dir 
where (dir like '%/%%/%') and (dir like '%.trn' or dir like '%.bak')


-------------------------------------------------------------------------------------------
--                            Insert files to be deleted into appropriate table
-------------------------------------------------------------------------------------------
insert into #tobdelted2
            (filedate,nameoffile)
select filedate,nameoffile
from #tobdelted
WHERE (DATEDIFF(month, (select convert(varchar(100),filedate,110)), GETDATE()) = 0 
and DATEDIFF(day,(select convert(varchar(100),filedate,110)),getdate())>@DifDays) or 
(
DATEDIFF(month, (select convert(varchar(100),filedate,110)), GETDATE())!=0
)


-------------------------------------------------------------------------------------------
--                        use Curso to delete the files
-------------------------------------------------------------------------------------------
DECLARE curDelFile CURSOR
READ_ONLY
FOR


    SELECT[nameoffile]
    FROM #tobdelted2


OPEN curDelFile

FETCH NEXT FROM curDelFile INTO @FileName
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN

        SET @DynDelete = 'DEL '+@path + @FileName + ' '

        EXEC master..xp_cmdshell @DynDelete
 -- print 'EXEC master..xp_cmdshell'+@DynDelete

    END
    FETCH NEXT FROM curDelFile INTO @FileName
END

CLOSE curDelFile
DEALLOCATE curDelFile

-------------------------------------------------------------------------------------------
--                            Delete temp tables
-------------------------------------------------------------------------------------------
BEGIN TRY
 drop table #dir
    drop table #tobdelted
    drop table #tobdelted2
END TRY
BEGIN CATCH
 Print 'Tables do not exist'
END CATCH

 
-------------------------------------------------------------------------------------------
--                            Diable xp_cmdshell proc
-------------------------------------------------------------------------------------------
exec sp_configure 'xp_cmdshell', 0
reconfigure
exec sp_configure 'show advanced options', 0
reconfigure