Restoring BizTalk Server 2010 databases

    When you look at Microsoft’s documentation about restoring your BizTalk databases they always talk about restoring your databases by using log shipping. Most of my client have various reasons (unfamiliarity with the technique, requires extra servers and licenses, etc.) for not wanting to use log shipping. Alternatively you can manually restore the BizTalk databases.


    My concern with manually restoring the databases is that it can take a lot of time and is prone to errors. By default the Backup BizTalk Server job creates a full backup once a day and a log backup every 15 minutes. Worst case scenario you will have to restore one full backup and (24 * 4) 96 logs times at least 5 databases. That means you will have to do 485 restore actions to restore the BizTalk databases to the latest state!


    One way of tackling this problem is to decrease the number of log backups made to for example once every two hours. This however means that you can lose two hours of data. Furthermore in high-volume scenario’s this means that the log file size can become very large. So reducing the number of log backups is not always a good solution.


    Since doing too much manual work is not my cup of tea, I wrote a PowerShell script that creates the SQL queries for me. Basically this script looks in the backup folder a creates all the SQL queries (in the correct syntax and the correct order) necessary to restore the databases. To get the script to work you will have to edit the following variables in the script:


    [STRING]$SQLServerName = "BizTalk2010DEV"
    [STRING]$InstanceName = "MSSQLSERVER" #Use MSSQLSERVER if you are using a default instance
    [ARRAY]$Databases = @("SSODB", "BAMPrimaryImport", "BizTalkDTADb", "BizTalkMsgBoxDb", "BizTalkRuleEngineDb", "BizTalkMgmtDb")
    [STRING]$FullBackupLocation = "C:\Backup\SQL_Server\Data"
    [STRING]$LogBackupLocation = "C:\Backup\SQL_Server\Log"
    [STRING]$SQLOutputLocation = "C:\temp"


    The $SQLServerName is the name of the SQL Server or SQL Server cluster name you are restoring to. The $InstanceName must contain the name of the instance. Use MSSQLServer for default instances. The $Databases must contain all of the databases that you want to restore. The $FullBackupLocation must contain the path of the folder that contains all the full backup files. The $LogBackupLocation must contain the path of the folder that contains all the log backup files. The $FullBackupLocation and the $LogBackupLocation can be the same location. The $SQLOutputLocation is the location where the script writes the SQL output file to.


    The script creates a SQL file containing all the queries needed to restore the databases. You can open the SQL file in SQL Server Management Studio and execute the query.


    I tested the script several times on different environments and it worked great. Just on word of advice, if you want to use this script as part of your backup and restore plan, please test it before you include it in your plan!

    27 March 2013