Search This Blog

Monday, January 30, 2012

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