Automatic generation of BizTalk DB restore SQL

When backing up the BizTalk databases you need to use BizTalk backup job for that since creating a regular maintenance task is not enough. The reason is that the BizTalk mechanism ensures that all the DBs BizTalk needs are backed up in a way that can be later restored to a synchronized point, this is done by entering a transaction marker to all the DBs before the backup. This marker will appear in their log. When performing the restore action, all the DBs should be restored up to that marker.

The steps for performing a restore of a DB to a marker are:

• Restore the last full backup.
• Restore all the log backups since the full backup except the last.
• Restore the last log backup up to the marker

If you have applicative DBs that you need to be synchronized with BizTalk it is possible to add them to use the same mechanism so they will be backed up by the BizTalk backup job (see instruction how to here).

When  performing a disaster recovery restore you need to perform the restore steps to all the DBs so they will all be restored to the same point and be synchronized, otherwise there is a risk BizTalk will fail to operate correctly.

There are detailed instructions how to perform such a restore in the BizTalk documentation  but this is not an easy task. For example a common configuration of the backup job is to run a full backup once a week and do a log backup a few times a day. This means that in the case of a disaster you might end up having to restore over 50 files which is a lot. It is important you restore them in the right order and you have to figure out which files need to be fully restored and which restored just up to the marker.

I doubt if during a crisis anybody can remembers this complex logic or will have the time to read the documentation to learn how to do it properly. Also if there is somebody who is an expert with the procedure there are so many steps to perform that if done manually the chances for a mistake are too high.

This is why I decided to generate the restore script automatically as part of the backup process. I added a step to the backup job that creates the SQL script for restoring the DBs. This way each backup run creates the SQL script needed for restoring the DBs that were backed up and to that specific point in time (= the marker point created on that run).

To write the SQL to a file I used the job step 'output' option. The step has an SQL which returns the lines needed for the restore. I do this by creating a temp table with one column and insert into it the SQL lines I need. I then perform a select on that table which is outputted by the step to a file.

To add the step perform the following:

• Add a new step to the BizTalk backup job (Backup BizTalk Server...) and name it "Create Restore Script"
• Set the step type to T-SQL
• Set the Database to your BizTalk management DB (default is BizTalkMgmtDb) 
• Enter as the SQL command this script. 
• Switch to the advanced tab and set in the output file where you want the script to be written. I recommend it will be placed in the same directory as the log backup files.
• Check the "Append output to existing file". This will make sure you don't override the file and so keep also the script of the previous runs so it will be possible to restore your environment back also to them.
• Go to step 3 (the last in the original job) and change in the advance tab so on success it will continue to the next step.

I have also prepared a script which adds the step to the job. Before you run it you should do a 'replace text' in the file to all the places where written %RestoreDataDir% with the directory you want your script file to be written to (for example D:\DBRestoreData). You can also do that after you run the script by opening the new step configuration and changing the output file location in the advanced tab configuration.

I don't wish anyone to ever have to use the generated restore script but if the need comes I hope this will help restoring BizTalk a bit easier.