Backup your BizTalk360 database via the BizTalk Backup job

Published on : Apr 16, 2019

Category : BizTalk360 Update

Lex

Author

One of the components of BizTalk360 is a SQL Server database. This database is used for all kind of configuration like user permissions and all the monitoring settings. When that data got corrupted or lost, you would have to do all the configuration all over from scratch. To prevent this from happening, you should frequently take backups of that database. Besides creating manual backups, you can also have these backups being created automatically.

There are two different approaches to making automated backups of this database, both are shown below:

  • Create a SQL Server Maintenance Plan
  • Extend the Backup BizTalk Server job

The difference between these 2 methods is, that with the Maintenance Plan approach you’ll have a backup which is not in sync with the backup of your BizTalk databases, while with the latter option your BizTalk360 backup will be in sync with the BizTalk backups. This could make restoring your databases in one go easier.

In a previous post, Rochelle has already explained how to create a Maintenance Plan to take care of the BizTalk360 database backups. In this article, we’ll explain how to add the BizTalk360 database to the Backup BizTalk Server job.

Adding custom databases to the BizTalk Server Backup job

What we basically are going to do is using a feature from BizTalk Server. As you probably are aware of, BizTalk Server contains multiple databases and to be able to restore them in sync, the backup needs to be created in sync. The only by Microsoft supported way to create such backups, is by using the Backup BizTalk Server job, which is a SQL Server Agent job. You can read more about that topic in the below articles:

BizTalk users can extend the backup job with other databases which are considered important to the integrations which are deployed in BizTalk Server. This is exactly what we will be doing with the BizTalk360 database.

The process exists of the following steps:

  1. Prepare the BizTalk360 database
  2. Add the BizTalk360 database to the BizTalk Server backup job
  3. Start making backups

Let’s take these steps one by one and have that database added to the BizTalk Server backup job!

Prepare the BizTalk360 database

In this first step, we’ll make sure a table and some Stored Procedures will be created in the BizTalk360 database. The table which becomes created is called MarkLog. You will find this table in all the databases which are being backed up via the BizTalk Server backup job.

Perform below steps to create that table and the needed Stored Procedures:

  1. Open SQL Server Management Studio and connect to the SQL Server instance which contains the BizTalk360 database
     

  2. Click Open File, navigate to folder C:\Program Files (x86)\Microsoft BizTalk Server 2016\Schema and select query Backup_Setup_All_Tables.sql
  3. From the Databases dropdown, select the BizTalk360 database

     


  4. Click the Execute button or hit F5 to execute the script. If the database has been created successfully, you can proceed with the next step
  5. Click Open File, navigate to folder C:\Program Files (x86)\Microsoft BizTalk Server 2016\Schema and select query Backup_Setup_All_Procs.sql
  6. If not yet selected, select the BizTalk360 database from the Databases dropdown
  7. Click the Execute button or hit F5 to execute the script

If both SQL scripts have been executed successfully, an important part of the configuration has been completed. The BizTalk360 database is ready and in the next step, it will be added to the BizTalk Server backup job!

Important: Ensure yourself that the BizTalk360 is in Full Recovery Model, otherwise the backup will fail! You can check this by:

  1. Right-click the database then select Properties
  2. Select Options
  3. Check if the Recovery Model is set to Full
     
     

Add the BizTalk360 database to the BizTalk Server backup job

In the previous step, we prepared the BizTalk360 database to be able to be backed up by the BizTalk Server backup job. In this step, we’ll make sure that that database becomes added to a table in BizTalk Server’s management database, which will make sure that the database will be picked up by the BizTalk Server backup job.

Follow below steps, to make sure that the BizTalk360 database will be picked up by the BizTalk Server backup job:

  1. In SQL Server Management Studio, connect to the SQL Server instance which contains the BizTalkMgmtDb
  2. In the Object Explorer, expand the Databases, BizTalkMgmtDb, Tables and find the dbo.adm_OtherBackupDatabases table

     


  3. Right-click that table and, from the menu that appears, select Edit Top 200 Rows. As you are in Edit mode, you can add a new row which will contain the information about the BizTalk360 database.

     


  4. In the last row, which now only shows NULL values, enter below values
    • DefaultDatabase: BizTalk360
    • DatabaseName: BizTalk360
    • ServerName: <Name of the SQL Server Instance which contains the BizTalk360 database>
    • BTSServerName: <Name of the SQL Server Instance which contains the BizTalk360 database>
  5. Hit Enter to save the record in the table

The BizTalk360 database is now part of the BizTalk Server backup job. The last step we need to do is forcing a full backup, to make sure that also incremental backups can be created.

Start making backups

We are almost there; we have seen how the BizTalk360 database has been prepared to accommodate the BizTalk Server backup job. In the previous step, we have added the BizTalk360 database to the BizTalk Server backup job. In this last step, we will force a full backup, to make sure that after that, also incremental backups can be created. By default, a full backup will be created once every 24 hours; Incremental backups will be created, by default, every 15 minutes.

Perform below steps to force a full backup:

  1. In SQL Server Management Studio, under the BizTalkMgmtDb, expand Programmability and expand Stored Procedures
  2. Scroll through the Stored Procedures until you have found sp_ForceFullBackup

     


  3. Right-click that Stored Procedure and, from the menu that appears, select Execute Stored Procedure… As the Stored Procedures doesn’t need any parameter values, just click OK
  4. If the Stored Procedure has been executed successfully, the next time the BizTalk Server backup job runs, it will perform a full backup

There are a couple of ways to check whether the backups are really being created. You can:

  • Check the output of the BizTalk Server backup job (in SQL Server Management Studio)
  • Check if the backup files have been created (in Windows Explorer)

Check the output of the BizTalk Server backup job

To perform this check, perform these steps:

  • In SQL Server Management Studio, you need to navigate to the SQL Server instance which contains the BizTalk Server backup job
  • Next, expand SQL Server Agent
  • Right-click the Backup BizTalk Server job and select View History

 

Check if the backup files have been created

To check the availability of the backup files, you firstly need to check where these files are located. Follow the below steps, to find that location and then check the actual location:

  • In SQL Server Management Studio, you need to navigate to the SQL Server instance which contains the BizTalk Server backup job
  • Next, expand SQL Server Agent
  • Double-click the Backup BizTalk Server job
  • In the Job Properties dialog which appears, under Select a Page, select Steps

     


  • Now, under Job step list, double click BackupFull
  • In the Job Step Properties dialog, at Command, scroll to the right to find the backup path

     


  • Copy the backup path and close all dialog screens
  • Next, open a Windows Explorer and paste the backup path in the Address bar

     

Now the backup files should show. Although we only checked the backup path for the full backup files, this folder might also contain the backup files of the incremental backups.

Conclusion

The BizTalk360 database contains valuable information about, amongst others, your monitoring configuration and the people who have access to BizTalk360. If in case of a disaster, you need to easily restore a backup of your BizTalk360 database, you need to have a recent backup of that database. The BizTalk Server backup job creates such backups. In this article, we have seen how to extend the BizTalk Server backup job to incorporate the backup of the BizTalk360 database.