Over time, I have found or developed several T-SQL (Transact-SQL) commands that you can run to help cross reference (XREF) various BizTalk entities. Most of these run against the BizTalkMgtmDB database.

Here is a list of them, so they are easy to find:

BizTalk SQL Queries

1. Simple list of Receive Ports and Receive Locations
2. Xref BizTalk Maps to Ports (Sends and Receive Ports)
and looks like I blogged this twice: SQL to find all BizTalk Maps on all Send and Receive Ports
3. BizTalk SQL to XRef Send/Receive Ports to Orchestrations
4. Find which Send Ports subscribe to which Receive Ports with SQL XREF query – based on RecivePortName= filters
5. XREF a BizTalk BTS.Operation back to the Orchestration Operation Name
6. Cross Reference BizTalk Receives bound to Orchestrations
7. Query and Join to show all BizTalk maps and map names (joining bt_MapSpec and bts_item)
8. SQL XRef BizTalk Pipelines to Sends/Receive Ports
9. XRef BizTalk Pipeline to Receive Locations

EDI SQL Queries

10. Xref SendPorts to BizTalk EDI Parties/Agreements
11. SQL to Lookup Parties based on Qualifier/Identifier (or list Party Names)
12. SQL to Retrieve BizTalk BatchingFilters for EDI Parties

Trying to delete a Send Port, you get this error:

SendPort ‘sp_ABC’ is being referenced by one or more TPM Parties. Please remove the Send Port reference(s) from the parties and retry deleting the Send Port (Microsoft.BizTalk.ExplorerOM)

By the way, TPM stands for “Trading Partner Management”.

Explanation

When you use EDISend Pipeline, BizTalk needs to know which Party to use, in order to put the correct identifiers and other fields in the ISA and GS segments of the EDI file.

One of the most common techniques for identifying the Party is to put the SendPort name in the X12_Agreement (found under “Parties” and then usually the name of the vendor or trading partner).

This creates a foreign-key relation or dependency in the database, and the SendPort cannot be deleted while it is being used in the X12 Agreement.

Parties can be found here in the BizTalk Admin Console. On the right, you will see a list of your trading partners (they might be customers or vendors for example):

Under the selected Party, choose the X12 agreement, and at the top, select the tab that says from your company to the trading partner. Click on “Send Ports” on the left. If you still want to delete your SendPort, find it in the list of SendPorts (one below is blurred to hide the name), then click the remove icon or word. That only disassociates the SendPort from the Agreement (it doesn’t actually delete the SendPort from the system).

But wait, there’s more! When you put a SendPort on an agreement, it get’s associated to the party as well. So there is a second place you have to remove it.

Now you should be able to go back to the Application and delete the SendPort.

If you are not sure which Party to select above, then I have a T-SQL query that cross-references Send Ports and parties.

Open the “BizTalk Server Administration Tool/Console”, expand the hierarchy on the left, and click on the “BizTalk Group”. From there, click on the tab at the top labeled “New Query”. The “Field Name” should already be populated with “Search for” and you need to use the drop-down box to pick “Subscriptions” for the “Value” column, then click “Run Query”.

Then find your orchestration preceded by the word “Activate”.

Click on it then go to the expression tab. This is where you will find the subscription for your orchestration. If you are using the “specify later” binding, it will be a ReceivePort name, and will typically have a message type, which is namespace#rootelement (from your schema).

To correlate the ReceivePort name to the GUID above, you should be able to use a query similar to this in SQL Server Management Studio (SSMS), add a where clause as needed (probably the uidGUID column, but I didn’t test it yet).

use BizTalkMgmtDb
select * from bts_receiveport 
where uidGUID = '794CD17E-6A4A-494C-BB30-564D323FD4D6'
--Substitute your GUID from the previous screen shot
--My Guid doesn't match above (B746...), 
-- because I fixed the Orchestration Binding 
-- before taking this second screen shot. 

Found this document:
PowerShell Provider for BizTalk 2013 this week.

Its from Francois Malgreve from Europe, and written October 2014, but most of it seems to still be applicable.

The table of contents has page number, but unfortunately, I couldn’t find any page numbers on the individual pages.

Suppose you want to list all orchestrations in an application, and then start, stop, unenlist, or enlist them.

It’s not object oriented. You don’t say $orch.unenlist, you use the command followed by the orchestration name.

There are four commandlets:
1. Enlist-Orchestartoin
2. Start-Orchestration
3. Stop-Orchestration
4. Unenlist-Orchestration

#unenlist all orchestration in a BizTalk application 
#Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions  
#NOTE: Must be in 32-bit version of Powershell to use this SnapIn
cls 
$appName = "TL2000"
cd "Biztalk:\Applications\$appName\Orchestrations"

#list the orchestrations and some of their properties 
#(only for debug, not needed to run below)
Get-ChildItem | ft -auto 

$orchs = Get-ChildItem  #get them into a variable so we can loop 
foreach ($orch in $orchs) 
{
  Write-Host "Orch Name: $($orch.Name)" 
  Unenlist-Orchestration -Path $orch.Name 
}

Sometimes you ask yourself what version of BizTalk is running on a certain server. Maybe you’re a consultant or employee stepping into a new job/contract and you are trying to get the lay of the land. Or maybe you have some old servers that are not documented.

The following script will answer that question. Might save keystrokes of opeining Control Panel or RegEdit to find out.

See this page to get a list of all the BizTalk versions. Hope you don’t find anything older than BizTalk 2010!

cls
#read the registry 
$item = Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\BizTalk Server\3.0"
Write-Host "Biztalk Internal Version: $($item.ProductVersion)"
$major,$minor, $x, $y = $item.ProductVersion.split('.') 
#Write-Host "$major $minor" 

# see https://social.technet.microsoft.com/wiki/contents/articles/7915.biztalk-server-versions.aspx 

$version = switch ($minor)
{
    13 {"2020"}  #assumed 
    12 {"2016"}
    11 {"2013/R2"}
    10 {"2013 (not R2)"}
    9 {"2010"} 
    8 {"2009"} 
    6 {"2006/R2"} 
    5 {"2006 (not R2)"} 
    0 {"2000-2004 Past Time to Upgrade! "} 
}

Write-Host "BizTalk Common Name: $version" 
Write-Host "Product Edition: $($item.ProductEdition)" 

Example Output:

Biztalk Internal Version: 3.10.229.0
BizTalk Common Name: 2013 (not R2)
Product Edition: Enterprise

BizTalk has a schedule job that does incremental backups, usually every 15 minutes. But what if you want to do a full backup immediate?

Run this command in SQL Server Management Studio (SSMS):

USE BizTalkMgmtDb
EXEC sp_ForceFullBackup

Then manually start the SQL agent job “Backup BizTalk Server (BizTalkMgmtDb)” (or wait until the next scheduled 15 minute interval).

The above is required if you get in the situation where you see this error in the 3rd step of the above backup agent job:

Error: BACKUP LOG cannot be performed because there is no current database backup.

test

This script can be scheduled to create a nightly backup of all the bindings and MSIs for all the applications in BizTalk. It uses the Powershell Extensions for BizTalk to enumerate the application names, then calls BTSTask once for the bindings, and once for the MSI.

It gives you the ability to customize your backup folder names and files names. For example, if your application name is “ABC.Orders”, the backups are:
ABC.Orders_2019_05_17__09_10_21.xml
ABC.Orders_2019_05_17__09_10_21.msi

You could make changes to create a new folder for each set of backups, example: Backups_2019_05_17__09_10_21.

Another enhancement could be to zip the archives, or to purge archives over x days.

There is one trick. You may also want to backup the Parties. I’m currently on BizTalk 2013, and I know there are some improvements for supporting importing/exporting parties individually in 2016. Given that I’m on BT2013, I decided to backup the parties just once. So if the application name = “BizTalk EDI Application”, I add the parameter for the party backup, and they will be in that file. You could also create a dummy empty application and use it.

<#

       Author: Neal Walters 
  Description: Backup BizTalk Applications (both Bindings and Application/MSIs) via Script 
         Date: May 2019 

#>

Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions
#This next line only needed when SQL server is not on same machine as BizTalk
New-PSDrive -Name BizTalk -Root BizTalk:\ -PsProvider BizTalk -Instance "YourSQLServer" -Database BizTalkMgmtDb

$exportFolder = "c:\Backup\BizTalk" 
$server = "YourSQLServer"
$database = "BizTalkMgmtDB"

cls

$apps = Get-ChildItem -Path "Biztalk:\Applications"

foreach ($app in $apps) 
    {
        Write-Host "===== Application: $($app.Name) ==== " 

        if ($app.Name -ne "BizTalk.System")   # avoid error trying to export this app 
          {
            $fmtDateTime = $(get-date -f yyyy_MM_dd__HH_mm_ss)
            $bindingsDestFilename = "${exportFolder}\$($app.Name)_$fmtDateTime.xml" 
            $msiAppDestFilename = "${exportFolder}\$($app.Name)_$fmtDateTime.msi" 
           
            write-host $bindingsDestFilename 
            $allArgs = @("ExportBindings", "/Destination:$bindingsDestFilename",  "/ApplicationName:$($app.Name)",
                "/Server:$server", "/Database:$database") 
            if ($app.Name -eq "BizTalk EDI Application") 
            {
               #just backup the parties one time with this one app 
               $allArgs += "/GlobalParties"
            }

            write-host "Args=$allArgs"
            & "c:\Program Files (x86)\Microsoft BizTalk Server 2013\BTSTask.exe" $allArgs

            $allArgs = @("ExportApp", "/Package:$msiAppDestFilename",  "/ApplicationName:$($app.Name)",
                "/Server:$server", "/Database:$database") 
            write-host "Args=$allArgs"
            & "c:\Program Files (x86)\Microsoft BizTalk Server 2013\BTSTask.exe" $allArgs
          }
    }

 

I’m writing a custom deploy solution for BizTalk. If the application doesn’t exist, I need to call “BTSTask AddApp” to add it.

Sample:


Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions

function checkApplicationExists ($appName) 
{
    cd "Biztalk:\Applications"
    $apps = Get-ChildItem
    $boolFoundMatch = $false 
    foreach ($app in $apps) 
      {
         Write-Host "  Testing $($app.Name) " 
         if ($app.Name -eq $appname) 
         {
            $boolFoundMatch = $true 
         }
      }
    return $boolFoundMatch 
  
}

cls
# Test an application that you know exists 
$appName = "Test.Vendors"
$app1Exists = checkApplicationExists $appname 
Write-Host "$appName $app1Exists" 

# Test an application that you know does not exist 
$appName = "Test.Vendors.NonExistent"
$app1Exists = checkApplicationExists $appname 
Write-Host "$appName $app1Exists" 


PowerShell extensions allow you to interact with BizTalk Artificacts. For example, you can like list applications, sendports, receive ports, host instances, and enable/disable them in PowerShell.

First run the InstallUtil

C:\Windows\system32>c:\Windows\Microsoft.NET\Framework64\v4.0.30319\InstallUtil.exe "c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll"

Output:
Microsoft (R) .NET Framework Installation utility Version 4.7.2558.0
Copyright (C) Microsoft Corporation. All rights reserved.

Running a transacted installation.

Beginning the Install phase of the installation.
See the contents of the log file for the c:\Program Files (x86)\Microsoft BizTal
k Server 2013\SDK\Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll
assembly’s progress.
The file is located at c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\
Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.InstallLog.
Installing assembly ‘c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Ut
ilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll’.
Affected parameters are:
logtoconsole =
assemblypath = c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utili
ties\PowerShell\BizTalkFactory.PowerShell.Extensions.dll
logfile = c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utilities\
PowerShell\BizTalkFactory.PowerShell.Extensions.InstallLog

The Install phase completed successfully, and the Commit phase is beginning.
See the contents of the log file for the c:\Program Files (x86)\Microsoft BizTal
k Server 2013\SDK\Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll
assembly’s progress.
The file is located at c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\
Utilities\PowerShell\BizTalkFactory.PowerShell.Extensions.InstallLog.
Committing assembly ‘c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Ut
ilities\PowerShell\BizTalkFactory.PowerShell.Extensions.dll’.
Affected parameters are:
logtoconsole =
assemblypath = c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utili
ties\PowerShell\BizTalkFactory.PowerShell.Extensions.dll
logfile = c:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utilities\
PowerShell\BizTalkFactory.PowerShell.Extensions.InstallLog

The Commit phase completed successfully.

The transacted install has completed.

Next, in Powershell (assuming you have already set your execution policy):

If your SQL Server is on same machine as BizTalk this should work:

Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions

#Verify it works by listing the BizTalk Applications 
cd "Biztalk:\Applications\"
Get-ChildItem | ft -auto 

If you SQL Server is on a different machine than the BizTalk server, then add the New-PSDrive statement as below (StackOverflow)

Add-PSSnapIn -Name BiztalkFactory.PowerShell.Extensions
New-PSDrive -Name BizTalk -Root BizTalk:\ -PsProvider BizTalk -Instance MySqlServer -Database BizTalkMgmtDb

#Verify it works by listing the BizTalk Applications 
cd "Biztalk:\Applications\"
Get-ChildItem | ft -auto