Licensing and Activation Wiki Moving Your SELECTserver Database

Moving Your SELECTserver Database

SELECTserver: Database moving to another server

SUMMARY:
How to move SELECTserver database to another server using SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition

Applies to:

SQL Server Desktop Engine (MSDE 2000)
SQL Server 2005 Express Edition

Database can be moved by creating backup and restoring it on the new server or detaching and attaching procedure.

Backup creating and restoring - Use if you are moving between version of SQL Server - Note: you can only move up in version, meaning a backup from MSDE can be restored to SQL Server 2005 Express but a backup from 2005 Express cannot be restored to MSDE

Connect to the machine running MSDE or SQL Server 2005 Express
Open a command prompt
Type the following command, and then press ENTER:

        osql –E

Type the following command for backup creation, replacing ‘DBname’ with the name of your database:

        BACKUP DATABASE _DBname_ TO DISK = 'C:\Program Files\Bentley\SelectServer\Database\Backup\_DBname_.bak'

Type the following command for log backup creation, replacing ‘DBname’ with the name of your database:

        BACKUP LOG DBname TO DISK = 'C:\Program Files\Bentley\SelectServer\Database\Backup\_DBname_log.bak'

Copy the backup files to the new SQL Server for the purposes of this article well assume you copied the files to c:\temp
Connect to SQL server on the new server (server where database will be moved)
Type the following command for database restore, replacing ‘DBname’ with the name of your database:

        RESTORE DATABASE _DBname_ FROM DISK ='C:\TEMP\_DBname_.bak'

NOTE: If you are using the default database that was installed with SELECTserver than the database name is SELECTserver

Detaching and attaching (skip it if backup/restore was performed)

Connect to the machine running MSDE or SQL Server 2005 Express
Open a command prompt
Type the following command, and then press ENTER:

        osql –E

Type the following command for detach of database, replacing ‘DBname’ with the name of your database:

        EXEC sp_detach_db 'MyDBname'

Copy the database files to the new SQL Server. The database files are located in C:\Program Files\Bentley\SELECTserver\Database\MSSQL$SELECTSERVER\Data and are named MyDBname.mdf and MyDBname.ldf
Connect to SQL server on the new server (server where database will be moved)
Type the following command for detach of database, replacing ‘DBname’ with the name of your database:

        EXEC sp_attach_db @dbname = N'_MyDBname_', 
        @filename1 = N'C:\Program Files\Bentley\SELECTserver\Database\MSSQL$SELECTSERVER\Data\_MyDBname_.mdf', 
        @filename2 = N'C:\Program Files\Bentley\SELECTserver\Database\MSSQL$SELECTSERVER\Data\_MyDBname_.ldf'

Configure database to support the SELECTserver