[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Move SQL 2005 to new server

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
bhieb
Asked:
bhieb
  • 8
  • 4
  • 2
  • +1
3 Solutions
 
jramsierCommented:
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
 
sammySeltzerCommented:
you can detach and reattach the databases.
0
 
bhiebAuthor Commented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
bhiebAuthor Commented:
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
 
bhiebAuthor Commented:
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
 
bhiebAuthor Commented:
Disregard that last one, I see now that I can detach from one and move the files and reattach on another.
0
 
jramsierCommented:
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
 
bhiebAuthor Commented:
Hmm that would be a pain to setup manually would something like this work?

http://support.microsoft.com/kb/918992/
0
 
Mark BradyCommented:
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
 
bhiebAuthor Commented:
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
 
Mark BradyCommented:
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
 
bhiebAuthor Commented:
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
 
Mark BradyCommented:
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
 
bhiebAuthor Commented:
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
 
Mark BradyCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now