Step-by-Step Guide to Migrate and Move Bopup Communication Server Database to a New Microsoft SQL Server

---

This article discusses and provides information on how to move Bopup Communication Server database and migrate to a new Microsoft SQL Server. The article contains a step-by-step guide on how to create a backup of existing database of Bopup Communication Server and restore it on a new SQL server.

This guide can be used in the following cases:

• On migrating from a built-in MSDE 2000 or Microsoft SQL Server 2005 Express (that can be installed as a part of Bopup Communication Server) to a new Microsoft SQL Server software;
• On reinstalling Bopup Communication Server on the same/another computer and restoring it's existing data;
• On moving existing database of Bopup Communication Server to another Microsoft SQL Server;

This guide uses OSQL.EXE utility, a command-line executable file that is a installed as a part of Microsoft SQL Server. It is used to run SQL switches and queries directly from a command-line interface. This guide requires you to have intermediate skills in a computer science and to understand the following definitions: Microsoft SQL Server, SQL database, SQL instance name, Windows Authentication, SQL Login mode.

You can also modify and use our batch files to backup and restore SQL database. Refer our FAQ section at How to backup and restore the communication server database? for details.

To display more information on available and supported command-line switches launch OSQL.EXE utility with the '/?' switch.

Creating a backup of the SQL database

In order to move your existing database of the communication server your must make it's backup first. You can create a backup remotely from another computer or locally on a computer which is running Microsoft SQL Server.

Locate a folder where the OSQL.EXE file is located. By default, that directory is located at "<DRIVE>\Program Files\Microsoft SQL Server\90\Tools\Binn\" path. To make a backup of an existing database of Bopup Communication Server run an appropriate query from a command line interface listed below. Please understand that built-in SQL Server is installed with BCS SQL instance name so you should specify this instance within the name of your server in a SQL query.

The SQL query format is the following, depending on a connection security mode, trusted (Windows Authentication) or login ID/password (SQL Login Mode):

<PATH>\osql.exe -S <COMPUTER>\<SQL-INSTANCE> -E -Q " BACKUP DATABASE bcs40 TO DISK = '<PATH-TO-BACKUP-FILE-TO-CREATE>' "

or

<PATH>\osql.exe -S <COMPUTER>\<SQL-INSTANCE> -U <LOGIN> -P <PASSWORD> -Q " BACKUP DATABASE bcs40 TO DISK = '<PATH-TO-BACKUP-FILE-TO-CREATE>' "


Examples:

If you have the built-in SQL Server that was installed with Bopup Communication Server and running the OSQL.EXE under the same user account in Windows under which you previously installed Bopup Communication Server then run the following command:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -S SERV1\BCS -E -Q " BACKUP DATABASE bcs40 TO DISK = 'C:\backup_bcs.bak' "

This example connects to SERV1 computer with running BCS instance name of the SQL server, uses trusted connection and saves the backup into backup_bcs.bak file.


If you have the built-in SQL Server that was installed with Bopup Communication Server and running the OSQL.EXE remotely from another computer you should also specify login ID and password to connect to the SQL server instead of the '-E' switch (that uses trusted connection). For the built-in SQL Server running remotely you can use 'sa' login ID with a blank password.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -S SERV2\BCS -U sa -P -Q " BACKUP DATABASE bcs40 TO DISK = 'C:\backup_bcs.bak' "

This connects to SERV2 computer with running BCS instance name of the SQL server using sa login ID and saves the backup into backup_bcs.bak file.


If you installed Microsoft SQL Server 2005 Express Edition using our guide on it's installing at Step-by-Step Guide to Install Microsoft SQL Server 2005/2008/2012 Express Edition as a Host Database Server for Bopup Communication Server then you should remember which authentication mode and SQL instance name you chose. If you installed the SQL server with it's default configuration options then you should specify the SQLEXPESS instance name while connecting to it.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -S SERV3 -U admin -P qwerty -Q " BACKUP DATABASE bcs40 TO DISK = 'C:\backup_bcs.bak' "

Example that connects to SERV3 computer with no instance name of the SQL server using the admin login ID, qwerty password and saves the backup into backup_bcs.bak file.


In case if you have Bopup Communication Server and use existing SQL server then you should know which authentication mode is enabled on that SQL server (Windows Authentication or SQL Mode) and/or have a valid SQL account to connect to the server.

You can also make a backup of the database using GUI console on your SQL server. For more information please apply to your SQL server help and documentation.

Restoring a backup of the SQL database

Procedure of restoring the SQL backup is the same like creating. If you have installed Microsoft SQL Server or have a clear installation of Bopup Communication Server with the built-in SQL Server then you can restore previously saved backup of the database. Locate the OSQL.EXE utility on your server computer with running SQL server then use the following queries and switches to restore the backup.

The SQL query format is the following, depending on a connection security mode, trusted (Windows Authentication) or login ID/password (SQL Login Mode):

<PATH>\osql.exe -S <COMPUTER>\<SQL-INSTANCE> -E -Q " RESTORE DATABASE bcs40 FROM DISK = '<PATH-TO-BACKUP-FILE-TO-RESTORE>' "

or

<PATH>\osql.exe -S <COMPUTER>\<SQL-INSTANCE> -U <LOGIN> -P <PASSWORD> -Q " RESTORE DATABASE bcs40 FROM DISK = '<PATH-TO-BACKUP-FILE-TO-RESTORE>' "


Examples:

If you have the built-in SQL Server that has been installed with Bopup Communication Server and running the OSQL.EXE under the same user account in Windows under which you have installed Bopup Communication Server then run the following command:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -S SERV1\BCS -E -Q " RESTORE DATABASE bcs40 FROM DISK = 'C:\backup_bcs.bak' "

This example connects to SERV1 computer with running BCS instance name of the SQL server, uses trusted connection and restores the backup_bcs.bak backup file from a given path.


If you have the built-in SQL Server that has been installed with Bopup Communication Server and running the OSQL.EXE remotely from another computer you should also specify login ID and password to connect to the SQL server instead of the '-E' switch (that uses trusted connection). For the built-in SQL Server running remotely you can use 'sa' login ID with a blank password.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -S SERV2\BCS -U sa -P -Q " RESTORE DATABASE bcs40 FROM DISK = 'C:\backup_bcs.bak' "

This connects to SERV2 computer with running BCS instance name of the SQL server using sa login ID and restores the backup from backup_bcs.bak file.


If you have installed Microsoft SQL Server 2005/2008/2012 Express Edition using our guide on it's installing then you should remember which authentication mode and SQL instance name you have chosen. And if the SQL server has it's default configuration options then you should specify the 'SQLEXPESS' instance name while connecting to it.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -S SERV3 -U admin -P qwerty -Q " RESTORE DATABASE bcs40 FROM DISK = 'C:\backup_bcs.bak' "

Example that connects to SERV3 computer with no instance name of the SQL server using the admin login ID, qwerty password and restores the backup from backup_bcs.bak file.


You can also restore a backup of the database using GUI console on your SQL server. For more information please refer to your SQL server help and documentation.

Adding user/login credentials on a new SQL server

If you have restored the backup of the database on the SQL server that was not previously used to host a Bopup Communication Server database and your SQL server uses SQL Login authentication mode (Mixed Mode) then you should create a new user login ID on the SQL server to allow to Bopup Communication Server to access it's own database.

If you have just only reinstalled Bopup Communication Server and it uses the built-in or an existing SQL server then you do not need to create that account because the setup wizard of the communication server has already created the 'bcsadmin' account on the SQL server.

If your new SQL server uses Mixed Mode to authorize users then Bopup Communication Server connects to the SQL server using the 'bcsadmin' login ID with the 'starmessage' password. Otherwise if the SQL server has Windows Athentication Mode to autorize and handle incoming connections you do not need to create the 'bcsadmin' account because the server uses current Windows credentials under which connection has been established.


The SQL query format to create 'bcsadmin' SQL account is the following, depending on a connection security mode, trusted (Windows Authentication) or login ID/password (SQL Login Mode):

<PATH>\osql.exe -S <COMPUTER>\<SQL-INSTANCE> -E -Q " USE bcs40; EXEC sp_addlogin 'bcsadmin', 'starmessage', 'bcs40'; EXEC sp_changedbowner 'bcsadmin'; CREATE USER [bcsadmin] for login [bcsadmin] with default schema = dbo; "

or

<PATH>\osql.exe -S <COMPUTER>\<SQL-INSTANCE> -U <LOGIN> -P <PASSWORD> -Q " USE bcs40; EXEC sp_addlogin 'bcsadmin', 'starmessage', 'bcs40'; EXEC sp_changedbowner 'bcsadmin'; CREATE USER [bcsadmin] for login [bcsadmin] with default schema = dbo; "


Examples:

If you create the 'bcsadmin' account on a Microsoft SQL Server and that server was not previously used to host a Bopup Communication Server database then you should know which authentication mode and instance name (if present) are used on the SQL server to specify login credentials.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe" -S SERV1 -U admin -P qwerty -Q " USE bcs40; EXEC sp_addlogin 'bcsadmin', 'starmessage', 'bcs40'; EXEC sp_changedbowner 'bcsadmin'; CREATE USER [bcsadmin] for login [bcsadmin] with default schema = dbo; "

Example that connects to SERV1 computer with no instance name of the SQL server using the admin login ID, qwerty password and adds new user with the bcsadmin name and starmessage password as an owner of the bcs40 database.


You can also create user on the SQL Server using the server's GUI management tools. For more information please refer to your SQL server help and documentation.

Modifying connection string of Bopup Communication Server

Bopup Communication Server uses a special text value in System Registry called ConnectionString to connect and access it's own database on a SQL server. That value contains a name/address of SQL server and tells the communication server which authentication mode to use.

If you have just moved the SQL database and have not reinstalled Bopup Communication Server then you should modify the ConnectionString text value in the registry. Otherwise if you have installed a new copy of the communication server on the same/another computer do nothing because the setup wizard has already done all this.

After restoring the backup of your SQL database and adding/creating the 'bcsadmin' user account (if needed) you should edit and change the ConnectionString property in the System Registry that is used by Bopup Communication Server.

Start the Registry Editor from "Start -> Run" menu by typing "regedit" command. Then navigate to the "HKEY_LOCAL_MACHINE\Software\B Labs\Bopup Communication Server" key. You must edit the ConnectionString text value in this registry key.

The ConnectionString value has the following format depending on an authentication mode on the SQL server.

Mixed Mode:
Provider='sqloledb';Data Source='<COMPUTER>\<SQL-INSTANCE>';Initial Catalog='bcs40'

Windows Authentication Mode:
Provider='sqloledb';Data Source='<COMPUTER>\<SQL-INSTANCE>';Initial Catalog='bcs40';Integrated Security='SSPI'


If you move the SQL database to Microsoft SQL Server running on another computer then you must edit the value according to the name/IP address of the remote computer and instance name of the SQL server. Add the Integrated Security='SSPI' substring if the new SQL server enables Windows Authentication Mode or delete it from the string if you will use Mixed SQL Mode. Add an instance name of your new SQL server after the computer name with the '\' (slash) sign. Or remove previous instance name from the address if the new SQL server is running with no instance name.


When you have completed all the modifications run the Management Console of installed Bopup Communication Server. You should see no error and warning messages on startup and your previous database with all settings/users/groups/etc will be shown.