You are here:   Home
  |  Login

BizTalkBill's Ramblings

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

Feb 11

Written by:
2/11/2011 3:55 PM  RssIcon

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

Tags:
Categories:

2 comment(s) so far...


Gravatar

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

Do you know of a way to recreate the Backup BizTalk Server job? Our BT server does not have this job and is instead using LiteSpeed backups, which based on my reading, are invalid if they are ever needed. Can I recreate the job using the stored procs in the MgmtDB, or is there some re-installation that needs to be done? (BT 2004/SQL 2005)

By Confused DBA on   9/15/2011 9:19 AM
Gravatar

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

Yes, the source for the job is in the BizTalk directory under the Schema directory in file BTS_Administration_Logic.sql near the bottom. Search for "Backup BizTalk", once you find it copy that bit of SQL script into a query window and execute. I have had to do this recently for one of my clients.

By Bill Chesnut on   9/15/2011 9:25 AM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 

Text/HTML













\




MCP.gif MCT.gif MCPD(rgb)_506.gif
MCTS_511.gifMCTS(rgb)_1271.gifMCTS(rgb)_1325.gif