Q: I am using MSDE/SQL Express for my RPM installation and would like to use a full version of SQL instead (Or migrate from SQL 2000 to SQL 2005). how do I move all of my data to the new database server?

A: The easiest way is detach the database from the old systems and reattach it to the new system. This can be done using the SQL Enterprise Manager or the SQL Management Server Studio.

Enterprise Manager is available with full versions SQL 2000 and SQL Management Server Studio is available with full versions of SQL 2005. There is a SQL Management Server Studio Express free for download which is geared for use with SQL Express.

Ensure RPM is shutdown and the deferred processor is not running. In your SQL manager, under the databases node, right-click on your database and choose the option to "Detach database". Doing this will also require the database to be unmounted during this operation.

Then copy the database files to your new SQL server installation and using the SQL manager, right-click on the databases node and choose the option to "Attach database" and select your old database files. If you are not sure where your database files can be found, you can examine the properties of the database to determine this. They can be copied to and accessed form any location on SQL server.

The final step is to point RPM and its website to the new database location.

To point the RPM application to the new database, open RPM, and select "Database settings" from the SETTINGS menu. Input the new server name. Once you have made this change you need to update the website database settings (must be done after the program).

To update the website database settings, go to SETTINGS | MANAGE WEB APPLICATION | MANAGE WEB APPLICATION INSTANCES. Select your website from the list, right-click on it, and choose to "update instance with current options".

Then you are done.