The health of BizTalk Server databases is very important for a successful messaging environment. BizTalk ships with 13 SQL Agent jobs that perform important functions to keep your servers operational and healthy.
Like any other system, all BizTalk Server databases should be backed up, and BizTalk Server provides an out-of-the-box job for accomplishing that: the Backup BizTalk Server (BizTalkMgmtDb) job.
This job makes both Full and Log backups. By default, the Backup BizTalk Server job performs a full backup once a day and performs log backups every 15 minutes. This means that once the full backup is performed, you need to wait 24 hours for it to automatically run another full backup of the BizTalk Server databases… even if you try to run the job manually, it will only create backups of the log files.
But sometimes we need, for several reasons, to have the ability and the possibility to force a full backup:
- We will have some maintenance planned on the server or apply a new configuration, and we want to back up the environment.
- Or, simply, we will install a new integration application, and again, we want to have a backup at this exact moment.
Each company has its policies, so again, for several reasons, we sometimes need to force a full backup of all BizTalk Server databases.
📝 One-Minute Brief
BizTalk Server relies heavily on SQL Server databases, making proper backups essential to platform stability. The built-in Backup BizTalk Server (BizTalkMgmtDb) SQL Agent job performs daily full backups and frequent log backups, but administrators sometimes need an immediate full backup before maintenance or deployment. The standard stored procedure sp_ForceFullBackup only flags the next run of the backup job to perform a full backup — it does not execute one instantly. This post presents a safer operational workaround: a custom SQL Agent job that first triggers the full-backup flag and then immediately runs the official BizTalk backup job, ensuring a supported full backup at the exact required moment.
The standard way is to use the BizTalkMgmtDb.dbo.sp_ForceFullBackup stored procedure. However, unlike what many people think, this stored procedure does not perform a full backup; it only marks the BizTalk Server databases internally so that the next time the Backup BizTalk Server job runs, it will need to perform a full backup of the data and log files.
All BizTalk Admins know this (if they don’t know, they should!); however, sometimes we need to rely on DBA Admins to perform this task, and do not misunderstand me, I have nothing against DBA! The BizTalk Administration team should be composed of several elements with different skills: DBA Admins, network admins, Sys Admins, and a BizTalk Admin… each playing its role. But sometimes DBAs don’t really understand the BizTalk infrastructure, and the BizTalk Administrator should have an important role in advising and helping them (like in other situations, it will be the opposite).
And because this is not a day-by-day task, people tend to forget all the steps, and sometimes they will create custom scripts to perform this task, but you really need to be aware of two important things:
- The Backup BizTalk Server job is the only supported method for backing up the BizTalk Server databases. Use of SQL Server jobs to back up the BizTalk Server databases in a production environment is not supported.
- You can use the SQL Server methods to back up the BizTalk Server databases only if the SQL Server service is stopped and if all BizTalk Server processes are stopped.
So, to help one of the DBA teams, I end up creating this job that is composed of two steps:
- Step 1: Force Full Backup
- That will call the BizTalkMgmtDb.dbo.sp_ForceFullBackup stored procedure.
- Step 2: Backup BizTalk Server
- That will call the standard Backup BizTalk Server (BizTalkMgmtDb) job.
Note: Of course this custom job should be disabled and manually executed when you need it!
Hope you find it useful.
Download
THIS SQL SCRIPT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.
You can download the Force Full Backup BizTalk Server (BizTalkMgmtDb) Job from GitHub here:
Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son.
