BizTalk Server Backup Job Does Not Delete Backup Files

The SQL Server Agent job named "Backup BizTalk Server" does not provide functionality for deleting backup files that have accrued over time. This can cause the disk that houses the backup files to fill up which can eventually cause the Backup BizTalk Server job to fail or other problems associated with limited disk space to occur.

To workaround this issue follow these steps:

1. Start SQL Server Management Studio.
2. Launch the Query Editor.
3. Connect to the BizTalk Management database (BizTalkMgmtDb by default)
4. Execute the following SQL Script to create the stored procedure sp_DeleteBackupHistoryAndFiles

   CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null AS BEGIN set nocount on IF @DaysToKeep IS NULL OR @DaysToKeep <= 0 RETURN /* Only delete full sets If a set spans a day such that some items fall into the deleted group and the other doesn't, do not delete the set */ DECLARE DeleteBackupFiles CURSOR FOR SELECT 'del "' + [BackupFileLocation] + '\' + [BackupFileName] + '"' FROM [adm_BackupHistory] WHERE datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep ) DECLARE @cmd varchar(400) OPEN DeleteBackupFiles FETCH NEXT FROM DeleteBackupFiles INTO @cmd WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles print @cmd END FETCH NEXT FROM DeleteBackupFiles INTO @cmd END CLOSE DeleteBackupFiles DEALLOCATE DeleteBackupFiles END GO

5. Modify the "Clear Backup History" step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles rather than calling sp_DeleteBackupHistory.

6. Enable xp_cmdshell for the SQL Server instance if it is disabled (xp_cmdshell is disabled by default). See the SQL Server online help for information about enabling xp_cmdshell.