Configuring the backup job for BizTalk is a fairly simple task, while restoring it is a bit more complicated. By default the BizTalk backup job makes a full backup once a day, and a log backup every 15 minutes. When backups are done, a mark is set on each file. This mark is the same across all databases, and should be used to restore all databases to the same point in time and keeping all databases in a consistent state. -Also, by default, all backups are made to the same directory folder.
The only supported disaster recovery procedure from Microsoft is log-shipping. Nick Heppleston has gone through the trouble of describing this in great detail, and I strongly recommend to read these post before you choose to use any other approach.
A few weeks ago, I sent out a question on twitter, asking whether people used log-shipping or not. I got 24 responses where only 4 used log-shipping.
Although log-shipping comes with many advantages, it is still expensive since it requires a secondary SQL cluster. Most of the people I asked confirmed this was the main reason why they had chosen other alternatives. Since BizTalk doesn’t come with any restore scripts/features other than log-shipping, everyone is left to fix this on their own.
If you’re in the same situation, feel free to download this sample. If it doesn’t fit your solution, it might at least be a good starting point.
The sample comes with two cmdlet’s: Get-Marks and New-RestoreDatabaseFromMark. The first one gives you a list of all marks from all log files. The second one, as the name implies, restores a database to a specific mark. When doing so, the database will be restored from the last full backup before the mark. After that, all log files will be restored in order from the full backup. The last log file will only be restored to the specified mark.
The Get-Mark cmdlet queries the backup output folder to retrieve all marks. The mark is part of the name of each backup file:
Each file is made up of the following parts:
[Server]_[Instance*]_[Database]_[Full|Log]_[Mark]
Eg: SERVER001_DTA_BizTalkDTADb_Log_BTS_2011_01_18_12_06_50_22.bak
* The instance is only present for none default instances.
You can use the New-RestoreDatabaseFromMark cmdlet with or without specifying the mark. Leaving the mark empty is eqvivilent to last mark.
The New-RestoreDatabaseFromMark cmdlet is called per database, why it's easier to create a script for restoring all databases together. The sample comes with a RestoreScript.ps1 script file, which could work as a good start:
$backupPath = "X:\BizTalkBackUp";
$dataPath = "E:\SQL Server 2008\MSSQL10.MSSQLSERVER\MSSQL\DATA";
$logPath = "E:\SQL Server 2008\MSSQL10.MSSQLSERVER\MSSQL\DATA";
$mark = Read-Host "Specify mark (use the Get-Marks cmdlet to get all marks or blank to use last mark)";
if ($mark.Length -eq 0)
{
Write-Output "Restoring to last mark...";
$mark="";
}
New-RestoreDatabaseFromMark SSODB $backupPath $dataPath $logPath $mark;
New-RestoreDatabaseFromMark BAMPrimaryImport $backupPath $dataPath $logPath $mark;
New-RestoreDatabaseFromMark BizTalkDTADb $backupPath $dataPath $logPath $mark;
New-RestoreDatabaseFromMark BizTalkMgmtDb $backupPath $dataPath $logPath $mark;
New-RestoreDatabaseFromMark BizTalkMsgBoxDb $backupPath $dataPath $logPath $mark;
New-RestoreDatabaseFromMark BizTalkRuleEngineDb $backupPath $dataPath $logPath $mark;
trap [Exception]
{
Exit;
}
Write-Output "Done restoring all BizTalk databases" -foregroundcolor "yellow";
The first section of the script defines a set of path variables. In my simple sample, all database files are located in the same folder. This is never a good practice, why you probably have different paths for data- and log files for each database.
To use the sample, open PowerShell and navigate to the sample output folder. Eg:
PS C:\> CD “c:\Program Files\bLogical.BizTalkManagement”
Before you can use the cmdlets, you need to install them. You can do this using the install script:
PS C:\Program Files\bLogical.BizTalkManagement> Install.ps1
After you’ve installed the snapins, you can start using the commands:
PS C:\Program Files\bLogical.BizTalkManagement> Get-Mark "X:\BizTalkBackUp"
or
PS C:\Program Files\bLogical.BizTalkManagement> MyRestoreScript.ps1
Downloads
HTH