Database Maintenance (BizTalk Server Sample)

The Database Maintenance sample demonstrates how to archive and restore sent and received message bodies, and how to delete the contents of the BizTalk Tracking database.

Note  For important information about all samples in this SDK, see Samples.

To download the updated BizTalk Server 2004 SDK, go to http://go.microsoft.com/fwlink/?LinkId=24723.

Location in SDK

<Samples Path>\Admin\Database Maintenance\

File Inventory

The following table shows the files in this sample and describes their purpose.

File(s)Description
ArchiveMessages.vbsMicrosoft® Visual Basic® Scripting Edition (VBScript) file used to archive sent and received message bodies.
Purge_DTADB.sqlStored procedure for deleting the contents of the Tracking database.
RestoreMessages.sqlSimple SQL scripts that restore message bodies previously backed up from a MessageBox database.
Tracking_Fragments.fmt, Tracking_Parts.fmt,. Tracking_Spool.fmtSQL format files used by the files ArchiveMessages.vbs and RestoreMessages.sql. The format files must be in the same folder as the files that use them.

To Build and Initialize This Sample

Use the following procedure to build and initialize the Database Maintenance sample.

  1. Before running ArchiveMessages.vbs or Purge_DTADB.sql, be sure the SQLServerAgent service is turned on.
  2. In Health and Activity Tracking, on the Configuration menu, click Orchestrations.
  3. Select an orchestration, select the Inbound message bodies and/or Outbound message bodies check boxes, and then click Apply.
  4. In Health and Activity Tracking, on the Configuration menu, click Pipelines.
  5. Select a pipeline, select the Inbound message bodies and/or Outbound message bodies check boxes, and then click Apply.

To Run This Sample

To run the various scripts and stored procedures in the Database Maintenance sample, copy the .vbs, .fmt, .sql, and .dat files to the machine running the BizTalk Tracking database and follow these instructions.

To archive messages

  • Confirm that the three format files, Tracking_Fragments.fmt, Tracking_Parts.fmt, and Tracking_Spool.fmt, are in the same folder, and then run ArchiveMessages.vbs.

To purge messages

  • Open the SQL Query Analyzer, select your BizTalk Tracking database, load Purge_DTADB.sql, and execute the script. The script creates the stored procedure dtasp_PruneTrackingDatabase in the BizTalk Tracking database. By calling the stored procedure and passing in a date/time parameter, you can delete all events and messages up to that date/time.

To restore messages

  1. Update the following lines to reflect your scenario.
    LineDescription
    SELECT @server = N'MyServerName'Update to reflect your scenario.
    SELECT @backupTimeStamp = N'YYYYMMDDHHMMSS'Update to reflect your scenario.
    USE BizTalkDTADbUpdate if your BizTalk Tracking database name is different.
    SELECT @filePrefix = @server + @backupTimeStampIf you use a file prefix that is different than the default created by ArchiveMessages.vbs (for example, MyFilePrefix), change this line to:

    SELECT @filePrefix = N'MyFilePrefix'

    SELECT @fmtPath = N'C:\FormatFilePath'Update to reflect the path to the .fmt files on your BizTalk Tracking database computer.
    SELECT @datPath = N'C:\DatFilePath'Update to reflect the path to the .dat files on your BizTalk Tracking database computer.
  2. Open SQL Query Analyzer, select a database, and load RestoreMessages.sql. You must choose the provided format files (.fmt) within the script. You should also have run the ArchiveMessages.vbs script so that some archived messages (.dat files) already exist, and the script should reference those archived message files. When you run RestoreMessages.sql, BizTalk Server restores the corresponding messages.

Remarks

These sample scripts may not be as transactional as is required in some production environments. For example, message archiving, which uses the bcp utility to access three distinct tables, results in a new transaction for each table access. If the second or third access operation fails, the bcp utility does not roll back the previous operation. Currently, manual clean up is required before the script is run again.

Further, these sample scripts may not have adequate error handling for some environments. The application reports SQL Server errors, but does not translate them into friendlier, script-specific error messages.

The following table provides additional information about files in this sample.

File nameAdditional information
ArchiveMessages.vbsThis file contains a Microsoft® Windows® script containing Visual Basic Script Edition (VBScript) script that you can run on the local BizTalk Server computer on which the MessageBox database (BizTalkMsgBoxDB) exists.

This script assumes that the user running the script has sufficient permission to access the MessageBox database.

This script first archives everything in the offline spool and empties it. The current online spool is then marked as offline, and the just emptied offline spool is marked as online. So if your offline spool is empty when you run this script, three empty .dat files are created.

If you want to access a different MessageBox database, change the following lines in this script:

g_localServerName = env.Item("COMPUTERNAME")
dbName = "BizTalkMsgBoxDB"

The SQL format files, Tracking_Fragments.fmt, Tracking_Parts.fmt, and Tracking_Spool.fmt, must be in the same folder as this script.

The files generated by this script have the exact same names of the archived tables.

Note  When you run the stored procedure dtasp_PruneTrackingDatabase, you may notice a "duplicate key was ignored" message. This message is expected and does not indicate a failure.

RestoreMessages.sqlThis file contains simple SQL scripts that restore message bodies that were previously backed up from a MessageBox database. It uses SQL Bulk Insert functionality, and relies on the following format files:
  • Tracking_Fragments.fmt
  • Tracking_Parts.fmt
  • Tracking_Spool.fmt

For additional information about format files, see "Using Format Files” in SQL Server Books Online.

See Also

Admin (BizTalk Server Samples Folder)

To download updated BizTalk Server 2004 Help from www.microsoft.com, go to http://go.microsoft.com/fwlink/?linkid=20616.

Copyright © 2004 Microsoft Corporation.
All rights reserved.
Show: