BizTalk SQL Server Job does not delets backup files

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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: