In BizTalk 2004 I create a new stored procedure sp_DeleteBackupHistoryAndFiles to be used instead of the Microsoft supplied sp_DeleteBackupHistory, this stored procedure will delete the backup files create by the job when they exceed the value specified in the DaysToKeep parameter of the Clear Backup History step. With the release of BizTalk 2010 there have been changes to the sp_DeleteBackupHistory stored procedures, they are adding a parameter to the stored procedure to use local time and changed to the query to prevent the deletion of the history from the last full backup set forward.
I have made the same update to my sp_DeleteBackupHistoryAndFiles stored procedure, this stored procedure can be downloaded from the download page on BizTalkBill.com and it is called BizTalk 2010 Stored Procedure to delete backups
Warning: sp_DeleteBackupHistoryAndFiles requires xp_cmdshell to be enabled on the SQL Server
- Download and execute the script in your BizTalk Management Database (BizTalkMgmtDb)
- Open the Backup BizTalk Server SQL Agent Job
- Open the Clear Backup History Step
- Change the stored procedure being execute from sp_DeleteBackupHistory to sp_DeleteBackupHistoryAndFiles
- Add @UseLocalTime = 1 (if you are using local time in the MarkAndBackupLog step
- Click OK until the job is saved.
If instruction to enable xp_cmdshell are needed please see SQL Books Online – http://msdn.microsoft.com/en-us/library/ms190693(v=SQL.100).aspx
Please Note: I am in the final process of testing a SSIS Package to delete the backup files that does not require xp_cmdshell