Script to update a BizTalk installation after the BizTalk databases have been moved to another database server. This script will work only for single server BizTalk groups without BAM and only when all databases are on the same server and instance.

MoveBizTalkDatabases.ps1

<#
    .SYNOPSIS 
      Script to update a BizTalk installation after the BizTalk databases have been moved to another database server.
    .DESCRIPTION
      Use this script to update a BizTalk installation (front-end and database) after BizTalk databases have been moved to another database server.
      The script is to be executed on the BizTalk front end server. 
	  This script will work only for single server BizTalk groups without BAM and only when all databases are on the same server and instance.
    .PARAMETER newDBServer 
	the new management database location
    .PARAMETER restorePath 
	the location of the BizTalk scripts UpdateDatabase.vbs and UpdateRegistry.vbs
    .EXAMPLE
      .\MoveBizTalkDatabases.ps1 -newDBServer databaseserver.somedomain.dom\someinstance
     This command update a BizTalk installation (front-end and database) after all BizTalk databases have been moved to databaseserver.somedomain.dom\someinstance
	 The command is to be executed on the BizTalk front end server. 
    .EXAMPLE
      .\MoveBizTalkDatabases.ps1 -newDBServer databaseserver.somedomain.dom\someinstance -restorePath c:\MySpecialBizTalkInstallFolder\Bins32\Schema\Restore
     This command update a BizTalk installation (front-end and database) after all BizTalk databases have been moved to databaseserver.somedomain.dom\someinstance
	 It uses a custom folder for the update scripts (UpdateDatabase.vbs and UpdateRegistry.vbs) 
	 The command is to be executed on the BizTalk front end server.
	 .LINK
	 http://msdn.microsoft.com/en-us/library/aa559835(BTS.20).aspx 
	 .LINK
	 http://msdn.microsoft.com/en-us/library/aa546753(v=bts.20).aspx 

#>
param (
        [parameter(Mandatory = $true,HelpMessage="Please enter the new management  database location like DEVDB26-DK1.sys.dom\DEV02")][string] $newDBServer,
        [parameter(Mandatory = $false)][string] $restorePath
    )
Push-Location

$ErrorActionPreference = "Stop"
$regKey = "hklm:\SOFTWARE\Microsoft\BizTalk Server\3.0"

function Create-UpdateInfoFile
{ 
param (
        [parameter(Mandatory = $true)][string] $newDBServer,
        [parameter(Mandatory = $true)][string] $oldDBServer
    )
@"
<UpdateConfiguration>
"@ 
"<MessageBoxDB oldDBName=`"$BizTalkMsgBoxDb`" oldDBServer=`"$oldDBServer`" newDBName=`"$BizTalkMsgBoxDb`" newDBServer=`"$newDBServer`" IsMaster=`"1`"/>"
"<TrackingDB oldDBName=`"$BizTalkDTADb`" oldDBServer=`"$oldDBServer`" newDBName=`"$BizTalkDTADb`" newDBServer=`"$newDBServer`"/>"
"<ManagementDB oldDBName=`"$BizTalkMgmtDb`" oldDBServer=`"$oldDBServer`" newDBName=`"$BizTalkMgmtDb`" newDBServer=`"$newDBServer`"/>"
if($BizTalkRuleEngineDb -ne '')
{
    "<RuleEngineDB oldDBName=`"$BizTalkRuleEngineDb`" oldDBServer=`"$oldDBServer`" newDBName=`"$BizTalkRuleEngineDb`" newDBServer=`"$newDBServer`"/>"
}
"	<OtherDatabases>"
"		<Database Name=`"SSO`" oldDBName=`"$SSODB`" oldDBServer=`"$oldDBServer`" newDBName=`"$SSODB`" newDBServer=`"$newDBServer`"/>"
@"
	</OtherDatabases>
</UpdateConfiguration>
"@ 
}

function Get-BizTalkInstallationInfo
{
    param (
        [parameter(Mandatory = $true)][string] $newDBServer,
        [parameter(Mandatory = $true)][string] $BizTalkMgmtDb,
        [parameter(Mandatory = $true)][string] $query
    )

    $connectionString = "Data Source=$newDBServer;Initial Catalog=$BizTalkMgmtDb;Integrated Security=SSPI;"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()

    $command = $connection.CreateCommand()

    $command.CommandText = $query
    
    
    $datareader = $command.ExecuteReader()
    $biztalkTable = new-object “System.Data.DataTable”
    $biztalkTable.Load($datareader)

    $connection.Close()

    return $biztalkTable
}

$regKey_Administration = $regKey + '\Administration'

$installPath = (Get-ItemProperty $regKey).InstallPath
$oldDBServer = (Get-ItemProperty $regKey_Administration).MgmtDBServer
$BizTalkMgmtDb = (Get-ItemProperty $regKey_Administration).MgmtDBName

if($oldDBServer -eq '' -or $BizTalkMgmtDb -eq '' -or $installPath -eq '')
{
    write-output "Couldn't get values below registry key $regKey and/or $regKey_Administration"
    
    write-output "installPath = $installPath"
    write-output "oldDBServer = $oldDBServer"
    write-output "BizTalkMgmtDb = $BizTalkMgmtDb"

    exit 
}

if($restorePath -eq '')
{
    $restorePath = $installPath +'\Bins32\Schema\Restore'
}

if((Test-Path  ($restorePath + '\UpdateDatabase.vbs')) -and (Test-Path  ($restorePath + '\UpdateRegistry.vbs')))
{
     Set-Location $restorePath
}
else
{
    write-output "Could not find UpdateDatabase.vbs or UpdateRegistry.vbs at $restorePath, please run the script again providing a valid -restorePath parameter"

    exit 
}

write-output "oldDBServer = $oldDBServer"
write-output "BizTalkMgmtDb = $BizTalkMgmtDb"

$admin_Group_dataTable = Get-BizTalkInstallationInfo -newDBServer $newDBServer -BizTalkMgmtDb $BizTalkMgmtDb -query 'SELECT * FROM [dbo].[adm_Group]'
$adm_OtherDatabases_dataTable = Get-BizTalkInstallationInfo -newDBServer $newDBServer -BizTalkMgmtDb $BizTalkMgmtDb -query “SELECT * FROM [dbo].[adm_OtherDatabases] where DefaultDatabaseName = 'SSO'”

$BizTalkMsgBoxDb = $admin_Group_dataTable.SubscriptionDBName
$BizTalkDTADb = $admin_Group_dataTable.TrackingDBName
$BizTalkRuleEngineDb = $admin_Group_dataTable.RuleEngineDBName

$SSODB = $adm_OtherDatabases_dataTable.DatabaseName
 
if($BizTalkMsgBoxDb -eq '' -or $BizTalkDTADb -eq '' -or $SSODB -eq '')
{
    Write-Output "Cannot continue as not all values below could be read from $BizTalkMgmtDb on $newDBServer :"
    Write-Output "BizTalkMsgBoxDb = $BizTalkMsgBoxDb"
    Write-Output "BizTalkDTADb = $BizTalkDTADb"
    Write-Output "SSODB = $SSODB"
    
    exit 
}

$UpdateInfoFileName = '.\UpdateInfo_' + $(Get-Date).ToString("yyyy-MM-dd_HH_mm") + '.xml'

Create-UpdateInfoFile -oldDBServer $oldDBServer -newDBServer $newDBServer | Out-File $UpdateInfoFileName

$runningHostInstances = Get-Service | Where-Object {$_.status -eq "running" -and $_.Name -like 'BTSSvc$*'}

Stop-Service -displayname "Enterprise Single Sign-On Service" -Force

cscript UpdateDatabase.vbs $UpdateInfoFileName

c:\windows\sysnative\cscript.exe UpdateRegistry.vbs $UpdateInfoFileName

Start-Service -displayname "Enterprise Single Sign-On Service"

$runningHostInstances|ForEach-Object{$_.Start()}

Pop-Location