Update to Stored Procedure to delete Backup BizTalk Server SQL Agent backup files

Posted: February 11, 2011  |  Categories: BizTalk SQL Uncategorized
Tags:
Since BizTalk 2004 there has been an issue with the Backup BizTalk Server SQL Agent job (this is the only supported way to backup the BizTalk Server Databases), the issue is that the job never deletes the backup files it creates.  There is a step in the job that clears the backup history table in the database but never delete the backup files from the disk.

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

Installation Instructions:

  • 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

turbo360

Back to Top