Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
Both answers were good, upvoted both, but had to pick one~ –  eyston Apr 13 '11 at 17:28

2 Answers 2

up vote 3 down vote accepted

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).

share|improve this answer

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/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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