Solved

Move SQL 2005 to new server

Posted on 2010-11-08
15
291 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
  • 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 28

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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now