1

We need to migrate a MSDE 2000 database running on a Windows Server 2003 Standard to a Windows Server 2008 R2 running SQL Server 2008 R2 Express (fresh install). The database is small, about 300MB.

Since Management Studio is not installed on the source server (Windows Server 2003), can we just stop the database instance via services and then copy the .MDF and .LDF files to the Windows 2008 R2 Server and attach the database using Management Studio?

  • 4
    No, no, no, no, no - do NOT stop the service and then copy the MDF/LDF files. What is wrong with taking a backup and then restoring? – Aaron Bertrand Mar 11 '15 at 15:58
  • Can you express why that would not work, aside from it not being best practice? There is nothing wrong with taking a backup and restoring. How can that be done on a box without Managment Studio? Would we need OSQL commands etc? No DBA's here to help, so trying to find the simple method. I've detached and attached DB's in the past with success so I thought this method could work. – Ken Mar 11 '15 at 16:05
  • 1
    If you stop the service you have no guarantee that the mdf/ldf files will be detached cleanly - I've seen multiple cases where this exact process led to corrupt databases. And since the corruption likely happened during the detach and/or service shutdown process, the files are damaged at the source, so you have 0 copies of your database. The files can be damaged in transit, too, so if you move them (don't copy them) and something goes wrong, same thing: 0 backups. You should be taking backups anyway, no? SSMS is not required to take backups. You can use SQLCMD, PowerShell, etc. – Aaron Bertrand Mar 11 '15 at 16:11
  • Thank You. I will heed your advice. We don't have native SQL backups for this particular application server. It is a Virtual Machine, and it is backed up with an application aware backup product. Point in time backups are not needed. I will take native SQL backups before the migration. I will also clone the VM before starting as well. – Ken Mar 11 '15 at 18:57
  • Of Note, I was able to install SQL Server Management Studio Express 2005 on the 2003 Server, and it is able to manage the MSDE 2000 database. Now we can take backups, set the DB offline, Detach/Attach etc. I was not aware until now that SQL Server Managment Studio could manage a MSDE 2000 Database. Hope this helps someone else. – user61598 Mar 18 '15 at 12:44
2

Relying on the service to shut down gracefully and detach your database files cleanly is risky. I have often seen this result in corrupted files that can not be used elsewhere, and which fail to start up even when starting back up the source instance which corrupted them. Moving the files after detaching them poses a similar risk - if they get damaged in or after transit, what are you going to fall back to?

If you don't have a backup of the database, either approach could result in disaster, since you are left with zero copies of your database.

If you do have a backup of your database, copy that, and restore it. If the backup isn't recent enough, create a new one. This is a much safer approach because your original database is still intact. If something goes wrong with the file during transit, or there are issues trying to restore it on the destination server, you can always start over - having lost nothing but time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.