Move X amount of users from one environment to another with diff SQL versions.

[Live2Ride]

We would like to accomplish the following:
Move a % of users from their current BES environment to a another BES environment.
Current environment for the users that need to be moved.
Server1 - BES v4.1.3 Server2 - SQL2000 server.

New environment that the users will be moved to.
Server1 - BES v4.1.3 Server2 - SQL2005 Enterprise Server.

Our BES admin. moved a couple users to the new BES which is still pointing to the (current environment ) SQL server. These are the only users on the new BES at this time.

When all of the accounts are moved to the new BES server, can we just backup the current SQL database and restore it to the new environment database that is running SQL2005?

Any problems with backing up a BESMgmt from SQL2000 and restoring to a BESMgmt running SQL2005?

Thanks in advance

[BESadmin]

How to move the BlackBerry Configuration Database to a new Microsoft SQL Server or instance

Doc ID : KB03112
Last Modified : 2007-08-15
Document Type : How To

Environment

• BlackBerry® Enterprise Server software version 2.1 through 4.1
• Microsoft® Exchange
• IBM® Lotus® Domino®
• Microsoft SQL Server™ 2000 Service Pack 3
• Microsoft SQL Server 2005

Procedure

The BlackBerry Enterprise Server services will be stopped during the process described in this article.

Important: Restarting the BlackBerry Enterprise Server will delay message delivery to BlackBerry devices. For more information, see KB04789.

To move the BlackBerry Configuration Database to a new Microsoft SQL Server or instance, complete the following tasks:

Note: In a Microsoft Exchange environment, complete the tasks using the BlackBerry Enterprise Server administration account unless otherwise stated.

1. Back up the BlackBerry Configuration Database.
2. Prepare the new Microsoft SQL Server.
3. Restore the BlackBerry Configuration Database.
4. Configure the BlackBerry Enterprise Server.

Task 1

To back up the BlackBerry Configuration Database, complete the steps for the appropriate environment.

Microsoft SQL Server

1. Open Enterprise Manager.
2. Right-click the BlackBerry Configuration Database (for example, BESMgmt).
3. Select Backup Database.
4. Click Add to specify the Directory Name and File Name.
5. Click OK to accept the settings.
6. Click OK again to start the backup process.
7. Close Enterprise Manager when the process is complete.

Microsoft SQL Server Desktop Engine (MSDE)

1. From a command prompt, type osql -E and press ENTER. Note: If you are logging in to a named instance of the Microsoft SQL Server, use the following command syntax to log in:

   osql -E -S <Microsoft_Server_name>\<instance_name>

2. Type the following commands in the specified order: 1> backup database <BlackBerry_Configuration_Database_name> to disk = "C:\<BlackBerry_Configuration_Database_name>.bak"
   2> go
   1> quit
3. Close the command prompt.

Task 2

To prepare the Microsoft SQL Server, complete the four steps below.

Step 1

Depending on the environment, assign the appropriate permissions.

Note: Complete this task using an account with administrator access to the new Microsoft SQL Server.

Microsoft SQL Server

1. Open Enterprise Manager.
2. Go to Microsoft Server Group\<Microsoft_Server_name>\Security.
3. Right-click Logins and select New Login.
4. On the General tab, click the double quotation marks ( "" ) button.
5. From the Global Address List, select the name of the BlackBerry Enterprise Server administration account .
6. Click Add, then click OK.
7. On the Server Roles tab, select Server Administrators and Database Creators. Note: In BlackBerry Enterprise Server software version 4.1, the System Administrator role is needed for role-based administration.
8. Close Enterprise Manager.

MSDE

1. Log in to the new Microsoft SQL Server.
2. Right-click My Computer, then click Manage.
3. Expand Local Users and Groups.
4. Select Groups, then open the Administrators group.
5. Click Add.
6. Type the name of the BlackBerry Enterprise Server administration account, then click Check Name.
7. Click OK, then close the Computer Management window.

Step 2

Depending on the environment, enable the correct server protocols.

MSDE or Microsoft SQL Server 2000

1. Click Start > Run, type svrnetcn and click OK. The Microsoft Server Network Configuration Utility opens.
2. Make sure that TCP/IP and Named Pipes are both listed as enabled protocols.
3. Close the Microsoft Server Network Configuration Utility.
4. If a protocol is enabled in step 2, restart the Microsoft SQL Services.

Microsoft SQL Server 2005 Express or Microsoft SQL Server 2005

1. Click Start > Microsoft SQL Server 2005 > Configuration Tools > Microsoft Server Configuration Manager > Microsoft Server 2005 Network Configuration.
2. Select the Microsoft SQL Server instance that will be used for the BlackBerry Configuration Database.
3. If necessary, enable the TCP/IP and Named Pipes protocols.
4. If a protocol is enabled in step 3, restart the Microsoft Services.

Step 3

Make sure the Microsoft Server Agent service is running.

Note: This service does not exist in Microsoft SQL Server 2005 Express.

1. Open the Microsoft Windows® Control Panel, then open Administrative Tools > Services.
2. Verify that the Microsoft Server Agent service is started and that its Startup type is set to Automatic.
3. Close the Services window.

Step 4

Update the BlackBerry Configuration Database schema.

Important: If BlackBerry Enterprise Server software was installed to create a blank BlackBerry Configuration Database on a new Microsoft SQL Server instance, do not perform the following task because schema has already been updated.

1. Download the installation package for the version of the BlackBerry Enterprise Server being used.
2. Extract the contents of the installation package.
3. Copy the Database folder to the new Microsoft SQL Server.
4. Go to the Database folder and open the BESMgmt.cfg file in a text editor, such as Notepad.
5. Locate the DB_NAME=BESMgmt line and make sure that the name specified matches the BlackBerry Configuration Database name.
6. If you are using a named instance of Microsoft SQL Server, change the SERVER=local line to SERVER=<Microsoft_Server_Name>\ <instance_name> and close the file. Save the changes.
7. From a command prompt, go to the Database directory.
8. Type createdb.exe BESMgmt.cfg and press ENTER.
9. After the command has finished executing, close the command prompt.

Task 3

Depending on the environment, use one of the methods below to restore the BlackBerry Configuration Database.

BlackBerry Configuration Database on a Microsoft SQL Server with the same directory structure

1. Copy the BlackBerry Configuration Database backup to the C:\ path of the new Microsoft SQL Server.
2. From a command prompt, type osql -E and press ENTER. Note: When logging in to a named instance of the Microsoft SQL Server, use the following command:

   osql -E -S <SQL_Server_name>\<instance_name>

3. Type the following commands in the specified order: 1> restore database <BlackBerry_Configuration_Database_name> from disk = "c:\<BlackBerry_Configuration_Database_name>.bak" with recovery,replace
   2> go
   1> quit
4. Close the command prompt.

BlackBerry Configuration Database on a Microsoft SQL Server with a different directory structure

1. Copy the BlackBerry Configuration Database backup to the C:\ path of the new Microsoft SQL Server.
2. From a command prompt, type osql -E and press ENTER. Note: When logging in to a named instance of the Microsoft SQL Server, use the following command:

   osql -E -S <SQL_Server_name>\<instance_name>

3. Type the following commands in the specified order: 1> restore database <BlackBerry_Configuration_Database_name> from disk = "C:\<BlackBerry_Configuration_Database_name>.bak" with move "<BlackBerry_Configuration_Database_name>" to "<new_path>\MSSQL\Data\<BlackBerry_Configuration_Database_name>.mdf", move "<BlackBerry_Configuration_Database_name>_log" to "<new_path>\MSSQL\Data\<BlackBerry_Configuration_Database_name>.ldf"
   2> go
   1> quit
4. Close the command prompt.

Detach and re-attach the BlackBerry Configuration Database

Depending on the environment, complete one of the procedures below.

Microsoft SQL Server

1. Open the Control Panel, then open Administrative Tools > Services.
2. Stop each service that has a name that begins with BlackBerry.
3. Log in to the existing Microsoft SQL Server.
4. Open Enterprise Manager.
5. Right-click the BlackBerry Configuration Database name.
6. Select All Tasks, then click Detach Database.
7. Close Enterprise Manager.
8. Go to the Microsoft SQL Server Data directory and copy the MDF and LDF files. Note: The default path is C:\Program Files\Microsoft SQL Server\MSSQL\Data\ and the default filenames are BESMgmt.mdf and BESMgmt.ldf.
9. Move the file copies to the new Microsoft SQL Server.
10. Log in to the new Microsoft SQL Server.
11. Open Enterprise Manager.
12. Right-click the BlackBerry Configuration Database name.
13. Select All Tasks, then click Detach Database.
14. Move the files that were copied in step 8 to the Microsoft SQL Server Data directory. When prompted to overwrite the files, click Yes.
15. In Enterprise Manager, right-click the Database folder, then click All Tasks.
16. Select Attach Database.
17. Select the MDF file to attach, then click OK.
18. Close Enterprise Manager.

MSDE

1. Open the Control Panel, then open Administrative Tools > Services.
2. Stop each service that has a name that begins with BlackBerry.
3. Log in to the existing MSDE Server.
4. From a command prompt, type osql -E and press ENTER. Note: When logging in to a named instance of the Microsoft SQL Server, use the following command:

   osql -E -S <SQL_Server_name>\<instance_name>

5. Type the following commands in the specified order: 1> exec sp_detach_db @dbname = "<BlackBerry_Configuration_Database_name>"
   2> go
   1> quit
6. Go to the Microsoft SQL Server Data directory and copy the MDF and LDF files. Note: The default path is C:\Program Files\Microsoft SQL Server\MSSQL\Data\ and the default filenames are BESMgmt.mdf and BESMgmt.ldf.
7. Move the files that were copied in step 6 to the new Microsoft SQL Server.
8. Log in to the new MSDE Server.
9. From a command prompt, type osql -E and press ENTER. Note: When logging in to a named instance of Microsoft SQL Server, use the following command:

   osql -E -S <SQL_Server_name>\<instance_name>

10. Type the following commands in the specified order: 1> exec sp_detach_db @dbname = "<BlackBerry_Configuration_Database_name>"
    2> go
    2> quit
11. Move the files that were copied in step 6 into the Microsoft SQL Server Data directory. When prompted to overwrite the files, click Yes.
12. From a command prompt, type osql -E and press ENTER. Note: If you are logging in to a named instance of Microsoft Server, use the following command syntax to log in:

    osql -E -S <SQL_Server_name>\<instance_name>

13. Type the following commands in the specified order: 1> exec sp_attach_db @dbname = "<BlackBerry_Configuration_Database_name>",
    2> @filename1 = "C:\Program Files\Microsoft SQL Server\MSSQL\Data\<BlackBerry_Configuration_Database_name>.mdf",
    3> @filename2 = "C:\Program Files\Microsoft SQL Server\MSSQL\Data\<BlackBerry_Configuration_Database_name>.ldf"
    4> go
    1> quit
14. Close the command prompt.

Task 4

To configure the BlackBerry Enterprise Server to connect to the new BlackBerry Configuration Database, complete the following steps:

1. On the BlackBerry Enterprise Server, click Start > Programs > BlackBerry Enterprise Server > BlackBerry Server Configuration.
2. On the Database Connectivity tab, select Change Database.
3. In the Change Database Wizard, specify the new Microsoft SQL Server name and the existing BlackBerry Configuration Database name.
4. Continue with the wizard. Make sure that the Start Services check box is selected, then click Finish.
5. Open Administrative Tools > Services and make sure all the BlackBerry Enterprise Server services started successfully. Note: The BlackBerry Database Consistency Service may be disabled. This is normal behavior.
6. Open BlackBerry Manager and make sure that all BlackBerry device users display a running status.
7. Test message flow from the BlackBerry device.
8. Close the command prompt.

For BlackBerry Enterprise Server software version 4.1, install the database notification system.

1. Copy the RimEsp.dll file to the C:\Program Files\Microsoft SQL Server\MSSQL\Binn folder on the new Microsoft SQL Server.
2. In Microsoft Query Analyzer, go to the Database\DBInstallScripts\SQLServer\4.1 folder on the installation media.
3. Open the NotifyInstall.sql file and replace <database_name> with the name of the BlackBerry Configuration Database.
4. Run the script.
5. Confirm that the Microsoft Query Analyzer displays the following message:

   Function dbo.xp_RIM_xxxx registered.

Additional Info

When moving from a MSDE server instance to a full Microsoft SQL Server on a separate computer, see KB04039 for information on how to increase the mail agent limitation on the BlackBerry Enterprise Server.

[Live2Ride]

Thanks for the reply!

When I try to do a test restore on another systsem I am getting this text back from the osql statement:

Msg 3154, Level 16, State 4, Server XXXX, Line 1
The backup set holds a backup of a database other than the existing
'XXXXXgmt' database.
Msg 3013, Level 16, State 1, Server XXXX, Line 1
RESTORE DATABASE is terminating abnormally.

Any Ideas I have followed the steps in the document and have put in the real name of the database where required?

OK, per research I found that this happens when a database exists with the same name. I deleted the database and now get another message:

Msg 3234, Level 16, State 2, Server XXXX, Line 1
Logical file 'BESMgmt' is not part of database 'BESMgmt'. Use RESTORE
FILELISTONLY to list the logical file names.

Presently searching for the fix

Thanks again for your help.

[BESadmin]

In the Options tab, check "Force Restore over existing database".