Scheduling Database Backups

Use the Backup BizTalk® Server job to back up your databases. The Backup BizTalk Server job runs as scheduled by the SQL Agent service.

Important  Backup BizTalk Server does not delete old backup files. You must manage those files to conserve disk space by backing them up and deleting them after new full backups have been created.

Important  Do not store the backup files on the same storage system as the database, because in the event of a failure, both the backups and the databases would be lost.

Scheduling backups for the BAM databases

  • If you are using Business Activity Monitoring (BAM), verify that neither the BAM cube process nor data maintenance Data Transformation Services (DTS) packages are running when the backup package is scheduled to run.
  • To ensure consistent schema across all BAM databases, back up the BAM databases and DTS packages each time you deploy or undeploy a BAM activity.
  • Back up the BAM Analysis database and Star Schema database each time you deploy or undeploy a BAM view.
  • Back up the BAM databases in the following order:
    1. Back up the BAM Analysis database, and then the BAM Star Schema database.
    2. Run Backup BizTalk Server to back up the BAM Primary Import database and your other BizTalk Server databases.
    3. Run the BAM data maintenance DTS package for all activities.

    Incorporate these steps into a DTS package, and schedule the package to run on a regular basis. To ensure data integrity, make sure no other BAM cubing or data maintenance DTS packages run when this backup package is scheduled to run.

    To ensure that you can recover a complete set of archived data should the BAM Archive database fail, back up the BAM Archive database after you copy the partition into the BAM Archive, but before you delete the partition from the BAM Primary Import database. To do this, modify the data maintenance DTS package for each activity to insert a step to back up the BAM Archive database before the last step "End Archiving."

Scheduling times

Follow these guidelines for scheduling your database backups:

  • Ideally, it is recommended that you back up your databases every 15 minutes.
  • At a minimum, back up your databases at least once a day.
  • Schedule the Backup BizTalk Server job so that it does not overlap other SQL Agent jobs.

To determine how often you should perform full and log backups for your environment, you must analyze your storage capabilities and the negative impact of losing in-flight transactions. Consider the following:

  • The more frequently you back up your database, the less data will be lost in the event of a failure.
  • Log backups are smaller than full backups but take more time to restore.

To schedule BizTalk database and log backups

  1. On the computer that contains the BizTalk Configuration database, start SQL Enterprise Manager.
  2. Expand the server, expand Management, expand SQL Server Agent, click Jobs, and then double-click Backup BizTalk Server Job.
  3. On the Steps tab, double-click BackupFull.
  4. In the Command box, change the following default command.
    exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '<destination path>' /* location of backup files */
    

    When changing the command, note the following:

    • 'd' = daily full backups. You can change this period to hourly (h/H), weekly (w/W), monthly (m/M), or yearly (y/Y). The first time the job is run during a new period, a full backup is performed.
    • 'BTS' is the default for Name.
    • '<destination path>' for the destination path. You must change this to a valid destination. The destination path may be local or a UNC path to another server.
  5. Click OK.
  6. On the Steps tab, double-click MarkAndBackupLog.
  7. In the Command box, change the following default command:
    exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '<destination path> ' /* location of backup files */
    

    When changing the command, note the following:

    • 'BTS' is the default for Log Mark Name. Change as needed.

      Log Mark Name is part of the naming convention for backup files:

      <Server Name>_<Database Name>_Log_< Log Mark Name >_<Timestamp>
      
    • '<destination path>' for the destination path. You must change this to a valid destination. The destination path may be local or a UNC path to another server.
  8. Click OK.
  9. Click the Schedules tab. By default, the job is scheduled to run every 15 minutes.
  10. To change the frequency, click Change, and then specify a new schedule.

For more information about scheduling SQL Agent jobs, see "Scheduling Jobs" at http://go.microsoft.com/fwlink/?LinkId=25092.

See Also

Backing Up Your Databases

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: