IT Made Simple

Scenario :
An ASP web application in which the backend DBMS is running on MSDE 2000 SQL Server.

Objective :
Making the ASP web application to use MSSQL 2005 Express on to the server. On top of that database created on the MSDE 2000 Instance needs to be migrated over to the MSSQL 2005 .

Scope :
1. MSDE 2000 SQL Server instance name is localhost\HELM , MSSQL db name HelmDb.
2. MSDE 2000 SQL Server instance is running at a non MSSQL default port (not 1433).
3. MSSQL Server 2005 Express is used.
4. Using default MSSQL Server 2005 Express instance localhost\SQLEXPRESS .
5. OS used Windows Server 2003.

Workflow :
1. Backup the raw database run the following command at the MSSQL Shell.
prompt > cd C:\Program Files\Microsoft SQL Server\80\Tools\Binn
prompt > osql -E -S .\\Helm
MSSQL prompt > sp_detach_db ‘HelmDb’
MSSQL prompt > go

2. Copy the mdf file e.g. HelmDb.mdf and place it at a safe location. The backup is complete.

3. Install the MSSQL 2005 Express .

4. Copy the HelmDb.mdf from the backup and place it into the physical location of MSSQL 2005 Express, usually it is located in path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data .

5. Reattach the mdf file back to the db. But you need to remove the ldf file, e.g. HelmDb_log.ldf
prompt> cd C:\Program Files\Microsoft SQL Server\90\Tools\Binn
prompt> osql -E -S .\\SQLEXPRESS
MSSQL prompt > sp_attach_single_file_db @dbname=’HelmDb’, @physname=’C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\HelmDb.mdf’
MSSQL prompt > go

6. You should be able to see something like the screenshot below during the reattachment, take note that MSSQL 2005 Express will upgrade the mdf to a higher version automatically. Not to forget, a new ldf or transaction log file will be created automatically.

7. Test the newly attached MSSQL 2005 Express db using SQL manangement express.

8. Change your web application connection string.

9. Well done, you have completed the migration.