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. Mandatory |
|
| 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.
Reminders
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.
Crumbs on the integration table 