This process should cover how to migrate an MSDE 2000 or SQL 2000 database to a new server running SQL 2000 (I'm sure MSDE 2000, as well, but thats not anything I deal with). This would include migrating from a local SQL 2000/MSDE 2000 instance on the BlackBerry server to a remote SQL database server.
The steps can vary from situation to situation, so this will include the "Backup and Restore" procedure. You can also substitute the Backup/Restore with the "Copy MDF/LDF Files; Attach Database" scenario as well (see below at the end).
1 - Backup the database
This can be done through the GUI or through command-line:
Command-Line:
osql -E
1> BACKUP database BESMgmt TO DISK = "C:\BESMgmt_backup.bak"
2> Go
GUI:
- Open SQL Enterprise Manager and expand to Databases.
- Right-click on Databases and click on Backup Database...
- Click on Add and choose a path and filename; click OK.
- Click on the Options tab and select to "Verify backup upon completion".
Copy this .BAK file to the new database server.
2 - Export the user list from the BlackBerry server
- Open BlackBerry Manager and right-click on the server instance and click on Export List. This is optional but suggested.
3 - On the new database server, be sure that the proper SQL permissions have been added for the BES service account (per the Installation Guide).
4 - Change the database information on the BlackBerry server
Prior to this step, be sure to copy down your Client Access License, SRP Identifier, and SRP Authentication Key for the BlackBerry server, as they may be needed for an optional step
- Open BlackBerry Server Configuration.
- Click on the Database Connectivity tab and click on Change Database.
- Select Yes when prompted and change the information to point to the new backend SQL server with the Database name of BESTest.
- At this point, you can either cancel the wizard or continue on through it. If you choose the latter, the SRP Identifier and SRP Authentication Key will be needed. The database has been created on the server and thats the important part of this process.
This step creates the extra system data needed on the new SQL server from a new database installation.
4 - Login to the database server and restore the database
- Open SQL Enterprise Manager and expand to Databases.
- Right-click on Databases and click on Restore Database...
- Select From Device and click on Select Devices...
- Click on Add and navigate to the .BAK file and click OK.
In the event that the drive letters change from where the database was stored on the old server to a new drive letter for the new server (for example, D:\ to E:\), you'll need to click on Options and change the drive letters for the LOG and DATA files under "Restore database files as".
- Click back on General and type in BESMgmt for "Restore database as".
- Click OK and it should restore the database successfully.
5 - Change the database information for the BlackBerry server to point to the correct database
- Open BlackBerry Server Configuration.
- Click on the Database Connectivity tab and click on Change Database. Select Yes when prompted and change the information to point to the new backend SQL server with the Database name of BESMgmt. Go through the rest of the wizard - the SRP Identifier and SRP Authentication Key should not be needed, as it will be pulled from the existing database (if not, just use the same information from step 3).
At this point, the BlackBerry services will startup and the users will populate automatically.
The "Copy MDF/LDF Files; Attach Database" scenario would include the following in place of step 4.
4 - Copy MDF/LDF Files and attach database
- On the original database server, stop the MSSQLSERVER service (and any other SQL services that are running as well). You will also want to disable the MSSQLSERVER service.
- Navigate to your SQL DATA directory and copy the BESMgmt.MDF and BESMgmt.LDF database files to the SQL DATA directory on the new database server.
- On the new database server, open SQL Enterprise Management and expand to Databases.
- Right-click on Databases and click on Attach Database...
- Click on the ... button and navigate to the BESMgmt.MDF file and select it
- All information should be populated, so click OK and it will attach it
You should probably verify permissions as well, but they should populate correctly. I did have an issue with a separate SQL-authenticated account populating for the database but not under the permissions tab under the login under the Security area of SQL (and it wouldn't let me add it there).
If anyone has something to add to this, by all means... This *should* be correct, but please verify prior to taking it as official documentation (although my official documentation from RIM wasn't exactly correct, either... had to add 2 steps after a phone call).
Obviously, for the potential Murphy's Law occuring, leave the MDF/LDF files on the original server as well as leaving SQL/MSDE installed. You never want to leave yourself in the situation where you have to a complicated rollback procedure. Rollback would be to enable and start the MSSQLSERVER service on the old database server then change the database information in the BlackBerry Server Configuration to the old server.
For another method, you can reference the top of bfrye's post here:
http://www.blackberryforums.com/showthread.php?t=8536