bhieb
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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?
ASKER
Disregard that last one, I see now that I can detach from one and move the files and reattach on another.
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).
ASKER
Hmm that would be a pain to setup manually would something like this work?
http://support.microsoft.com/kb/918992/
http://support.microsoft.com/kb/918992/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
"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).
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.
ASKER
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.
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.
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?
ASKER
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
FYI...I found the MS "approved" way of doing it, although it looks more complicated.
http://support.microsoft.com/kb/314546
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!
ASKER
Will detatching and reattching the master, model, msdb, and temp database cover those?