SQL Query to Delete the OLD Back up files based on No of Days

Here is a small SQL Script developed to delete the OLD Backup files based on the Date.

All you need to do is just check how many days old files should be deleted and provide the same in

Select statement after get date.

SET NOCOUNT ON

GO

sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

sp_configure 'xp_cmdshell', 1

GO

PRINT 'Running reconfigure'

RECONFIGURE

GO

DECLARE @rc INT

DECLARE @cmd VARCHAR(1000)

DECLARE @cursor CURSOR

DECLARE @physicaldevicename VARCHAR(1000)

DECLARE @Err VARCHAR(1000)

SET @cursor = CURSOR FOR SELECT b.physical_device_name AS physicaldevicename FROM msdb..backupset AS a

 inner join msdb..backupmediafamily as  b on a.media_set_id = b.media_set_id

 WHERE a.backup_start_date 

 < GETDATE()-5 ORDER BY a.backup_start_date,a.database_name

OPEN @cursor

WHILE 1=1

BEGIN

    FETCH FROM @cursor INTO @physicaldevicename

    IF @@fetch_status <> 0

            BEGIN

                  SET @Err = @@fetch_status

                  PRINT @Err

                  BREAK

            END

    ELSE

    BEGIN

        SET @cmd = 'del ' + @physicaldevicename

            PRINT 'Starting deleting the backup file ' + @cmd

            EXEC @rc = master.dbo.xp_cmdshell @cmd

            IF @rc <> 0

                  BEGIN

                        PRINT 'Backup File ' + @physicaldevicename + ' was not deleted'

                  END

            ELSE

                  BEGIN

                        PRINT 'Backup File ' + @physicaldevicename + ' deleted successfully at timestamp '+ CONVERT(VARCHAR,GETDATE(),109)

                  END

      END

END

CLOSE @cursor

DEALLOCATE @cursor

GO

sp_configure 'xp_cmdshell', 0

GO

RECONFIGURE

GO

sp_configure 'show advanced options', 0

GO

PRINT 'Running reconfigure'

RECONFIGURE

GO

SET NOCOUNT OFF