| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2003-11-18 : 13:34:25
|
| We are moving our data center and part of this process involves implementing new server names and new logical drives. What steps should I follow to move a SQL Server instance to a server with a different name? I assume I should install SQL Server 2000 on the new box and then restore master, model and msdb using backups from the old server, but I don't know what impact the different server names will have on these system tables. I'm also concerned about the impact of a different server name on replication, dts, linked servers and remote servers.Thanks, Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-18 : 14:16:05
|
| Start with this:[url]http://support.microsoft.com:80/support/kb/articles/Q224/0/71.ASP&NoWebContent=1[/url]I don't ever move master, model, or msdb when I move databases to another server. To move logins, I use a stored procedure that I developed called isp_Tranfer_Logins (search the forums for it). For DTS packages, I do a Save As and then modify them. For replication, I redo replication manually or generate the scripts then changes the necessary information. To move the user databases, I use BACKUP/RESTORE or attach/detach method. For jobs, I script them out by right clicking on each of them.Tara |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2003-11-18 : 14:29:18
|
| Tara,Thanks for the feedback. Since you don't restore master, do you recreate each backup device?I believe your approach is what I will be following.Thanks, Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-18 : 14:32:38
|
| I don't use backup devices. I backup straight to a file. Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-18 : 14:38:19
|
| Cross Post?http://www.dbforums.com/t971051.htmlDave, it's not a little thing, and you should have a script that you follow to make sure you don't miss anything...you need to make it repeatble....And what connects to your db's...gotta worry about that too...Good Luck...Brett8-) |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2003-11-18 : 14:50:23
|
| Yes, that's my post. I put it out on a couple of forums today to get extra feedback. Regarding it not being "a little thing", the funny thing is I created a project plan that some people outside of the DBA area are already questioning due to the number of hours I've estimated. I warned everyone up-front that changing server names will create a lot of work. I have a feeling some key people do not fully grasp the situation. It's as if they see it as a name change that can be accomplished by a find/replace. I wish it was that simple.Dave |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-18 : 14:54:33
|
| It's always easy when you don't have a clue...$100 bucks says they make you cut the time in half...Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-18 : 14:54:48
|
| Changing servers is rather quick actually. All the prep work like scripting out jobs and replication should be done beforehand. The actual work should take less than an hour. The time would be dependent on how long a BACKUP/RESTORE method or detach/attach method would take. All other steps should be rather quick.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-18 : 15:07:47
|
quote:
Originally posted by tduggan
All the prep work like scripting out jobs and replication should be done beforehand.
Yeah, that's what'll take the most...don't forget testing in your plan...Brett8-) |
|
|
|
|
Help Restoring SQL Instances to Box with New Name