Export (0) Print
Expand All

How to Configure the Backup BizTalk Server Job

You must configure the Backup BizTalk Server job before you can back up BizTalk Server. To configure the backup, you will need to perform most or all of the following tasks:

  • Edit the SQL Server Agent job Backup BizTalk Server (BizTalkMgmtDb) to identify the primary and destination SQL Servers and other backup options

  • Choose a Windows user account to back up your databases and create a SQL Server login for this account

  • Map SQL Server logins to the BTS_BACKUP_USERS database role in the BizTalk Server databases

  • Ensure MSDTC service is active on all nodes. Else, adding a linked server between the source node and the destination node will fail.

Certain configuration and backup operations such as this one require membership in the sysadmin SQL Server role. To back up your BizTalk Server databases, you must be logged on at the primary server with an account that is a member of the SQL Server sysadmin Server role on the primary server. BizTalk Server configuration adds a database role named BTS_BACKUP_USERS so that the user account you use to back up your databases does not require System Administrator (sysadmin SQL Server role) permissions on all of the SQL Servers that may be involved in a backup, except for the primary server.

You will need to decide which login account you will use to perform your BizTalk Server database backups. You can use a local account, and you can use more than one account, but it is generally simpler and more secure to create one dedicated Windows domain user account specifically for this purpose. You must configure a SQL Server logon account for this user, and the user must be mapped to a SQL Server login for all of the SQL Servers that participate in the backup process, either as primary (source) or secondary (destination) servers. Assign this user to the BizTalk BTS_BACKUP_USERS database role for each of the BizTalk Server databases you will back up.

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.

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.

The following procedures will guide you through the steps necessary to configure the backup job.

noteNote
To perform these tasks, you must login to SQL Server using a login account that is a member of the sysadmin SQL Server role.

noteNote
You must configure the SQL Server Agent service to run under a domain account (recommended, although local accounts can be used), with a mapped user login on each instance of SQL Server.

  1. On the computer that contains the BizTalk Management database, click Start, click All Programs, click Microsoft SQL Server 2008 R2, 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 Microsoft SQL Server Management Studio, double-click SQL Server Agent, and then expand Jobs.

  4. In the Object Explorer 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.

    To configure backup compression:

    noteNote
    By default, Set Compression Option is set to 0, which makes backup compression off by default. To change the default, change Set Compression Option to 1.

    1. In the Job step list, click Set Compression Option, and then click Edit.

    2. On the General page, in the Command box, edit the command, and then click OK.

      exec [dbo].[sp_SetBackupCompression] @bCompression = 0 /*0 - Do not use Compression, 1 - Use Compression */
      
      
  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 */
    
    1. Frequency: The default is d (daily). This is the recommended setting. Other values include h (hourly), w (weekly), m (monthly), or y (yearly).

    2. Name: The default is BTS. The name is used as part of the backup file name.

    3. 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.

      ImportantImportant
      • 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.

        To specify a remote path, enter a UNC share such as \\<ServerName>\<SharedDrive>\, where <ServerName> is the name of the server where you want the files, and <SharedDrive> is name of the shared drive or folder.

        Backing up data over a network is subject to any network issues. When using a remote location, verify the backup succeeded when the Backup BizTalk Server job finishes.

      • To avoid potential data loss, configure the backup disk to be a different disk than the database data and log disks. This is necessary so you can access the backups if the data or log disk fails.

    4. 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.

    5. 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 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 computer, specify an integer value from 0 (midnight) to 23 (11 PM) as the local time hour for the BackupHour parameter.

  8. In the Job step list, double-click MarkAndBackupLog to open the Job Step Properties – MarkAndBackupLog screen.

    The MarkAndBackupLog step is responsible for marking the logs for backup, and then backing them up.

  9. On the General page, in the Command box, replace '<destination path>' with the full path (including single quotes) to the computer and folder where you want to store the BizTalk Server database logs and then click OK. The <destination path> may be local or a UNC path to another server.

    ImportantImportant
    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.

  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.

    noteNote
    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 How to Schedule the Backup BizTalk Server Job.

    noteNote
    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.

noteNote
The sp_ForceFullBackup stored procedure in the BizTalkMgmtDb database can be used to help perform an ad-hoc full backup of the data and log files. The stored procedure updates the adm_ForceFullBackup table with a value 1. The next time the Backup BizTalk Server job is ran, a full database backup set is created.

© 2010 Microsoft Corporation. All rights reserved.

Community Additions

ADD
Show:
© 2014 Microsoft