BizTalk Server Tips and Tricks: How to Backup (other) BizTalk Custom Databases

During my sessions about BizTalk Server Tips and Tricks, I normally ask: What RosettaNet, ESB or UDDI have in common? And the answer is: they all are BizTalk optional features that are not part of the primary installation process, you need to execute “secondary” installation processes to add theses features. These installation processes will create BizTalk custom databases for supporting all of these new optional features. But the big questions here are: do you think that these databases are being backed up? And if not, how to backup (other) BizTalk Custom Databases?

Do you think that these databases are being backed up?

To respond this first question, the answer is: No! Because these BizTalk custom databases (we are calling “custom databases” because they are supporting optional features that are not part of the primary installation process) are not installed by default with BizTalk Server, they are not included in the default list of databases to be marked and backed up by the Backup BizTalk Server job. The default list of databases that are, normally, being backed up by the Backup BizTalk Server job are:

• BAMAlertsApplication
• BAMPrimaryImport
• BizTalkDTADb
• BizTalkMgmtDb
• BizTalkMsgBoxDb
• BizTalkRuleEngineDb
• SSODB

How to Backup (other) BizTalk Custom Databases?

If you want the Backup BizTalk Server job to back up these additional BizTalk custom databases, you must manually add the databases to the Backup BizTalk Server job. You can achieve this by:

• Taking Windows Explorer and browse to the “Schema” directory on the BizTalk installation folder, normally:
  • C:\Program Files (x86)\Microsoft BizTalk Server <version>\Schema
• Run “Backup_Setup_All_Tables.sql” and next “Backup_Setup_All_Procs.sql” against all your BizTalk custom databases that you want to back up. This creates the necessary table, procedures,  roles and assigns permissions to the stored procedures.
• After that you need need to modify the adm_OtherBackupDatabases table, in the BizTalk Management (BizTalkMgmtDb) database, to include a row for each of the new BizTalk custom databases
  • Type the new server and database names in the corresponding columns, as shown in the following tab
    • DefaultDatabaseName: The friendly name of your custom database.
    • DatabaseName: The name of your custom database.
    • ServerName: The name of the computer running SQL Server.
    • BTSServerName: The name of the BizTalk Server. This value is not used, but it must contain a value nonetheless.

To complete the process, you, mandatory, need to force Backup BizTalk Server (BizTalkMgmtDb) job to perform a full backup of the databases, otherwise you will receive the following error:

• BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013)

To do that you need:

• Execute the “sp_ForceFullBackup” stored procedure present in the BizTalkMgmtDb database.

The next time you run the Backup BizTalk Server job, it will back up all your BizTalk custom databases. Note: I will not recommend you to add any of your application support custom databases to the Backup BizTalk Server job since they may interfere with the execution times of this job. If the Backup BizTalk Server job starts to take a long time to execute, it may also affect the overall performance of the BizTalk platform. Stay tuned for new BizTalk Server Tips and Tricks! Check out the first blog of the series BizTalk Server Tips and Tricks: Enabling BAM Add-In for Excel 2016.