Moving databases

[SQLBill]

My server is dying...it's getting obsolete and it doesn't have enough umph. We currently have SQL Server 2000 Enterprise Edition on Windows Advanced Server 2000. We are installing SQL Server 2000 EE on a Windows 2003 Adv Server.

I have the user database files and the TEMPDB on a SAN system. Master, Model and MSDB are on the server itself.

Once the new server is built and SQL Server installed, I intend to stop services on the original server, move the system databases to the SAN. Then connect the new server and stop SQL Server services, rename the system database files, and move the original system database files over to the new server.

This way I should keep all of my user accounts/passwords and all of my jobs.

Has anyone done this? See any problems with this method?

-SQLBill

Posting advice: FAQ481-4875

 

[JayKusch]

You might have to acknowledge the possible server name change but after the move is done, a simple sp_dropserver 'OldName' followed by a sp_addserver 'NewName' should cover that base.

Thanks

J. Kusch

 

[SQLBill]

Thanks Jay....that's a good one for me to remember.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

The other thing you will need to do is update the sysjobs table with the new server name. The column is originating_server.

Other than that and the sp_dropserver/sp_addserver you should be good to go.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[SQLBill]

Thanks....this is really looking like it might be easier than I was expecting.

-SQLBill

Posting advice: FAQ481-4875