BizTalk SQL Server Job does not delets backup files


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]


/****** Object:  StoredProcedure [dbo].[sp_DeleteBackupHistoryAndFiles]    Script Date: 10/26/2010 16:40:18 ******/





ALTER PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null



set nocount on

IF @DaysToKeep IS NULL OR @DaysToKeep <= 0



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)


IF (@@fetch_status <> -2)


EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles

print @cmd


FETCH NEXT FROM DeleteBackupFiles INTO @cmd


CLOSE DeleteBackupFiles

DEALLOCATE DeleteBackupFiles


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.


About Naushad Alam
I am a BizTalk developer/administrator/designer/architect with over 9 years of experience. Currently I am living in UK and working with one of the largest bank as a Senior BizTalk Consultant. I believe in sharing my experience with others and learn more while helping or solving others problem.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: