SQL DBA - Delete OLD Backup files Using TSQL - SQL server 2005

Step 1 :

            USE MASTER

GO

Step 2 :

EXEC SP_CONFIGURE 'show advanced options',1

GO

Step 3 :

EXEC SP_CONFIGURE 'XP_CMDSHELL',1

GO

RECONFIGURE

GO

Step 4:

TSQL TO Delete All the Backup Files

DECLARE @DBbackuppath varchar(8000)

DECLARE @DBName varchar(255)

DECLARE @Command varchar(255)

DECLARE @Backup_finish_date datetime

DECLARE DATABASES_CURSOR CURSOR FOR

SELECT Physical_device_name,Backup_finish_date,database_name FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D' -- add a condition if u need a condition on Backup_finish_date

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBbackuppath,@Backup_finish_date,@DBName

WHILE @@FETCH_STATUS = 0

BEGIN

      FETCH NEXT FROM DATABASES_CURSOR INTO @DBbackuppath,@Backup_finish_date,@DBName

      set @DBbackuppath = 'del '+@DBbackuppath

      exec master..xp_cmdshell  @DBbackuppath

      END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR