Archive old database backup files using TSQL Script - SQL Server

I experienced into one issue for database backups were failing. And this was due to space issues on the disk drive. This disk drive is specific to allocate for the database backups only. The space was eaten by this database backups and this drive contains so many old backups. I have manually deleted all files and continue this activity so many days. If you create a maintenance plan then it have the option to delete old backup files. But I have the stored procedure for the database backups. So I do not have the option to delete old and unused database backup files.

Finally, I have created a script to clean those old backups. I have created one stored procedure in which you need to pass their parameters, One is Backup type as want to delete full, differential or transaction log backups. Second is From days and the third one is End day.

Please make sure XP_CMDSHELL is enabled on the database instance as this is required to enable it to delete database backup files to be deleted physically. Here is a query to enable it.

USE MASTER
GO

EXEC SP_CONFIGURE 'show advanced options',1
GO
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
GO
RECONFIGURE
GO

I have already told you as I have created scripts to delete the old DATABASEPROPERTY backups, please find below SP for the same.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteDBBackupOldFiles]
@BackupType char(1) = 'D', -- 'D'-Full, 'I'-Differential, 'L'-Log backup type
@StartDayFromToday int,
@EndDayFromToday int
AS
BEGIN
SET NOCOUNT ON

DECLARE @IsFileExists int
DECLARE @DeletedFile VARCHAR(500)
DECLARE @OldFiles VARCHAR(500)

DECLARE OldFiles CURSOR FAST_FORWARD  FOR
SELECT 
bmf.physical_device_name
from msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
WHERE DEVICE_TYPE = 2 
AND TYPE = @BackupType 
AND BACKUP_START_DATE < = GETDATE() - @StartDayFromToday
AND BACKUP_START_DATE > = GETDATE() - @EndDayFromToday
-- AND DATEDIFF(DAY,BACKUP_START_DATE,GETDATE()) > BETWEEN @StartDayFromToday and @EndDayFromToday 

OPEN OldFiles

FETCH NEXT FROM OldFiles INTO @OldFiles

WHILE @@FETCH_STATUS =0
BEGIN

EXEC XP_FILEEXIST @OldFiles,@IsFileExists OUTPUT
IF @IsFileExists=1
BEGIN

PRINT 'Deleted File : ' + @DeletedFile

SET @DeletedFile = 'DEL ' + @OldFiles

EXEC XP_CMDSHELL @DeletedFile

END
FETCH NEXT FROM OldFiles INTO @OldFiles
END
CLOSE OldFiles
DEALLOCATE OldFiles
END

Finally, after creating a stored procedure, it is time to run and clean the old database backups. After running it will remove all the old database backups as per specified parameters. Like we have passed 'D', 3 and 10 with SP, So I will delete Full database backups while being older than 3 days ago and 10 days before created.

EXEC [DeleteDBBackupOldFiles] 
  @BackupType  = 'D',
  @StartDayFromToday = 3,
  @EndDayFromToday = 10

Which method you use for old database backup maintenance? You can read my earlier posts for deleting files using File System Task and Script Task in SSIS.