Introduction
BizTalk SQL server backup jobs does not deletes the old backup files (which keeps on using the space unless manually delete them) from the backup location.
However it does deletes the backup history from the table, which is configured in the “Clear Backup History” step of “Backup BizTalk Server (BizTalkMgmtDb)” job. To delete the old backup files following solution can be used which creates a new database job and modifies the “Clear Backup History”step of the BizTalk Job. I have modified the Stored procedure to delete all the files which are less then given hour. This keeps my backup location clean and tidy. Since we are using log shipping for our BizTalk recovery process, so I would not like to keep any backup file which is older than 14 hours.
Following steps were performed to implement this solution.
- Connect to the SQL Server instance using Management studio. Launch a new SQL Query window and write following SQL,
USE [BizTalkMgmtDb]
GO
/****** Object: StoredProcedure [dbo].[sp_DeleteBackupHistoryAndFiles] Script Date: 10/26/2010 16:40:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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 in such a way that some items fall into the deleted group and the other does not, do not delete the set
*/
DECLARE DeleteBackupFiles CURSOR
FOR SELECT ‘del “‘ + [BackupFileLocation] + ‘\’ + [BackupFileName] + ‘”‘ FROM [adm_BackupHistory]
WHERE datediff( hh, [BackupDateTime], getdate() ) >= @DaysToKeep
AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( hh, [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
After execution of above SQL script a new stored procedure gets created in “BizTalkMgmtDB”
- Connect to SQL Server agent on the instance and modify properties of the “Clear Backup History” step of “Backup BizTalk Server” SQL Job as follows.
This will call the stored procedure which will delete all the backup files which are older than 15 Hours and also deletes the entries from “adm_BackupHistory” table of BizTalkMgmtDb database.
This keeps my backup location clean and does not require any failure in the backup job if the location is full.
Please see the following article for more details.
http://support.microsoft.com/kb/982546
Naushad Alam's We[b]log 