The BizTalk backup process

A BizTalk group’s data consists of a number of databases with data moving between them. To keep a consistent state when taking a backup, the job uses a marked transaction. A description of that process can be found here. Every time the backup job runs some rows are added to the adm_BackupHistory table in the BizTalkMgmtDb database containing information like the MarkName and files. This information is used by BizTalk Log Shipping to keep track of backups.

BizTalk Log Shipping

BizTalk Log Shipping is the only fully supported way of disaster recovery. When backups are taken SQL Server jobs will restore them to a passive server. You can find a description of BizTalk Log Shipping here and how to configure it here. There might be situations when you need to manually restore the databases. This process is both tedious and error prone, especially during a critical situation.

The task explained

You have a number of backup files of data and logs and need to restore manually. When restoring the last transaction logs, you must use the STOPATMARK argument to keep the databases in a consistent state. What can we do to be prepared in this situation? Having a script prepared to restore is a good way. The best moment to generate the script is directly after we have taken the backups. The idea is to add an extra step to the backup job that generates a restore script based on the information in the adm_BackupHistory table, using the same information as the BizTalk Log Shipping.

Note: You should never change the logic of the BizTalk Server jobs; in this case we’re augmenting the process without changing what the backup job does.

The script

How it works

What do the script need to do to create the restore script?

• Get last mark name from adm_BackupHistory
• Get last full backup file information from adm_BackupHistory
• Loop all databases
  • Generate full database restore script for the current database
  • Get log file information for the current database from adm_BackupHistory
  • Generate log restore script for the current database using with no recovery for all except for the last one which have the current mark. For the file with the current mark generate script using STOPATMARK.

Configure the script

In the top of the script you have three variables that might need to be updated.

Variable	Comment
$ServerName	Database Server name where BizTalk management database is located. If it is located on a named instance the instance name must be provided.
$MgmtDb	The database name for the BizTalk management database.
$SqlScriptDestination	Default set to $null, then the generated scripts will be stored in the same location as the full backups. Set a path if you want the scripts saved on a different location.

Getting the script in place

1. Add an extra step to the backup job of PowerShell type. Add your version of the script in the command field or the full path to the PowerShell script.
Note: If you have implemented Log Shipping you must use a file since the script is longer than the 3200 characters that the Log Shipping process will copy.

2. Change the ”Clear Backup History” step to go to next step on success. Also set the new step to quit responding success or failure.

The resulting SQL scripts

For each backup a new set of script files is created, one file for each database instance that is included in the environment. The naming is “Server_Instance” (Green rectangle in the picture), “Markname of starting full backup” (Blue) and then the “last mark” (Red).

Each database will be set to single user mode and close all open connections. All the databases in the backup have a restore database using with move in the expression so you have the possibility to change the location of the database files. Every restore of logs have NORECOVERY except the last one that use STOPATMARK to stop at the transaction mark.

Restore process

When you are in the process of restoring and have an environment with no activity, I.e. all BizTalk Services and SQL Server agent turned off, you can run the scripts on the database instances where you want to restore. The scripts can be edited to have a different file location if needed. If you’re moving databases to another server don’t forget the normal procedure running the UpdateDatabase.vbs script with and updated SampleUpdateinfo.xml file.

The script is licensed “as-is.” You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. Test it in safe environment and change it to fit your requirements.
Download the code and description 

Note:
This version only supports writing the full path to the script in the jobstep.
This version don’t work with Availability groups, a new post is coming soon.