April 20, 2007 at 12:34 pm
What's the best way to move SQL Server from one server to another? Here's the scenario:
The new server is newer hardware with a mirrored drive configuration. The old server had just one drive. The new server will have the same name, IP, and OS. There are over 200 of these in the enterprise, so we'd like to automate the process if possible.
Is it feasible to copy the system databases as well as the user databases, since the box will have the same name and IP address?
Thanks...Nali
April 20, 2007 at 2:00 pm
it is possible to do. some things to consider - data and log file locations will need to be the same, or else you will have use command line to start sql and drop the user databases and then reattach or restore them. I've done it both ways, restoring system databases and starting from scratch. they both have their pitfalls, but I think that restoring the system databases is the easier way to go about it. Another thing to consider is that both machines can't be on the same domain at the same time. We overcame this issue by using a large detachable USB drive. it was slow IO, but worked for us. We also ran into an issue with a server not having as much memory as the old one, so SQL bombed when we tried to start it stating it could allocate enough memory. You will want to restore the user databases first, then the system datbases (otherwise master will not find the user databases it expects to find). After you restore the system databases, if sql won't start, just check the error logs on the c:\ drive and fix your problem. many times this requires starting sql using trace flags. Good luck
April 23, 2007 at 8:57 am
We have always moved to new hardware without moving system databases and we've never had a major problem. We use the methods described in this article: http://support.microsoft.com/kb/314546/en-us. We've gotten around the problem of two machines with the same name by copying all the scripted objects and database backups to a third "parking lot" server, unplugging the original server from the network, connecting and setting up the new server, and copying the databases and objects on the new server. A couple of times, we've just named the new server differently than the old server.
Greg
Greg
April 23, 2007 at 9:32 am
I don't think moving the system databases is a good idea. It can work fine, but if you have issues, then it's hard to recover.
Easy to reinstall, then move DBs (detach/attach) and script out logins and jobs.sp_help_revlogin will do the logins with same passwords, then sp_change_users_login to sync SIDs. Script jobs, copy packages, not a huge job since you can stage most things and have them go first with the dbs going last when you change names.
April 24, 2007 at 4:29 am
if the server name and ip address is the same you should be fine moving system database as long as SQL versions and directory strucures are identical. Just bring SQL down, move the database files into place (after having first copied originals to a safe place as a back out), restart SQL. Application database will come up suspect if files not in exact same locations but drop/restore or detach/reattch will fix this, or put files in same place then move afterwards to your new required location.
Steve is right though moving master database has potential to bite you so have a planned backout or use scripts (sp_help_revlogin plus do not foreget default database, default language plus reassign any server roles, user defines error mesages?) to recreate master db.
If SQL version is identical can safely copy over msdb to get your jobs and dts stuff back easily, only gotcha is have to update column originating_server to new server name in table sysjobs if it is not the same.
it will be easier to automate if you go via the scripting route I guess.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply