Move SQL 2000 to new box.

[jjjax0330]

We want to upgrade our existing sql 2000 box to a new faster box. What is the best procedure to do this? We want to keep the same server name because of all links and so on. Thanks, Joe

 

[ptheriault]

I would set up the new server in parallel. Migrate your user and databases. Then you can test make sure everything works on the new server. When you are ready, rename the old server or take it off-line. Then rename the new server and change the IP address to the old server.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

You also have to make sure to tell SQL about it's new host name.

exec sp_dropserver 'OldServerName'
go
exec sp_addserver 'NewServerName', 'local'
go
update msdb.dbo.sysjobs
set originating_server = 'NewServerName'
go
--Restart SQL

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tran008]

Mr. Denny,

Just to clear myself up. Where do you run the code? From the old box or the new box?

thanks

 

[ptheriault]

You run that from the new server. I would also use sp_rev_logins to move your login accounts. That way you won't have a problme with database SIDS. you can get that procedure here.

thread962-1342371

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jjjax0330]

Thanks, I just found this article that gives detailed steps if you can have a little downtime which we can. Does this look good?

http://vyaskn.tripod.com/moving_sql_server.htm

 

[mrdenny]

Yep, that looks like the procedure.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com