BizTalk Backup Files - deleting the old ones

BizTalk Backup Files - deleting the old ones

  • Comments 2

The BizTalk Back Up job does not delete the old files for safety reasons.  This obviously must be addressed by you.

The proposed solution will take advantage of the out of the box database Back Up mechanism to avoid using extra tasks or external procedures.

REALLY Important Note One: This is the crazy coding way to achieve it

Thanks to my mate, Stephan Pepersack, who has just asked me “ Cool but, Why not just create a SQL Server Maintenance Job that deletes the files????  :-)

 

REALLY Important Note Two: This is not an out of the box feature

As it is going to be coded by YOU, it has never been tested. So, may not be supported by Microsoft

 

Having a look at the BizTalk Backup Job

Go to the SQL Server Agent node on SQL Server management Studio and locate the BizTalk BackUp Job. Go to Stepsimage

Basically, this how the backup process creates the Files:

  1. BackUpFull: As the name states will create the full backup of each database included in the process
  2. MarkAndBacupLog: will create the backup of the transactional logs (more complex than that but by the moment it is all we need to know)
  3. Clear Backup History:  will delete the internal old backup table rows responsible to log the backup operations already completed. This table is called  adm_BackupHistory

This is  the step we need to focus on.

Now, Click on Edit button to see what is running in this step.

image

As you can see, it runs a stored procedure called sp_DeleteBackUpHistory. As it name states, will delete the history rows older than @DaysToKeep days ago (in the table adm_BackupHistory ), in other words, in this case,  will retain 14 days of activity.

The plan is this:

We are going to create a new stored procedure to substitute the shipped one adding the delete files functionality in the same way the adm_BackupHistory is being  updated.  In this case: deleting the files older than 14 days.

 

Prerequisites (xp_cmdshell )

To delete de files we are using the DOS command: del. In order to grant SQL Server to run it, is needed to ensure xp_cmdshell is activated on the SQL Server instance.

Just open a new SQL query on SQL Server Management Studio and try to run the following code:

EXEC xp_cmdshell 'dir c:\'

if you get an error like this:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

You will need to activate xp_cmdshell execution

 

How to activate xp_cmdshell execution

just run the following T-SQL Script:

--to see the current configured settings on the instance--

exec sp_configure
GO


exec sp_configure 'xp_cmdshell', 1 -–Activates the xp_cmdShell Execution
GO
RECONFIGURE  --Applies the changes
GO

Warnings

1. Be aware that this procedure will apply all pending configurations. You need to ensure that running the RECONFIGURE command will cause no problems on your environment.

2. For security reasons you may not activate the xp_cmdShell. Someone with T-SQL execution access can execute malicious code against your server.

 

The sp_DeleteBackupHistoryCustom procedure

Run the following T-SQL query to create the procedure.

USE [BizTalkMgmtDb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryCustom] @DaysToKeep smallint = null

AS

BEGIN

DECLARE @BackupFileName varchar(255), @BackupFileLocation varchar(255)

DECLARE @CmdCommand varchar (300)

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

*/

/*we get all the files to delete from table  [adm_BackupHistory] */

--A cursor is opened

DECLARE Backup_cursor CURSOR

FOR

SELECT [BackupFileName]

,[BackupFileLocation]

FROM [dbo].[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 )

OPEN Backup_cursor

FETCH NEXT FROM Backup_cursor INTO @BackupFileName, @BackupFileLocation

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

SET @CmdCommand = 'del ' + @BackupFileLocation + '\' + @BackupFileName

EXEC master..xp_cmdShell @CmdCommand  -- Deleting the files

END

FETCH NEXT FROM Backup_cursor INTO @BackupFileName, @BackupFileLocation

END

CLOSE Backup_cursor

DEALLOCATE Backup_cursor

--Historic deletion logic

DELETE [dbo].[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 )

END

 

The final step will be to edit the Clear Backup History step by calling the already created stored procedure sp_DeleteBackupHistoryCustom

That’s all! :-)

Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
  • Please comment on the "REALLY Important Note One: This is the crazy coding way to achieve it".

    Is it because you wanted to "avoid using extra tasks or external procedures"?

    Why is the unsupported RECONFIGURE better considering that "For security reasons you may not activate the xp_cmdShell"?

  • Hi Igor,

    The reconfigure action it is fully supported.

    In this case though, the full procedure I am showing here, it is not supported because Microsoft did not tested.

Page 1 of 1 (2 items)