Move SQL Server to a new machine

1,740 pts.
Tags:
SQL Server
I am moving our SQL Server databases to a new machine. The old OS is Windows 2000 and the new is Windows Server 2003. The version of SQL Server is staying the same and the data folder structure will be the same. My question is: Would it work to install SQL Server on the new machine, stop the services on both machines and then simply copy the data folders from the old to the new. Then start the services on the new and be up and running. Rather than restoring databases from back up or detach/attach all the databases?
ASKED: June 13, 2006  4:44 PM
UPDATED: June 16, 2006  10:28 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think you’ll have to attach them anyway

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kamiilihp
    Another consideration is users and permissions, which are not persisted with detach/attach.
    0 pointsBadges:
    report
  • PurpleLady
    Microsoft generally doesn't approve of the copy method, but I have used it successfully in the past with the same type of process. There will be issues since there server name will be changing - in one case, it was not supposed to change but oops! It did. There is a KB article on how to resolve the problems associated with this. This process was very successful when the server name remained the same, i.e. a replacement server.
    0 pointsBadges:
    report
  • Randym
    To PurpleLady, I forgot to mention that I planned on having the server name remain the same as well. I think I'll try the copy since it has worked for you. To Everyone: I just tested using the restore database using Enterprise Manager. First I started the database in single user mode (-m in the command). Then I restored the Master database. That brought in all my security logins. Then I restored the MSDB which brought in all the DTS and Jobs. Then I restored each database. That all seemed to work well. Right now of course, the server names are different; so the Agent for running jobs are having a problem running I think because even though I have the database set up as "Local", it still has the server name associated with the job somehow. I am going to try disconnecting the server from the network and rename the server to the same as the old and see if that corrects that problem.
    1,740 pointsBadges:
    report
  • Kpooschke
    Doing what you're suggesting works really well and is the easiest way to move an entire SQL Server installation from one machine to another. Since all of the system database files will also be copied, it will retain all information about the databases (and they show up as attached) as well as logins, users, and permissions. The only thing to be careful of is that the data drive is the same drive letter on both machines (if not, there are ways around this, but it requires some more steps). There are also 2 things you'll need to change on the new server after everything is copied and SQL is started up. 1) You'll need to change the SQL Server name from the old machine to the new. To do this, you'll need to manually update the sysservers table, changing the values of srvname and datasource for the local server to the new name. 2) You'll need to change the "source" server of all of the SQLAgent jobs or you'll get errors when you try to edit them. To do this, update the sysjobs table in msdb and set the value of originating_server to the new server name. That'll do it.
    0 pointsBadges:
    report
  • WHDILLON
    That should work fine. Just move the *.mdf and *.ldf over and attach. Users will be orphaned, so make a plan for that. Otherwise, good to go. William
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following