How To Delete Old SQL Backup Files

How To Delete Old SQL Backup Files


-------------------------------------------------------------------------------------------
--                            Declare all the variables to be used
-------------------------------------------------------------------------------------------
Declare @FileName varchar(100),
@DynDelete varchar(100),
@path varchar(100),
@DifDays varchar (100)=1  --Specify how old files are in days.

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

-------------------------------------------------------------------------------------------
--                            Create all temp
-------------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------------
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