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 is 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 are 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”, that you need to run against these databases in other 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 these custom databases.

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

Note: I will not advise you to backup 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 database, 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.

See this and more tips here: BizTalk Server Tips & Tricks for Developers and Admins (Deep Dive)