Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

How to Restore Databases in the Backup BizTalk Server Job

This section covers the steps to bring online the databases in the BizTalk group that are backed up by the Backup BizTalk Server job. By default, all databases are backed up by using the Backup BizTalk Server job except for the BAM databases. See Restoring Analysis Services and Supporting Databases for more information about backup and restore of the BAM databases. You must restore all databases to the same mark to ensure a consistent transactional state among the databases. For more information, see Marked Transactions, Full Backups, and Log Backups.

If there is only one server in the destination system, make sure that all of the log backup sets (except for the most recent set) have been restored. For more information, see Viewing the History of Restored Backups. If all the log backup sets have not been restored, and the restore job is not currently running, run the restore job (manually if necessary). If there are outstanding backup sets that can be restored, the job will process them until they are all restored.

If there are multiple servers in the destination system, all servers must be restored to the same backup set. You must view the restore history on each server and make sure that the most recent log backup set restored is the same on all servers. If it is not, you must manually run the restore job on each server that needs the most recent log backup set restored. After all of the servers are on the same backup set, the final set can be manually restored.

The adm_BackupHistory table is the central history point for the log shipping process for the source system. All backup work performed is recorded to this table. All servers in your destination system read from this table to receive the information needed to perform their restore work.

Cc296741.note(en-US,BTS.10).gifNote
If you restore the BAM Primary Import database from a backup, then you should also restore the BAM Archive, BAM Star Schema, and BAM Analysis databases by using a backup older than the BAM Primary backup. For more information, see Backing Up the BAM Analysis and Tracking Analysis Server Databases.

Cc296741.note(en-US,BTS.10).gifNote
If you move the full or log backups for a source database from the location in which the Backup BizTalk Server job put them, you should update the associated row for that database in the bts_LogShippingDatabases table on the destination system by setting the LogFileLocation or DBFileLocation to the new location where the destination system should read the full/log backup files. This table is populated when you run the bts_ConfigureBtsLogShipping stored procedure. By default, these columns are set to null, which indicates that the destination system should read the backup files from the location stored in the adm_BackupHistory table.

Cc296741.Important(en-US,BTS.10).gifImportant
Always keep a copy of your backup files in a secure location. Even if you have log backups, you cannot restore your databases without the backup files.

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

  1. On the computer or computers you have identified as the destination system, click Start, click Programs, click Microsoft SQL Server, and then click Enterprise Manager. Open the appropriate server by clicking it.

  2. Wait for all restore jobs to complete on SQL Server instances at the disaster recovery site. This can be confirmed by reviewing the Master.dbo.bts_LogShippingHistory table that is populated by the Get Backup History job. When a backup is successfully restored, the Restored column is set to a value of 1 and the RestoreDateTime is set to the date and time the restore was completed. When all of the databases that are part of the backup set have been successfully restored, the backup set ID is written to the Master.dbo.bts_LogShippingLastRestoreSet table.

  3. Double-click Management, double-click SQL Server Agent, and then click Jobs. In the details pane, right-click BTS Log Shipping - Get Backup History, and then click Disable Job.

  4. In the details pane, right-click BTS Log Shipping - Restore Databases, and then click Disable Job.

  5. In the details pane, right-click BTS Log Shipping - Restore To Mark, and then click Start Job.

    Cc296741.note(en-US,BTS.10).gifNote
    These SQL Server Agent jobs are described in the topic How to Configure the Destination System.

    SQL Server Agent jobs and BizTalk Server databases are restored to the destination system.

  6. On the computer running BizTalk Server 2006, where you edited the SampleUpdateInfo.xml file, open a command prompt. Click Start, click Run, type cmd and then click OK.

  7. Navigate to the following directory: %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\Restore.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, browse to the following folder: %SystemRoot%\Program Files (x86)\Microsoft BizTalk Server 2006\Bins32\Schema\Restore.

  8. At the command prompt, type:

    cscript UpdateDatabase.vbs SampleUpdateInfo.xml

    This script updates all tables that store information about the location of other databases.

    Cc296741.note(en-US,BTS.10).gifNote
    You only need to run UpdateDatabase.vbs on one server in the BizTalk group.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, you must run UpdateDatabase.vbs from a 64-bit command prompt.

  9. Copy the edited SampleUpdateInfo.xml file to the %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\Restore directory on every computer running BizTalk Server 2006 that is part of the BizTalk group.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, browse to the following folder: %SystemRoot%\Program Files (x86)\Microsoft BizTalk Server 2006\Bins32\Schema\Restore.

  10. On each computer in the BizTalk group, open a command prompt. Click Start, click Run, type cmd and then click OK.

  11. Navigate to the following directory: %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\Restore.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, browse to the following folder: %SystemRoot%\Program Files (x86)\Microsoft BizTalk Server 2006\Bins32\Schema\Restore.

  12. At the command prompt, type:

    cscript UpdateRegistry.vbs SampleUpdateInfo.xml

    This script updates all registry entries that store information about the location of other databases.

    Cc296741.note(en-US,BTS.10).gifNote
    You need to run UpdateRegistry.vbs on every server in the BizTalk group.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, you must run UpdateRegistry.vbs from a 64-bit command prompt.

  13. Restart all of the BizTalk Server services. For more information about how to restart the BizTalk Server services, see "How to Start, Stop, Pause, Resume, or Restart BizTalk Server Services" in BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkID=106445.

  14. After restoring your databases, you must restart the Windows Management Instrumentation service. Click Start, click Run, type services.msc, and then click OK. Right-click Windows Management Instrumentation, and then click Restart.

  15. On the computer you use to administer BizTalk Server, click Start, click Programs, click Microsoft BizTalk Server 2006, and then click BizTalk Server Administration.

  16. In the console tree, right-click the BizTalk Group, and then click Remove.

  17. In the console tree, right-click BizTalk Server 2006 Administration, and then click Connect to Existing Group.

  18. In the Connect to Existing BizTalk Server Configuration Database dialog box, in the SQL Server name drop-down list box, select the name of the Microsoft SQL Server instance that hosts the BizTalk Management database. When you select the instance of SQL Server, BizTalk Server automatically attempts to detect BizTalk Server databases on that computer.

  19. In the Database name drop-down list box, select the BizTalk Management database (BizTalkMgmtDb) to which you want to connect, and then click OK.

    The BizTalk Server Administration console adds the BizTalk group to the console tree.

    Your BizTalk server is now restored and should be running. You should now configure the Backup BizTalk Server job to start writing backups to a new destination server. You should also reconfigure a new destination system.

Cc296741.Important(en-US,BTS.10).gifImportant
If you are using the Rule Engine, after restoring the databases, you must restart the Rule Engine Update Service on every server in the BizTalk group. For more information about how to restart the Rule Engine Update Service, see "How to Start, Stop, Pause, Resume, or Restart BizTalk Server Services" in BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkID=106445.

Cc296741.note(en-US,BTS.10).gifNote
If you are using BAS or BAM, this is the time to restore the BAS and BAM databases. For more information, see Restoring BAS and Restoring Databases Not Included in the Backup BizTalk Server Job.

  1. On the computer or computers that you have identified as the destination system, 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 computer on the destination system, and then click Connect to connect to the appropriate SQL Server computer.

  3. Wait for all restore jobs to complete on SQL Server instances at the disaster recovery site. This can be confirmed by reviewing the Master.dbo.bts_LogShippingHistory table that is populated by the Get Backup History job. When a backup is successfully restored, the Restored column is set to a value of 1 and the RestoreDateTime is set to the date and time the restore was completed. When all of the databases that are part of the backup set have been successfully restored, the backup set ID is written to the Master.dbo.bts_LogShippingLastRestoreSet table.

  4. In Microsoft SQL Server Management Studio, double-click the appropriate server, double-click SQL Server Agent, and then double-click Jobs.

  5. In the details pane, right-click BTS Log Shipping - Get Backup History, and then click Disable.

    In the Disable Jobs dialog box, the status changes to Success.

  6. In the details pane, right-click BTS Log Shipping - Restore Databases, and then click Disable.

    In the Disable Jobs dialog box, the status changes to Success.

  7. In the details pane, right-click BTS Log Shipping - Restore To Mark, and then click Start Job.

    SQL Server Agent jobs and BizTalk Server databases are restored to the destination system.

  8. On the computer running BizTalk Server 2006, where you edited the SampleUpdateInfo.xml file, open a command prompt. Click Start, click Run, type cmd and then click OK.

  9. Navigate to the following directory: %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\Restore.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, browse to the following folder: %SystemRoot%\Program Files (x86)\Microsoft BizTalk Server 2006\Bins32\Schema\Restore.

  10. At the command prompt, type:

    cscript UpdateDatabase.vbs SampleUpdateInfo.xml

    This script updates all tables that store information about the location of other databases.

    Cc296741.note(en-US,BTS.10).gifNote
    You only need to run UpdateDatabase.vbs on one server in the BizTalk group.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, you must run UpdateDatabase.vbs from a 64-bit command prompt.

  11. Copy the edited SampleUpdateInfo.xml file to the %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\Restore directory on every computer running BizTalk Server 2006 that is part of the BizTalk group.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, browse to the following folder: %SystemRoot%\Program Files (x86)\Microsoft BizTalk Server 2006\Bins32\Schema\Restore.

  12. On each computer in the BizTalk group, open a command prompt. Click Start, click Run, type cmd and then click OK.

  13. Navigate to the following directory: %SystemRoot%\Program Files\Microsoft BizTalk Server 2006\Schema\Restore.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, browse to the following folder: %SystemRoot%\Program Files (x86)\Microsoft BizTalk Server 2006\Bins32\Schema\Restore.

  14. At the command prompt, type:

    cscript UpdateRegistry.vbs SampleUpdateInfo.xml

    This script updates all registry entries that store information about the location of other databases.

    Cc296741.note(en-US,BTS.10).gifNote
    You need to run UpdateRegistry.vbs on every server in the BizTalk group.

    Cc296741.note(en-US,BTS.10).gifNote
    On 64-bit computers, you must run UpdateRegistry.vbs from a 64-bit command prompt.

  15. Restart all of the BizTalk Server services. For more information about how to restart the BizTalk Server services, see "How To Start, Stop, Pause, Resume, or Restart BizTalk Server Services" in BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkID=106445.

  16. After restoring your databases, you must restart the Windows Management Instrumentation service. Click Start, click Run, type services.msc, and then click OK. Right-click Windows Management Instrumentation, and then click Restart.

  17. On the computer you use to administer BizTalk Server, click Start, click Programs, click Microsoft BizTalk Server 2006, and then click BizTalk Server Administration.

  18. In the console tree, right-click the BizTalk Group, and then click Remove.

  19. In the console tree, right-click BizTalk Server 2006 Administration, and then click Connect to Existing Group.

  20. In the Connect to Existing BizTalk Server Configuration Database dialog box, in the SQL Server name drop-down list box, select the name of the Microsoft SQL Server instance that hosts the BizTalk Management database. When you select the instance of SQL Server, BizTalk Server automatically attempts to detect BizTalk Server databases on that computer.

  21. In the Database name drop-down list box, select the BizTalk Management database (BizTalkMgmtDb) to which you want to connect, and then click OK.

    The BizTalk Server Administration console adds the BizTalk group to the console tree.

    Your BizTalk server is now restored and should be running. You should now configure the Backup BizTalk Server job to start writing backups to a new destination server. You should also reconfigure a new destination system.

Cc296741.Important(en-US,BTS.10).gifImportant
If you are using the Rule Engine, after restoring the databases, you must restart the Rule Engine Update Service on every server in the BizTalk group. For more information about how to restart the Rule Engine Update Service, see "How to Start, Stop, Pause, Resume, or Restart BizTalk Server Services" in BizTalk Server 2006 R2 Help at http://go.microsoft.com/fwlink/?LinkID=106445.

Cc296741.note(en-US,BTS.10).gifNote
If you are using BAS or BAM, this is the time to restore the BAS and BAM databases. For more information, see "Backing Up and Restoring BAS" and "Backing Up and Restoring BAM" in BizTalk Server 2006 Help at http://go.microsoft.com/fwlink/?LinkID=104582 and http://go.microsoft.com/fwlink/?LinkID=104581.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.