An ASP web application in which the backend DBMS is running on MSDE 2000 SQL Server.
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 .
1. MSDE 2000 SQL Server instance name is localhostHELM , 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 localhostSQLEXPRESS .
5. OS used Windows Server 2003.
1. Backup the raw database run the following command at the MSSQL Shell.
prompt > cd C:Program FilesMicrosoft SQL Server80ToolsBinn
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 FilesMicrosoft SQL ServerMSSQL.1MSSQLData .
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 FilesMicrosoft SQL Server90ToolsBinn
prompt> osql -E -S .SQLEXPRESS
MSSQL prompt > sp_attach_single_file_db @dbname=’HelmDb’, @physname=’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataHelmDb.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.