Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Moving databases

Status
Not open for further replies.

SQLBill

MIS
May 29, 2001
7,777
US
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
 
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
 
Thanks Jay....that's a good one for me to remember.

-SQLBill

Posting advice: FAQ481-4875
 
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)

[noevil]
(Not quite so old any more.)
 
Thanks....this is really looking like it might be easier than I was expecting.

-SQLBill

Posting advice: FAQ481-4875
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top