How to Back Up and Restore SQL Agent Jobs

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 2005 or SQL Server 2008)
  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 2005 or SQL Server 2008)
  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.

Community Additions