How to Move the BizTalk Server Databases
You can use this procedure to move the primary BizTalk Server databases to another server. This same basic procedure can also be used to move the BizTalk Server databases from a local SQL Server to a remote SQL Server or to a SQL Server cluster.
Sign in with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.
Stop all BizTalk Server services. For more information, see Restart BizTalk Server Services, and shut down BizTalk Server.
It is critical to make sure that all the BizTalk Server services and jobs are stopped before you move the databases.
Stop the IIS service.
Stop the SQL Server Agent service.
Back up the BizTalk databases by following the database backup procedures as described at Backing Up and Restoring the BizTalk Server Databases.
Restore the BizTalk databases on the new server following the database restore procedures at How to Restore Your Databases.
Script the SQL Server Agent jobs listed below for transfer to the new server, as described at How to Back Up and Restore SQL Agent Jobs. Run each of the scripts on the new server to recreate the jobs.
Run each of the scripts on the new server to recreate the jobs. Certain jobs, such as the Backup BizTalk Server (BizTalkMsgBoxDb) job, will have to be reconfigured unless the new server file paths and server name are identical to the old server.
You can also use the SSIS/DTS Transfer Jobs task to move the jobs to the new server, but most users will probably find it easier to script the jobs using SQL Management Studio.
In addition to scripting SQL Server Agent jobs as described in the previous step, you must also script logins as described in How to Back Up and Restore SQL Server Logins. These logins need to be restored on the destination server.
Restore the BizTalk Server databases by following steps 9 through 22 in How to Restore Your Databases. This procedure updates the BizTalk Management (BizTalkMgmtDb) database and registry with the new location of the BizTalk databases.
In the SampleUpdateInfo.xml file, comment out all of the databases except for those you have moved to the new server.