Here's a simple stored procedure to call from the "Backup BizTalk Server" job in the "Clear Backup History" step. Just follow these steps:
- Open SQL Server management Studio
- Open a new query window and connect to the BizTalkMgmtDb database
- Execute this script to add a new stored procedure called sp_DeleteBackupHistoryAndFiles
USE [BizTalkMgmtDb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null, @UseLocalTime bit = 0 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 don't don't delete the set Delete history only if history of full Backup exists at a later point of time why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not. If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour) */ declare @PurgeDateTime datetime if (@UseLocalTime = 0) set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE()) else set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE()) DECLARE DeleteBackupFiles CURSOR FOR SELECT 'del "' + [BackupFileLocation] + case right(BackupFileLocation,1) when '\' then '' else '\' end + [BackupFileName] + '"' FROM [adm_BackupHistory] [h1] WHERE [BackupDateTime] < @PurgeDateTime AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime) AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db') 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
- 5.Modify the "Clear Backup History" step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles, instead of calling the stored procedure sp_DeleteBackupHistory
- Make sure xp_cmdshell for the SQL Server instance is enabled. this will be disabled by default. To enable this, execute following SQL script:
EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE
This stored procedure is made for BizTalk Server 2010.
Because with the release of BizTalk 2010 there have been changes to the sp_DeleteBackupHistory stored procedures. They added a parameter to the stored procedure to use local time and changed the query to prevent the deletion of the history from the last full backup set forward.
For older versions of BizTalk you should use this script instead:
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
Source: http://www.biztalkbill.com/Home/tabid/40/EntryId/81/Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx
