Script to delete old SQL Server Backup Files Using MSDB Backup History Data

USE master
GO
-- enable show advanced options
exec sp_configure 'show advanced options', 1
go
RECONFIGURE
GO
-- enable Ole Automation Procedures
exec sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO

USE master
GO

-- =================================================================================
-- Author:         Eli Leiba
-- Create date:    2018-09
-- Procedure Name: dbo.usp_DeleteOldBackupFiles
-- Description:
--   The procedure looks over the backup history table in the MSDB database:
--   And searches for backup files older than @DaysOld days before 
--   The current procedure Execution date. 
--   If the @mode parameter = R, it only reports the file names and
--   If the @mode parameter = D it reports and deletes the files. 
-- ==================================================================================
CREATE PROCEDURE dbo.usp_DeleteOldBackupFiles (@DaysOld INT, @mode CHAR (1))
AS
BEGIN
   DECLARE @dbName SYSNAME
   DECLARE @SD DATETIME
   DECLARE @FD DATETIME
   DECLARE @filename VARCHAR (255)
   DECLARE @msg VARCHAR (300)
   DECLARE @fileExists INT
   DECLARE @Filehandle INT

   SET NOCOUNT ON

   DECLARE c_fileList CURSOR
   FOR
   SELECT b.database_name,
      b.backup_start_date,
      b.backup_finish_date,
      a.physical_device_name
   FROM msdb.dbo.backupmediafamily a,
      msdb.dbo.backupset b
   WHERE a.media_set_id = b.media_set_id AND 
      (CONVERT (DATETIME, b.backup_start_date, 102) < GETDATE () - @DaysOld) 
   ORDER BY b.database_name, b.backup_finish_date;
				
   -- create a file system object
   EXEC sp_OACreate 'Scripting.FileSystemObject', @Filehandle OUTPUT
   
   -- open working cursor and loop over all rows.
   OPEN c_fileList;
				
   FETCH NEXT  FROM c_fileList INTO @dbName, @SD, @FD, @filename
   WHILE @@FETCH_STATUS = 0
   BEGIN
      EXEC Master.dbo.xp_fileexist @filename, @fileExists OUT
      IF @fileExists = 1
      BEGIN
         SET @msg = CONCAT (
            'Delete backup file: '
            @filename,
            'For Database: '
            @dbName,
            'Started on: '
            Convert (VARCHAR (12), @sd, 103),
            'Finished on: '
            Convert (VARCHAR (12), @fd, 103)
            )
				
            IF (UPPER (@mode) IN ('R','D'))
            BEGIN
               PRINT @msg
            END
				
            -- If mode = D then delete the file 
            IF (UPPER (@mode) = 'D')
	   BEGIN
	      EXEC sp_OAMethod @Filehandle, 'DeleteFile', NULL, @filename
	   END
      END

      FETCH NEXT FROM c_fileList INTO @dbName, @sd, @fd, @filename
   END
   CLOSE c_fileList;
   DEALLOCATE c_fileList;
				
   -- Memory cleanup
   EXEC sp_OADestroy @Filehandle
   SET NOCOUNT OFF
END
GO			

USE master
GO
EXEC dbo.usp_DeleteOldBackupFiles @DaysOld=700, @mode = 'R'
GO

USE master
GO
EXEC dbo.usp_DeleteOldBackupFiles @DaysOld=700, @mode = 'D'
GO