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

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

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

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

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

Yes the above is true if you have multiple instances.

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

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.

In the job step list did you configure 5 correctly? If yes it should do an automatic full backup.

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?

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.