Generating restore scripts for BizTalk Server – Version 4

I have created a new version of my PowerShell script to generate restore scripts for BizTalk Server. The basic idea is the same and you can read more about it in my previous post here

I have rewritten the script and done some changes.

Download the script with some fixes for failed backups in the chain and secondary replica not readable RestoreScriptForBizTalkJob.4.1.0 

Support for Availability groups

With the release of BizTalk Server 2016 we can use SQL Server availability groups. The previous version of the script didn’t work with availability groups. I have added a function that checks if the running server is the primary replica or not in an availability group to return true and false if it is a secondary replica. The script just exits if it is a secondary replica.

Script parameters

The new script is intended to be called from the job step using parameters to the file.

Parameter Default value Comment
ServerName Name of the SQL Server including instance containing BizTalk Management database i.e “Server01\myinstance”.

Note: If you use the listener name in an AG you will need to add the instance name.


MgmtDb BizTalkMgmtDb Name of BizTalk Management database

Not mandatory

SqlScriptDestination NULL By default, the script stores the scripts in the full backup folder, use this parameter to redirect to another folder.

Not mandatory


Removed loading SQL Server PowerShell provider

The script does not load the SQL Server PowerShell provider, the reason is that I just wanted the script to be a bit cleaner.

Check if database exists

This version checks if the database exists before setting it to single user mode to avoid unnecessary error messages while recovering.

Tested scenarios

This version of the script has been tested with:

  • BizTalk 2016 using SQL Server 2016 with Availability groups
  • BizTalk 2016 using SQL Server 2016
  • BizTalk Server 2013 R2 using SQL Server 2014

I have tested both generation of the scripts and recovery.


When you recover BizTalk server databases it is all or nothing. All the databases for the group that are included in the backup job must be restored together and to the same transaction mark.

While recovering, ensure that you have stopped all activity in your BizTalk group.

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s