BizTalk Server tips and tricks for Administrators: How to back up RosettaNet, ESB or UDDI databases

Have you had the time to think about what some features like RosettaNet, ESB, or UDDI have in common?

Well, all of them have custom databases, and all of them are optional features.

But the most important question here, because they have custom databases, is: Do you think that these databases are being backed up? And the data inside is saved?

And the response is NO… Because these “custom” databases are not installed 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. So if you want the Backup BizTalk Server job to back up RosettaNet, ESB, or UDDI custom databases, you must manually add the databases to the Backup BizTalk Server job.

Fortunately for us, Microsoft provides two SQL Scripts:

• Backup_Setup_All_Procs.sql
• Backup_Setup_All_Tables.sql

That can be found in the Schema folder inside the BizTalk installation folder: C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Schema, which you need to run against these databases in order to extend the standard backup mechanisms. However, you also need to modify the adm_OtherBackupDatabases table (present in the BizTalk Management (BizTalkMgmtDb) database) to include a row for each of these custom databases.

How to Back Up Custom Databases (RosettaNet, ESB, or UDDI databases)

Note: I will not advise you to back up any custom application databases (databases used to support BizTalk Application processes or others) with the Backup BizTalk Server job, with the exception of custom BizTalk databases, like RosettaNet, UDDI, or ESB.

To accomplish that, you need to:

• Browse to the C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Schema directory, and then run against the RosettaNet, ESB, or UDDI (custom) databases the following SQL scripts.
  • Backup_Setup_All_Procs.sql
  • and Backup_Setup_All_Tables.sql

• So, in this case, we need to run these two SQL scripts against the following databases
  • RosettaNet
    • BTARNARCHIVE
    • BTARNCONFIG
    • BTARNDATA
  • ESB Toolkit
    • EsbExceptionDb
    • EsbItineraryDb
  • UDDI
    • UDDI3

Note: This creates the necessary procedures, tables, roles, and assigns permissions to the stored procedures.

• Modify the adm_OtherBackupDatabases table, present in the BizTalk Management (BizTalkMgmtDb) database, to include a row for each of your custom databases
  • Type the new server and database names in the corresponding columns
    • 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.

The next time you run the Backup BizTalk Server job, it will back up your custom databases.

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.