none
Backup BizTalk Server job RRS feed

  • Question

  • Hi,

    I'm a SQL DBA, maintaining BizTalk databases. Currently I'm using regular t-sql backup scripts to backup all BizTalk databases

    BizTalkMgmtDb
    SSODB
    BizTalkRuleEngineDb
    BizTalkMsgBoxDb
    BizTalkDTADb

    I have couple of questions here:

    1. If I want use BizTalk built-in backup job Backup BizTalk Server (BizTalkMgmtDb), I can only backup BizTalkMgmtDb database right? If yes why only this database requires built-in backup job?
    2. Do I need to go for regular backup scripts for the rest of the above databases? 
    Please correct me If I'm wrong

    thanks
    Friday, August 28, 2009 6:48 AM

Answers

  • Hi,

    BizTalk performs transactions across the different BizTalk databases this is why you need to backup all databases using a special procedure to backup the databases (the backup job). When you do a "manual" backup of each separate database you risk of being unable to restore the system.

    The job will not only backu the BizTalkMgmtDb database but will also backup the other databases.

    For more information see: http://msdn.microsoft.com/en-us/library/aa560972(BTS.10).aspx




    HTH, Randal van Splunteren - http://biztalkmessages.vansplunteren.net - Please mark as answered if this answers your question.
    Friday, August 28, 2009 6:55 AM
    Moderator
  • Three steps that will be performed by the Backup BizTalk Server Job.

    Step 1 performs full database backups of the BizTalk Server databases.
    Step 2 backs up the BizTalk Server database logs.
    Step 3 specifies for how long the backup history is kept.

    The Backup BizTalk Server SQL Agent job is a critical job that must be configured in order to be able to successfully back up the BizTalk Server 2006 databases that participate in Distributed Transaction Coordinator transactions. Databases that participate in DTC transactions such as with BizTalk must be backed up and restored as a set to ensure consistency.

    These databases must be backed up by the Backup BizTalk Server SQL Agent job and cannot be backed up using the normal SQL Server backup procedures. The reason is because BizTalk uses SQL Server log marks to keep the set of databases consistent as part of DTC transactions.
    The Backup BizTalk Server job creates a log mark and then backs up the database log for each database that is part of the Backup BizTalk Server SQL Agent job. This log mark is used when restoring the last log file for each database so that transactional consistency is maintained.
    Here are the steps to configure the Backup BizTalk Server SQL Agent job:

    1. In SQL Server 2000 Enterprise Manager or SQL Server 2005 Management Studio, navigate
    to the SQL Agent jobs list.

    2. Right-click Backup BizTalk Server (BizTalkMqmtDb) and select Properties.

    3. In the Job Properties dialog under Select a page, click Steps to view the job steps.

    4. In the Job step list, click BackupFull, and then click Edit.

    5. On the General page, in the Command box, 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. Also add a new parameter by typing a
    comma and then a number one (“,1”) at the end of the parameter list for the stored
    procedure sp_BackupAllFull. Adding this parameter enables an automatic full backup
    after a backup failure. Click OK when finished.

    6. In the Job step list, click MarkAndBackupLog, and then click Edit.

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

    8. In the Job step list, click Clear Backup History, and then click Edit.

    9. On the General page, in the Command box, change DaysToKeep=<number> to the
    number of days (default is 14) you want to keep the backup history, and then click OK
    twice to close the Job Properties dialog box.

    Change the backup schedule for MarkAndBackupLogSched if desired and then right-click
    the Backup BizTalk Server SQL Agent job and select Enable. The default schedule is to perform
    a log backup every 15 minutes.

    Once the Backup BizTalk Server job is configured and enabled, right-click and select Start
    Job to test. Click F5 to refresh the status on the Jobs node. If the result is not successful, check
    the following:

    • Verify that the destination folder exists and is reachable if a UNC share.
    • Check that the job owner has permissions on the destination folder.
    • Ensure that linked servers are configured properly if BizTalk databases are present in multiple SQL Server database instances.

    Abdul Rafay http://abdulrafaysbiztalk.wordpress.com/ Please mark this answer if it helps
    Friday, August 28, 2009 10:37 AM

All replies

  • Hi,

    BizTalk performs transactions across the different BizTalk databases this is why you need to backup all databases using a special procedure to backup the databases (the backup job). When you do a "manual" backup of each separate database you risk of being unable to restore the system.

    The job will not only backu the BizTalkMgmtDb database but will also backup the other databases.

    For more information see: http://msdn.microsoft.com/en-us/library/aa560972(BTS.10).aspx




    HTH, Randal van Splunteren - http://biztalkmessages.vansplunteren.net - Please mark as answered if this answers your question.
    Friday, August 28, 2009 6:55 AM
    Moderator
  • Thank you Randel,

    The job will not only backup the BizTalkMgmtDb database but will also backup the other databases.

    Is the above is true even if the BizTalk databases are on different instances on the same server 'Server A'. Please see the below

    ServerA\INS1 has BizTalkMsgBoxDb database

    ServerA\INS2 has BizTalkDTADb database

    ServerA\INS3 has BizTalkMgmtDb and SSODB databases

    We have Backup BizTalk Server (BizTalkMgmtDb) on ServerA\INS3 instance. If I enable this job & set the parameters in the job step, will it backup the other databases which are on other instances ServerA\INS2, ServerA\INS1??

    Please confirm above, I'm very eager to enable this built in backup job if it works for the above configuration we have.

    thanks
    Friday, August 28, 2009 7:50 AM
  • Three steps that will be performed by the Backup BizTalk Server Job.

    Step 1 performs full database backups of the BizTalk Server databases.
    Step 2 backs up the BizTalk Server database logs.
    Step 3 specifies for how long the backup history is kept.

    The Backup BizTalk Server SQL Agent job is a critical job that must be configured in order to be able to successfully back up the BizTalk Server 2006 databases that participate in Distributed Transaction Coordinator transactions. Databases that participate in DTC transactions such as with BizTalk must be backed up and restored as a set to ensure consistency.

    These databases must be backed up by the Backup BizTalk Server SQL Agent job and cannot be backed up using the normal SQL Server backup procedures. The reason is because BizTalk uses SQL Server log marks to keep the set of databases consistent as part of DTC transactions.
    The Backup BizTalk Server job creates a log mark and then backs up the database log for each database that is part of the Backup BizTalk Server SQL Agent job. This log mark is used when restoring the last log file for each database so that transactional consistency is maintained.
    Here are the steps to configure the Backup BizTalk Server SQL Agent job:

    1. In SQL Server 2000 Enterprise Manager or SQL Server 2005 Management Studio, navigate
    to the SQL Agent jobs list.

    2. Right-click Backup BizTalk Server (BizTalkMqmtDb) and select Properties.

    3. In the Job Properties dialog under Select a page, click Steps to view the job steps.

    4. In the Job step list, click BackupFull, and then click Edit.

    5. On the General page, in the Command box, 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. Also add a new parameter by typing a
    comma and then a number one (“,1”) at the end of the parameter list for the stored
    procedure sp_BackupAllFull. Adding this parameter enables an automatic full backup
    after a backup failure. Click OK when finished.

    6. In the Job step list, click MarkAndBackupLog, and then click Edit.

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

    8. In the Job step list, click Clear Backup History, and then click Edit.

    9. On the General page, in the Command box, change DaysToKeep=<number> to the
    number of days (default is 14) you want to keep the backup history, and then click OK
    twice to close the Job Properties dialog box.

    Change the backup schedule for MarkAndBackupLogSched if desired and then right-click
    the Backup BizTalk Server SQL Agent job and select Enable. The default schedule is to perform
    a log backup every 15 minutes.

    Once the Backup BizTalk Server job is configured and enabled, right-click and select Start
    Job to test. Click F5 to refresh the status on the Jobs node. If the result is not successful, check
    the following:

    • Verify that the destination folder exists and is reachable if a UNC share.
    • Check that the job owner has permissions on the destination folder.
    • Ensure that linked servers are configured properly if BizTalk databases are present in multiple SQL Server database instances.

    Abdul Rafay http://abdulrafaysbiztalk.wordpress.com/ Please mark this answer if it helps
    Friday, August 28, 2009 10:37 AM
  • Thank You Abdul,

    Is the above is true even if the BizTalk databases are on different instances on the same server 'Server A'. Please see the below

    ServerA\INS1 has BizTalkMsgBoxDb database

    ServerA\INS2 has BizTalkDTADb database

    ServerA\INS3 has BizTalkMgmtDb and SSODB databases

    We have Backup BizTalk Server (BizTalkMgmtDb) on ServerA\INS3 instance. If I enable this job & set the parameters in the job step, will it backup the other databases which are on other instances ServerA\INS2, ServerA\INS1??

    Thanks
    Friday, August 28, 2009 6:39 PM
  • Yes the above is true if you have multiple instances.
    Abdul Rafay http://abdulrafaysbiztalk.wordpress.com/ Please mark this answer if it helps
    Saturday, August 29, 2009 8:48 AM
  • Hi,

    I have successfully configured the Backup BizTalk Server (BizTalkMgmtDb) and all the databases from 3 instances are backing up. When I ran the Job, Full backup is performed & then Log backup backup is running every 15 mins.

    But I deleted the full backups and I want run a full backup again. But whenever I run this job only log backups are happening.

    What should I do, to run the full backup again?

    please help me out..
    Monday, August 31, 2009 7:17 PM
  • The Backup job has three steps. The full backup is step one. run the job from step 1. That will give you the full back up.
    Tuesday, September 1, 2009 2:25 AM
  • In the job step list did you configure 5 correctly? If yes it should do an automatic full backup.
    Abdul Rafay http://abdulrafaysbiztalk.wordpress.com/ Please mark this answer if it helps
    Tuesday, September 1, 2009 9:02 AM
  • Hi All,

    I want to know that does the Backup BizTalk Server job only takes a backup of the transactional logs or it shrinks the logs too from the local drive?

    If it does not shrink the logs then eventually the logs would use up all the space of the local drive so, do we have to shrink the logs on a regular basis?

    Thursday, January 16, 2014 10:09 AM
  • Ideally you should start a FRESH thread (people typically do not look at updated ANSWERED Threads).

    As part of the BizTalk Backup, once every 24 hours a complete Backup is taken and placed in the configured folders. This can be the restore point in case you delete the intermediate shipped logs. Also, from the BizTalk Tables created, one can find out the status of the applied log and then delete them.

    Regards.

    Thursday, January 16, 2014 10:16 AM