PowerShell cmdlet for BizTalk db restore

Listen with webReader
Published 22 February 11 08:59 AM | wmmihaa

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.

image

The Get-Mark cmdlet queries the backup output folder to retrieve all marks. The mark is part of the name of each backup file:

image

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

image

Downloads 

HTH

Comments

# Jan Odegard said on March 23, 2011 03:48 PM:

Nice work Mikael. We found this quite useful when trying to resolve a few issues in our BAM infrastructure.

A couple of suggestions that I found necessary to implement:

- Changing the parsing of file names in Get-Marks to use LastIndexOf(name) instead of IndexOf(name) - turned out we had "BTS" in our file path :)

- Handling backup files created by the DTA Purge and Archive job. By default these files have a different date format and will cause this script to fail.

Thanks!

# wmmihaa said on March 27, 2011 11:40 AM:

Let me know if you've made any changes, and I'd be happy to post them.

Thanks

# Tareq Ali said on March 29, 2011 11:48 AM:

That is great work, I tried it and it saved us a lot of time.

# Alvaro said on December 15, 2011 11:25 PM:

Great work, many thanks!

This Blog

News

    MVP - Microsoft Most Valuable Professional BizTalk User Group Sweden BizTalk blogdoc

    Follow me on Twitter Meet me at TechEd

    Visitors

    Locations of visitors to this page

    Disclaimer

    The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

Syndication