How to Convert a BEIMS MS Access Database to a MS SQL Server, MSDE Or Oracle Database

How to Move a SQL Server or MSDE Database from One Server to Another Server

SUMMARY

This article will explain how to move an existing MS SQL or MSDE BEIMS® database from one server to another Microsoft® SQL Server. There are many ways of doing this but the most successful method we use involves backing up the database, copying the backup to the other server and then restore it. A script then needs to be run to re-create the permissions and the security logins for BEIMS®.

DETAILED INFORMATION

It is assumed that the database application is already installed.

1. Shut down BEIMS® so no data can be entered or modified while the database is being. Refer to How to Shutdown and Activate BEIMS® for instructions on how to do this.

1. Create a backup of the BEIMS® database. For MSDE users, use the MSDE Administration application found in Start-> Program Files->BEIMS®->Tools->MSDE Administration on the PC with MSDE installed to backup the BEIMS® database.

1. Copy the backed up database to the MS SQL Server that it is going to be restored on.

1. Using Enterprise Manager, right mouse click Databases and select All Tasks -> Restore Database.

1. Type the name of the database as BEIMS® and select From Device. Click Select Devices.

1. Click on Add. Select the BEIMS® backup file to restore and click OK and OK again.

1. Click on the Options tab and make sure the Move to physical file path is correct for the BEIMS_Data and BEIMS_Log Logical file names. By default, the paths are the same as the previous server. This path may or may not exist on the new SQL Server. For SQL Server 7 the default data path is C:\MSSQL7\Data and the SQL Server 2000 path is C:\Program Files\Microsoft SQL Server\MSSQL\Data.

1. Click on OK to begin the restore and OK again when it has finished.

1. We now need to create the BEIMS® and Reports logins. Start Query Analyzer and connect to the new SQL Server and then select the BEIMS® database. Type the following lines into the query window.

exec sp_addlogin 'beims', 'beims98'

exec sp_addlogin 'reports', 'reports'

exec sp_addsrvrolemember 'beims', sysadmin

Click on Query -> Execute to run the query.

1. Now we need to fix the permissions for the database users BEIMS and Reports. This is required because the link between the SQL Server security login for BEIMS and Reports and the database users BEIMS and Reports has been broken. The reason for this is that this information is not stored in the backed up database but in the SQL Server system database. Clear the previous query and type the following lines into the query window.

EXEC sp_change_users_login 'Auto_fix', BEIMS

EXEC sp_change_users_login 'Auto_fix', REPORTS

Click on Query -> Execute to run the query. The links between the security logins and database users are now restored. If the new SQL Server did not have the security logins BEIMS and Reports (as should be the case), the query automatically created them.

1. The database is ready for use. On the client PC, change the ODBC to point to the new SQL Server and BEIMS database. Refer to How to Create a SQL Server or MSDE ODBC for instructions on how to do this.

1. Activate BEIMS so users can log in again. Refer to How to Shutdown and Activate BEIMS for instructions on how to do this.

REFERENCES

How to Shutdown and Activate BEIMS

How to Create a SQL Server or MSDE ODBC