If BizTalk Server is being installed as part of the environment build, a separate backup job, specifically for BizTalk will need to be configured after the BizTalk specialists have deployed the software.

Firstly, identify a suiteable Windows user account (usually a domain service account) for running the backup job. Craete a SQL Server login for the account (unless it already exists) and then assign it the BTS_BACKUP_USERS role either via the GUI or the following T-SQL:

— Assign backup role to nominated user

USE [BizTalkDTADb]

 

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘Domain\BizTalk_SVC_BKP’

USE [BizTalkMgmtDb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘Domain\BizTalk_SVC_BKP’

USE [BizTalkMsgBoxDb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘Domain\BizTalk_SVC_BKP’

USE [BizTalkRuleEngineDb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘Domain\BizTalk_SVC_BKP’

USE [SSODB]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘Domain\BizTalk_SVC_BKP’

Once that’s done, navigate to “SQL Server Agent” in SSMS, expand the “Jobs” folder. Right click on the “Backup BizTalk Server (BizTalkMgmtDb)” job that’s been created, and click on “Properties”. Update the “Owner” of the job to the user chosen above.

 

Click on the “Steps” page from the menu on the left, highlight the “BackupFull” step and Edit.

 

Update the “Command” string so that the destination path reflects an appropriate directory for hosting the BizTalk backup files. It’s usually a good idea to use the same disk as the other database backups, but a different parent directory so that any existing maintenance plans with “Maintenance Cleanup Tasks” do not interfere with purging of the BizTalk backup files.

As an example, the following directory is used here: \MSSQL\BizTalk_Backups

 

Repeat the set above for the “MarkAndBackupLog” step of the job.

 

Update the “Clear Backup History” step with an appropriate retention period for the backup history. The default of 14 days is usually acceptable, but given that we usually only keep 2 days’ worth of backups online, this can be reflected here also.

 

NOTE: The above step does not purge the physical backup files. An additional step (below) will be added to take care of this.

For the same step, click on the “Advanced” option and update the “On success action” to “Go to the next step”. Then change the “On failure action” to “Quit the job reporting failure”.

 

From the “Job step list” page, click “New”.

 

Create a new step with the following properties, updating the path, server name, and hour’s retention as appropriate to your requirements:

Step Name: Delete Old Backups

Type: PowerShell

Run as: SQL Server Agent Service Account

Command:

$path = ‘\MSSQL\BizTalk_Backups

$filter = ‘SNAPSQL01*.bak’

$retention= (Get-Date).AddHours(-48)

Get-ChildItem (Join-Path $path $filter) -Recurse |? {($_.PSIsContainer -eq $false) -and ($_.LastWriteTime -lt $retention)} | Remove-Item

 

Click on the “Advanced” page. Update the “On success action” to “Quit the job reporting success”. Then change the “On failure action” to “Quit the job reporting failure”.

 

Finally, check the “Job History” after around 30 minutes to ensure the job is completing successfully, and every 15 minutes (default).


Further information around configuration of the BizTalk Backup job can be found here in the MSDN article here:

https://msdn.microsoft.com/en-us/library/aa546765.aspx