SQL Server Express - Move by Copying Data Folder?

Question

We are moving our SQL Server to new hardware. Initially we were going to do this simply via backup and restore, but noticed we'd have to recreate all the logins. We don't want to do that :)

Downtime is not an issue, so would it be possible to simply stop the old and new server and copying all of the mdf files between the two? This would copy the master database which has the logins as well as all of the databases. I just don't know if it is supported.

We are using SQL Express so don't have the fancy bells and whistles, but on the flip side downtime is not an issue.

Thanks!

Answer 1

The location of every database file for every database is stored in master, in absolute full path. That means that you would have to copy all the files at exactly the same full path on the new server (including drive letter). If this is possible, then you can copy the files. After the copy the @@SERVERNAME will return the old server name, and this can be corrected by following the procedure described in KB818334. Another thing to watch out for is if you have anything encryption related that may have been saved using the DPAPI machine or service key (eg. linked server credentials, proxies, database master keys etc), they won't be accessible after the copy because the new host DPAPI won't be able to decrypt them.

Overall, copying the files (specially master) is not a supported procedure and may result in a unusable server. Copying logins is a well know procedure and supported (there is even an SSIS task for it).

Answer 2

I wouldn't recommend you moving the master database files like you are planning to do. It's more messier than compared to moving logins. In fact moving logins is easier if you follow the before article.

http://support.microsoft.com/kb/918992

I would recommend using DETACH and use CREATE DATABASE FOR ATTACH.

http://weblogs.sqlteam.com/dang/archive/2009/01/18/Dont-Use-sp_attach_db.aspx http://www.sqlservercentral.com/articles/Administration/65896/