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

• Open Bentley SELECTserver Database Setup
• Enter name of the new database server
• Select database from ‘Database’ drop down
• Click OK