Backup Cleanup for the 'DTA Purge and Archive' job in BizTalk 2006 & BizTalk 2004 SP2

There's good documentation available for configuring the 'DTA Purge and Archive' functionality in BizTalk.

But what about the backups of the DTA Database ? Are they going to stay there forever ? In the documentation they say that you need to manage the cleanup of the backup files yourself.

Here is what i did :

I added a step in the 'DTA Purge and Archive' job in SQL called 'DeleteBackupHistory'.

• BizTalk 2006 on SQL Server 2005 :

DECLARE @OldestDate VARCHAR(50)

--Delete all backed up files created from the 'Purge&Archive' job (take 1 day more than specified in the job)

SELECT @OldestDate = CAST(DATEADD(d, -8, GETDATE()) AS VARCHAR)

EXECUTE master.sys.xp_delete_file 0,N'\\yourserver\yourshare\DTAArchive\',N'bak',@OldestDate,0

• BizTalk 2004 SP2 on SQL Server 2000 :

Since there is no stored procedure 'xp_delete_file' available in SQL Server you have to write a bit more code to do the job. The main principle is that it is looking in the dta_ArchiveHistory table to get the backups older than a specified date :

USE BizTalkDTADb

DECLARE @OldestDate DATETIMESELECT @OldestDate = DATEADD(d, -8, GETDATE())

SELECT dtTimeStamp, nvcBackupLocation FROM dta_ArchiveHistory WHERE dtTimeStamp < @OldestDate

DECLARE @WhichFile VARCHAR(300)

DECLARE @Cmd VARCHAR(500)

DECLARE cur CURSOR FAST_FORWARD FOR SELECT nvcBackupLocation FROM dta_ArchiveHistory WHERE dtTimeStamp < @OldestDate

OPEN cur
FETCH NEXT FROM cur INTO @WhichFile

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @cmd = 'del ' + @WhichFile + ' /Q /F'

EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

FETCH NEXT FROM cur INTO @WhichFile

END

CLOSE cur

DEALLOCATE cur