Moving SQL Server 2000 to another server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ronnie - 26 Mar 2008 17:51 GMT

I will be moving the SQL Server 2000 databases from Server A to Server B.
Both the servers will be having the same directory structure. Here is what I
will be doing:
1. Backup and restore master from Server A to Server B
2. Backup and restore msdb and model from Server A to Server B
3. Detach all the user databases from Server A, copy the .mdf and .ldf files
to Server B and attach the databases on Server B.
4. Shut down Server A.
5. Rename Server B to Server A.

Any suggestions will be highly appreciated. Thanks.

Reply to this Message

Tom Moreau - 26 Mar 2008 18:16 GMT

If the folder layouts are identical, then you could just shut down Server A,
copy the whole Data folder(s) over and then start up Server B. Just be sure
to run sp_dropserver and sp_addserver when you are done.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Reply to this Message

Tibor Karaszi - 26 Mar 2008 21:00 GMT

And here's some additional (good) reading:

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

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> If the folder layouts are identical, then you could just shut down Server A,
> copy the whole Data folder(s) over and then start up Server B. Just be sure
[quoted text clipped - 11 lines]
>
> Any suggestions will be highly appreciated. Thanks.

Reply to this Message

ronnie - 26 Mar 2008 21:53 GMT

Thanks for the information. If I am going to rename the server B to server A
after shutting down server A, what will I achieve by running the
sp_dropserver and sp_addserver?

Reply to this Message

Tom Moreau - 26 Mar 2008 21:54 GMT

Oh yeah, you're renaming it back to the original. Missed that one. In that
case, you're OK without those.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Thanks for the information. If I am going to rename the server B to server A
after shutting down server A, what will I achieve by running the
sp_dropserver and sp_addserver?

"Tom Moreau" wrote:

Reply to this Message

ronnie - 27 Mar 2008 13:41 GMT

Thanks a lot, Tom and Tibor. This was really helpful.

> Oh yeah, you're renaming it back to the original. Missed that one. In that
> case, you're OK without those.
[quoted text clipped - 24 lines]
> >
> > Any suggestions will be highly appreciated. Thanks.

Reply to this Message