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?
Best Answer
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.