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 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.
Workflow :
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.
good job well done.. u shld include this in office forum 🙂
Hi Duddits, I will paste this link to the office forum, increase my site traffic 😀
you can install the SQL Management Studio Express which allow you to do the attach/detach via GUI.
I tried this approach on Windows Vista with no success. Kept getting access denied.
First I thought that even though I was logged in as Admin, I wasn’t getting DB admin privileges. This turned out to be true. By default, an admin user doesn’t have any login on the SQL DB. I then added my windows login to the database. Still no success. so I tried SQL login using sa. Still same result. I then thought maybe osql wasn’t running as admin. This also turned out to be true. By default, in Vista, the command shell does not run with admin privileges even if you are logged in as one. You need to do ctrl-shift-enter when you type “cmd” to get a command shell that runs as admin. However this didn’t work either. I finally got in installed by running Sql Server Management Studio Express (as Administrator) and choosing Attach…
One more problem I had was that for some reason, despite pointing it to the mdf file. It insisted in locating the original path where the mdf file was installed (which was on a different machine and thus didn’t exist on this machine). To get it to work I had to recreate the same directory structure that held the mdf file on the other machine, copy the mdf into it then point it to that mdf file. Also, it didn’t work if I left out the ldf file.