C.4 Migrating MSDE to SQL Server

If you decide to migrate an MSDE database to SQL Server, you have several options. One is to simply install SQL Server on top of MSDE. If you install SQL Server on a separate computer (a more likely approach), you can detach the MSDE database file and reattach it on a SQL Server machine using the sp_detach_db and sp_attach_db stored procedures that are provided in all SQL Server and MSDE databases.

To start, log on to your MSDE instance, and use the following commands (in OSQL) to remove the database:

USE Master
sp_detach_db 'databaseName'
GO

Next, copy the corresponding data and log files (.mdf and .ldf files) from the current location to the new computer. They should be placed in the data path used by your SQL Server.

Reattach the database to SQL Server using the database name and the database filename (with full path). These commands can be entered on the SQL Server using Query Analyzer or OSQL (which SQL Server also supports). Just make sure that you log on to the correct SQL Server first before you execute these instructions.

USE master
sp_attach_db 'database name','databaseFile'
GO

You can verify the change in file location using the sp_helpfile stored procedure:

USE databaseName
sp_helpfile
GO