As anyone who has fully implemented BizTalk will know, if you do not backup your databases you will have problems.
I can not stress more highly, please back up your BiTalk databases.
The next thing you will notice is that its not that simple, there is a Backup Job that ships with the product, that creates backups, you NEED to use this job to create your backups or you will not be supported.
The job will create a full backup daily, and a transactional log backup every 15 minutes. (This is configurable)
At the end of the day, you end up with quite a lot of files, that take up quite a lot of disk space depending on how active your solution has been.
In reality you should be archiving these backups daily, and cleaning up the backup directory. But what if your backup directory is on the SAN and its backuped up, but there are several days worth of backup still lying around…
This happens more often than you would think, so we have 100’s of these backup files filling up the disk, which gets critical when the disk fills up.
So how do I clean up these files automatically.......
I found the following script on the net, which have I modified slightly, appologies to the person who first wrote it, I forget where I sourced it from, however it is a fine script.
This works for BizTalk 2004 and BizTalk 2006.
It usesmaster.dbo.xp_cmdshell, which in SQL 2005, is disabled by default. Please don’t just go and enable it, be sure to set it up correctly.
Set the user who has access to this, as a limted access user with permissions only to where you would like them to have permissions to:
EXEC sp_xp_cmdshell_proxy_account ‘[userid]’, ‘[password]’
Under the surface area configuration tool of sql 2005, located under configuration for features, check the box for xp_cmdshell.
The script it self is rather simple, I generally keep as many days as I feel I need, 3 is my default, it will clean up and delete the physical backup files for you. Neat.. Create it in the BizTalkMgmtDb.
You need to set up a NEW sql agent job for this, do not modify the existing BizTalk backup job, as you will not be supported.
Create a new job that has a single step pointing at the script below, it only needs to run once every day or so, and your problem of backup files filling up your file system will be resolved.
Sample step: exec [dbo].[sp_DeleteBackupHistoryAndFiles] 3
See at the end for the script to create this job.
/*
Procedure to delete database backup files for BizTalk 2004/06
Note: Uses master.dbo.xp_cmdshell
Modified by Paul Somers (paul@somers.com)
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
*/
CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
AS
BEGIN
set nocount on
IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
RETURN
DECLARE DeleteBackupFiles CURSOR
FOR SELECT '
FROM [adm_BackupHistory]
WHERE datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep
AND [BackupSetId] NOT IN
( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [history2]
WHERE [history2].[BackupSetId] = [BackupSetId] AND datediff( dd, [history2].[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
GO
/*
Script to Create the Clean up BizTalk Backup Job
Paul Somers (paul@somers.com)
*/
USE [msdb]
GO
/****** Object: Job [Clean up BizTalk Backups] Script Date: 07/19/2006 09:57:39 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 07/19/2006 09:57:39 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Clean up BizTalk Backups',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [CleanupBackupFiles] Script Date: 07/19/2006 09:57:39 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CleanupBackupFiles',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec [dbo].[sp_DeleteBackupHistoryAndFiles] 3',
@database_name=N'BizTalkMgmtDb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20060719,
@active_end_date=99991231,
@active_start_time=100000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
