BizTalk Server Backup Job Does Not Delete Backup Files

BizTalk Server Backup Job Does Not Delete Backup Files

Important

This wiki post is a preliminary Knowledge Base article. The official version of this article has been published to http://support.microsoft.com/kb/982546.

Summary

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.

More Information

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.

Acknowledgements

Many thanks to William Chesnut (http://biztalkbill.com/)  for the SQL query code used above to delete old backup files.

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Ed Price - MSFT edited Revision 7. Comment: Title casing; adding tags

Page 1 of 1 (1 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Which BizTalk version does this apply to? All versions i.e. 2006 (R2), 2009, 2010?

    Yes, 2004 and later.  See support.microsoft.com/.../982546 for more information.

  • See support.microsoft.com/.../982546 for all of the BizTalk versions for which this applies.

  • It only states : "These steps have only been tested with BizTalk Server 2009 and later running on SQL Server 2008 and later.". Does not indicate if this also applicable for BizTalk 2010. Also other versions one needs to test it thoroughly.

  • Hi,

    In fact, these steps have only been tested with BizTalk Server 2009 running on SQL Server 2008 NOSP/SP1/SP2/SP3. For BizTalk Server 2010 these steps have not been tested yet that I know of. It turns out that the author of the original SP (William Chesnut - http://biztalkbill.com/) updated the SP for BizTalk Server 2010, see his blog post @ www.biztalkbill.com/.../Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx.  My recommendation would be that if you are running BizTalk Server 2010, you can certainly try running Bill's updated version but not in a production environment until our testers have run it through its paces. I've commented support.microsoft.com/.../982546 to reference the updated version of this SP and requested that our Product Support Team get sign off on running this against BizTalk Server 2010 databases after which time they will update the KB article.

    Regards,

    Trace

  • Hi,

    Thanks for update.

    Cheers,

    Steef-Jan

  • These steps of BizTalk are similar to oracle, great resource.

  • Ed Price - MSFT edited Revision 7. Comment: Title casing; adding tags

  • Very good article

Page 1 of 1 (8 items)