Solved

Move SQL 2005 to new server

Posted on 2010-11-08
15
295 Views
Last Modified: 2012-05-10
We are upgrading the hardware on our SQL server, and I'm needing to know the quickest way to move the databases/instaces from one machine to the other.  The old machine is 2003 Standard 32bit and the new one is 2008 R2 64bit. The SQL server is currently 2005 32bit, but it will be installed on the new server as 2005 64bit.

What is the best way to go about doing this. I was going to just install SQL 2005 64 bit on the new server (both instances). Then just do full backups and restores from the old server. Rename the machines and be done.

That will get my data there, but what about all the other stuff, like users, maintenance jobs, ....?

I have about 20 small databases spanning 2 instances with no replication. Any guidance would be appreciated.
0
Comment
Question by:bhieb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 10

Accepted Solution

by:
jramsier earned 167 total points
ID: 34086605
Detach the DB move it then attach the database to the new server.

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

Always backup before working on it.
0
 
LVL 29

Assisted Solution

by:sammySeltzer
sammySeltzer earned 167 total points
ID: 34086624
you can detach and reattach the databases.
0
 

Author Comment

by:bhieb
ID: 34086648
I understand that, getting the databases moved is the easy part but what about all the other stuff. Users, maintenance plans, and all the other goodies that are not "databases"?

Will detatching and reattching the master, model, msdb, and temp database cover those?
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

Author Comment

by:bhieb
ID: 34086670
The idea here is that when the server is renamed I won't have to touch the clients, all the settings will be the same and the applications will work seemlessly.
0
 

Author Comment

by:bhieb
ID: 34086723
Also I don't know that the detach function is what I'm looking for, since I am moving them to a new server not just a location on the existing box. I would imagine backup/restore would be a better option?
0
 

Author Comment

by:bhieb
ID: 34086794
Disregard that last one, I see now that I can detach from one and move the files and reattach on another.
0
 
LVL 10

Expert Comment

by:jramsier
ID: 34086877
yes you will need to create the user names and change dbo on the database.  I have just in the past recreated those logins, if it was done right it would be a AD accounts that can easily be recreated, if not i would set it up in AD but ensure the software you are using support the AD login account (some require that you do not use AD).
0
 

Author Comment

by:bhieb
ID: 34086972
Hmm that would be a pain to setup manually would something like this work?

http://support.microsoft.com/kb/918992/
0
 
LVL 20

Assisted Solution

by:Mark Brady
Mark Brady earned 166 total points
ID: 34087489
This guy writes a pretty thorough tutorial on migrating from one physical machine to another. In particular to your question, pay attention (on the first page) to his notes about copying user data files etc... I was always of the understanding that user accounts were stored in a database like any other data. Perhaps even called "users"

http://vyaskn.tripod.com/moving_sql_server.htm

If that is the case you would export it like any other export of a database. However, the link might provide you with the correct way to copy literally everything.

http://vyaskn.tripod.com/moving_sql_server.htm
0
 

Author Comment

by:bhieb
ID: 34087934
Yah i read that one too, but this part concerned me.

"Install the exact same version of Windows operating system on the new server."

Since this is not the same version of Windows or SQL really (32bit vs 64bit).
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 34088009
I saw that but I'm inclined to thing that the section on user accounts would be the same no matter which version you are using (I think). I wonder why he states they must be EXACTLY the same? Most people wanting to swap to new servers do so because they are upgrading both hardware AND the latest software.
0
 

Author Comment

by:bhieb
ID: 34088092
His method scares me, but there is little risk since all you would need to do is plug the old one back in and rename.

He's basically doing a very rough software move (one I've done with 3rd party software I did not know much about). Essentially install the basic package then just overwrite the actual data in Program Files via copy/paste. Since 2008 and 2003 are not that different this may work, but there could be problems if say 2008 wrote a registry entry to a different spot or something like that or a stray DLL that did not get installed right (most of these should be non issues).  In theory though it should work.
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 34088325
I agree with you on the scary part. In practice, developers should NOT change the way they install and register their software but some do so you do run the risk of it not running properly. You could look at each entry or folder and if it is the same then use the copy/paste solution perhaps?
0
 

Author Comment

by:bhieb
ID: 34088347
Most likely. I'm a week or so out on this, but will award points accordingly. Thanks for the options guys!

FYI...I found the MS "approved" way of doing it, although it looks more complicated.

http://support.microsoft.com/kb/314546
0
 
LVL 20

Expert Comment

by:Mark Brady
ID: 34088403
Well at least you will have only Bill Gates to blame if it all goes south! That option might be the best for you. Good luck!
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question