Ever had the need to export either all BizTalk applications or all binding files from a BizTalk Server installation? You can use this little SQL script to generate a BTSTAST script automatic this process for you.

Simply open SQL Server Management Studio, connect to your database server and open a new query window. Paste the script, hit CTRL+T to have the result output as text and F5 to run it.

Remove the lines with dashes (-) and (XX row(s) affected) and copy the remaining lines to a .bat file of your choice. Run the bat script, sit back and watch the export process :o)

DECLARE @MSIPATH VARCHAR(50);
DECLARE @BINDINGPATH VARCHAR(50);

-- Set paths for exported files
SET @MSIPATH = 'C:\Backup\' + CONVERT(VARCHAR(8), GETDATE(), 112) + '\MSI\';
SET @BINDINGPATH = 'C:\Backup\' + CONVERT(VARCHAR(8), GETDATE(), 112) + '\Bindings\';

-- Generate script for exporting MSI packages
SELECT
    'BTSTASK ExportApp -ApplicationName:"' + nvcName + '" -Package:"' + @MSIPATH + nvcName + '.msi"'
FROM
    [BizTalkMgmtDb].[dbo].[bts_application]
WHERE
    isDefault <> 1
AND
    isSystem <> 1;

-- Generate script for exporting binding files
SELECT
    'BTSTASK ExportBindings -ApplicationName:"' + nvcName + '" -Destination:"' + @BINDINGPATH + nvcName + '.xml"'
FROM
    [BizTalkMgmtDb].[dbo].[bts_application]
WHERE
    isDefault <> 1
AND
    isSystem <> 1;

SELECT 'PAUSE';