Export (0) Print
Expand All

How to Back Up and Restore SQL Agent Jobs

[Unless specifically noted, the content in this topic applies to BizTalk Server 2013 and 2013 R2.]

This topic describes how to back up and restore SQL Server Agent Jobs. You should back up your SQL jobs after you configure them.

BizTalk Server Jobs

The following SQL Server Agent jobs are associated with BizTalk Server. The jobs installed on each server will be different depending on which features are installed and configured. Most of these jobs are created during BizTalk Server setup. Several are created when configuring log shipping.

  • Backup BizTalk Server (BizTalkMgmtDb)

  • CleanupBTFExpiredEntriesJob_BizTalkMgmtDb

  • DTA Purge and Archive (BizTalkDTADb)

  • MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb

  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb

  • MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb

  • MessageBox_Parts_Cleanup_BizTalkMsgBoxDb

  • MessageBox_UpdateStats_BizTalkMsgBoxDb

  • Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb

  • PurgeSubscriptionsJob_BizTalkMsgBoxDb

  • Rules_Database_Cleanup_BizTalkRuleEngineDb

  • TrackedMessages_Copy_BizTalkMsgBoxDb

  • BTS Log Shipping Get Backup History

  • BTS Log Shipping Restore Database

  • BTS Log Shipping Restore To Mark

To back up a job using a script (SQL Server 2008 R2/SP1)

  1. Start SQL Server Management Studio.

  2. Expand the SQL Server Agent folder, and then expand the list of Jobs.

  3. Right-click the job you want to create a backup script for, and then select Script Job as.

  4. Click CREATE To or DROP To, then click New Query Editor Window, File, or Clipboard to select a destination for the script. Typically, the destination is a file with a .sql extension.

  5. Repeat this procedure from Step 3 for each job you want to script. Refer to the list of BizTalk Server related jobs to determine which jobs you will need to script.

    At a minimum, you should back up the Backup BizTalk Server (BizTalkMgmtDb) job after it is configured.

To restore a job from a script (SQL Server 2008 R2/SP1)

  1. Start SQL Server Management Studio.

  2. Click File, click Open, then click File.

  3. Locate and open the file containing the scripted job.

  4. Execute the script to create the job.

© 2014 Microsoft Corporation. All rights reserved.

Community Additions

© 2014 Microsoft