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.
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???? :-)
As it is going to be coded by YOU, it has never been tested. So, may not be supported by Microsoft
Go to the SQL Server Agent node on SQL Server management Studio and locate the BizTalk BackUp Job. Go to Steps
Basically, this how the backup process creates the Files:
This is the step we need to focus on.
Now, Click on Edit button to see what is running in this step.
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.
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.
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
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
exec sp_configure 'xp_cmdshell', 1 -–Activates the xp_cmdShell Execution
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.
Run the following T-SQL query to create the procedure.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryCustom] @DaysToKeep smallint = null
DECLARE @BackupFileName varchar(255), @BackupFileLocation varchar(255)
DECLARE @CmdCommand varchar (300)
set nocount on
IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
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
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 )
FETCH NEXT FROM Backup_cursor INTO @BackupFileName, @BackupFileLocation
WHILE (@@FETCH_STATUS <> -1)
IF (@@FETCH_STATUS <> -2)
SET @CmdCommand = 'del ' + @BackupFileLocation + '\' + @BackupFileName
EXEC master..xp_cmdShell @CmdCommand -- Deleting the files
--Historic deletion logic
The final step will be to edit the Clear Backup History step by calling the already created stored procedure sp_DeleteBackupHistoryCustom
That’s all! :-)
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"?
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.