How to Restore Your Databases

How to Restore Your Databases

Collapse All

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.

Note
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 and Restoring BAM.

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

Note

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.

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

Prerequisites

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

To restore your databases (SQL Server 2008)

On the computer or computers that you have identified as the destination system, click Start, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio.
In the Connect to Server dialog box, specify the name of the SQL Server on the destination system, and then click Connect to connect to the appropriate SQL Server.
In Microsoft SQL Server Management Studio, double-click the appropriate server, double-click SQL Server Agent, and then double-click Jobs.
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.
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.
In the details pane, right-click BTS Log Shipping - Restore To Mark, and then click Start Job at Step….
When the Start Job on <servername> dialog appears, click Step ID 1 (it will be selected by default) and then click Start.
The Start Job on <servername> dialog will close, leaving the Start Jobs - <servername> dialog open. This dialog will show progress and status of the running job. When the job finishes, check that the Status is Success, and then click Close.

If the Status is Error, click the link in the Message field to obtain more information about the nature of the problem.

If the job is successful, SQL Server Agent jobs and BizTalk Server databases are restored to the destination system.
On the computer running BizTalk Server, where you edited the SampleUpdateInfo.xml file, open a command prompt

On Windows Server 2003, do the following: Click Start, click Run, type cmd and then click OK.

On Windows Server 2008, do the following: Click Start. In the Start Search box, type cmd, and then press CTRL+SHIFT+ENTER. If a User Access Control dialog is displayed, verify that the action described is what you want, then click Continue.

Navigate to the following directory: drive:\Program Files\Microsoft BizTalk Server 2009\Schema\Restore.

Note
On 64-bit computers, browse to the following folder: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server 2009\Bins32\Schema\Restore.

At the command prompt, type:

cscript UpdateDatabase.vbs SampleUpdateInfo.xml

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

Note
You only need to run UpdateDatabase.vbs on one server in the BizTalk group.

Note
On 64-bit computers, you must run UpdateDatabase.vbs from a 64-bit command prompt.

Copy the edited SampleUpdateInfo.xml file to the drive:\Program Files\Microsoft BizTalk Server 2009\Schema\Restore directory on every computer running BizTalk Server that is part of the BizTalk Server group.

Note
On 64-bit computers, browse to the following folder: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server 2009\Bins32\Schema\Restore.

On each computer in the BizTalk Server group, open a command prompt as described in step 7.

Navigate to the following directory: drive:\Program Files\Microsoft BizTalk Server 2009\Schema\Restore.

Note
On 64-bit computers, browse to the following folder: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server 2009\Bins32\Schema\Restore.

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.

Note
You need to run UpdateRegistry.vbs on every server in the BizTalk group.

Note
On 64-bit computers, you must run UpdateRegistry.vbs from a 64-bit command prompt.

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.
After restoring your databases, you must restart the Windows Management Instrumentation service. Click Start, click Run, type services.msc, and then click OK. If a User Access Control dialog is displayed, verify that the action described is what you want, then click Continue. Right-click Windows Management Instrumentation, and then click Restart.
On the computer you use to administer BizTalk Server, click Start, click All Programs, click Microsoft BizTalk Server 2009, and then click BizTalk Server Administration.
In the console tree, right-click the BizTalk Group, and then click Remove.
In the console tree, right-click BizTalk Server 2009 Administration, and then click Connect to Existing Group.
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.
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.

Important
If you are using the Rules Engine, after restoring the databases, you must restart the Rule Engine Update Service on every server in the BizTalk Server 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.

Note
If you are using BAM, this is the time to restore the BAM databases. For more information, see Backing Up and Restoring BAM.

To restore your databases (SQL Server 2005)

On the computer or computers that you have identified as the destination system, click Start, click All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.
In the Connect to Server dialog box, specify the name of the SQL Server on the destination system, and then click Connect to connect to the appropriate SQL Server.
In Microsoft SQL Server Management Studio, double-click the appropriate server, double-click SQL Server Agent, and then double-click Jobs.
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.
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.
In the details pane, right-click BTS Log Shipping - Restore To Mark, and then click Start Job at Step….
When the Start Job on <servername> dialog appears, click Step ID 1 (it will be selected by default) and then click Start.
The Start Job on <servername> dialog will close, leaving the Start Jobs - <servername> dialog open. This dialog will show progress and status of the running job. When the job finishes, check that the Status is Success, and then click Close.

If the Status is Error, click the link in the Message field to obtain more information about the nature of the problem.

If the job is successful, SQL Server Agent jobs and BizTalk Server databases are restored to the destination system.

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

Note
If you are using Windows Server 2008 or Windows Vista, you will need to open the command prompt with full administrator permissions. A procedure to do this is given in step 7 of the preceding section. For additional information see http://go.microsoft.com/fwlink/?linkid=131688.

Navigate to the following directory: drive:\Program Files\Microsoft BizTalk Server 2009\Schema\Restore.

Note
On 64-bit computers, browse to the following folder: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server 2009\Bins32\Schema\Restore.

At the command prompt, type:

cscript UpdateDatabase.vbs SampleUpdateInfo.xml

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

Note
You only need to run UpdateDatabase.vbs on one server in the BizTalk group.

Note
On 64-bit computers, you must run UpdateDatabase.vbs from a 64-bit command prompt.

Note
On 64-bit computers, browse to the following folder: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server 2009\Bins32\Schema\Restore.

On each computer in the BizTalk Server group, open a command prompt as described in step 7.

Navigate to the following directory: drive:\Program Files\Microsoft BizTalk Server 2009\Schema\Restore.

Note
On 64-bit computers, browse to the following folder: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server 2009\Bins32\Schema\Restore.

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.

Note
You need to run UpdateRegistry.vbs on every server in the BizTalk group.

Note
On 64-bit computers, you must run UpdateRegistry.vbs from a 64-bit command prompt.

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.
After restoring your databases, you must restart the Windows Management Instrumentation service. Click Start, click Run, type services.msc, and then click OK. If a User Access Control dialog is displayed, verify that the action described is what you want, then click Continue. Right-click Windows Management Instrumentation, and then click Restart.
On the computer you use to administer BizTalk Server, click Start, click All Programs, click Microsoft BizTalk Server 2009, and then click BizTalk Server Administration.
In the console tree, right-click the BizTalk Group, and then click Remove.
In the console tree, right-click BizTalk Server 2009 Administration, and then click Connect to Existing Group.
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.
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.

Important
If you are using the Rules Engine, after restoring the databases, you must restart the Rule Engine Update Service on every server in the BizTalk Server 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.

Note
If you are using BAM, this is the time to restore the BAM databases. For more information, see Backing Up and Restoring BAM.

Next Steps

Backing Up and Restoring BAM

Tasks

How to Configure the Backup BizTalk Server Job
How to Configure the Destination System for Log Shipping