Configure the Backup BizTalk Server Job

How to Configure the Backup BizTalk Server Job

You must configure the Backup BizTalk Server job before you can back up BizTalk Server 2006.

When setting up the user account that you are using to back up your databases, note the following:

• You must configure the SQL Server Agent service to run under a domain account or a local account with a mapped user on each instance of SQL Server.
  
• You must configure a SQL Server logon account for this user, and assign this user to the BizTalk BTS_BACKUP_USERS role on each server.
  
• Using a local account for the SQL Server Agent service is not recommended. For example, if you are backing up to a remote computer, you would need to create a local account on the destination system using the same user name and password that you have used on the primary system. If the password were changed on one system but not on the other, the backup job would fail. Using a domain account avoids these issues (and others) and provides centralized account administration.
  
• BizTalk Server includes a SQL Server role named BTS_BACKUP_USERS so that the user account you use to back up your databases does not require System Administrator permissions within SQL Server, except for the primary server controlling the backup process.
  
• The Backup BizTalk Server job can be run under a different account than the SQL Server Agent service account. The general procedure described in How to Configure the BTS_BACKUP_USERS Role for Archiving and Purging Data from the BizTalk Tracking Database can be used to configure the backup job to run under a different user account by changing the database names and user accounts as appropriate.
  

Note
The Backup BizTalk Server job does not delete outdated backup files, so you need to manually manage those backup files to conserve disk space. After you have created a new full backup of your databases, you should move the outdated backup files onto an archival storage device to reclaim space on the primary disk.

Note
BizTalk Server does not write tracking data directly to the BizTalk Tracking database; rather it caches the data in the MessageBox database and then moves it to the BizTalk Tracking database. If MessageBox data loss occurs, some tracking data may also be lost.

Prerequisites

---

To back up your BizTalk Server 2006 databases, you must be logged on with an account that is a member of the SQL Server sysadmin fixed server role.

To configure the Backup BizTalk Server job (SQL Server 2005)

---

1. On the computer that contains the BizTalk Management database, click Start, click Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.
2. In the Connect to Server dialog box, specify the name of the SQL Server where the BizTalk Server databases reside and the appropriate authentication type, and then click Connect.
3. In SQL Server Management Studio, double-click SQL Server Agent, and then click Jobs.
4. In the details pane, right-click Backup BizTalk Server (BizTalkMgmtDb), and then click Properties.
5. In the Job Properties - Backup BizTalk Server (BizTalkMgmtDb) dialog box, under Select a page, click Steps.
6. In the Job step list, click BackupFull, and then click Edit.
7. On the General page, in the Command box, edit the command, and then click OK.
   
   exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '<destination path>' /* location of backup files */, 0 (default) or 1 /* ForceFullBackupAfterPartialSetFailure */, 'BackupHour' /* local time hour for the backup process to run */
   

   • Frequency: The default is d (daily). This is the recommended setting. Other values include h (hourly), w (weekly), m (monthly), or y (yearly).
     
   • Name: The default is BTS. This is the recommended setting. The name is used as part of the backup file name.
     
   • Location of backup files: Replace '<destination path>' with the full path (the path must include the single quotes) to the computer and folder where you want to back up the BizTalk Server databases. If you specify a local path, then you have to manually copy all the files to the same folder on the destination system whenever the Backup BizTalk Server job creates new files. If you specify a remote path, it must be a UNC share such as \\<ServerName>\<SharedDrive>\, where <ServerName> is the name of the server where you want the files to be backed up using the Backup BizTalk Server Job, and <SharedDrive> is name of the shared folder.
     
     

     Note
     As a best practice for backing up your data, you should specify a computer for your backup that is different from the computer with the original data.
   • Force full backup after partial backup failures: The default is 0 when not specified, which means that if a log backup fails, no full backups are done until the next full backup frequency interval is reached. Replace with 1 if you want a full backup to be made whenever a log backup failure occurs.
     
   • Local time hour for the backup process to run: The default is NULL when not specified, which means that backup job will not be associated with the time zone of the BizTalk Server 2006 computer and will run at midnight UTC time (0000). If you want to backup to run at a particular hour in the time zone of the BizTalk Server 2006 computer, specify an integer value from 0 (midnight) to 23 (11 PM) as the local time hour for theBackupHour parameter.
     
8. In the Job step list, click MarkAndBackupLog, and then click Edit.
9. On the General page, in the Command box, edit the command, and then click OK.
   
   exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '<destination path>' /* location of backup files */ 0 (default) or 1 /* use local time stamps for the backup log */
   

   Modify the parameters in the Command box as needed:
   
   • Log mark name: The default is BTS. This is the recommended setting. The name is used as part of the database log files backup name.
     
   • location of backup files: Replace '<destination path>' with the full path (including single quotes) to the computer and folder where you want to store the BizTalk Server database log files. The <destination path> may be local or a UNC path to another server.
     
     

     Note
     To avoid potential data loss, the <destination path> should specify a computer to store the database logs that is different from the computer with the original database logs.
   • use local time stamps for the backup log: The default is 0 when not specified which indicates to use UTC time stamps for the backup log files. Replace with 1 if you want to use local time stamps for the backup log files and then click OK.
     
10. In the Job step list, click Clear Backup History, and then click Edit.
11. On the General page, in the Command box, change DaysToKeep=<number> to the number of days you want to keep the backup history, and then click OK twice to close the Job Properties - Backup BizTalk Server (BizTalkMgmtDb) dialog box.
    

    Note
    The DaysToKeep parameter specifies how long the backup history is kept in the Adm_BackupHistory table. Periodically clearing the backup history helps to maintain the Adm_BackupHistory table at an appropriate size. The default value for the DaysToKeep parameter is 14 days.
12. Change the backup schedule, if desired. For more information, see Scheduling the Backup BizTalk Server Job.
    

    Note
    The Backup BizTalk Server job runs the first time you configure it. By default, on subsequent runs, the Backup BizTalk Server job performs a full backup once a day and performs log backups every 15 minutes.
13. In the details pane, right-click the Backup BizTalk Server job, and then click Enable.
    
    In the Enable Jobs dialog box, the status changes to Success.