Moving SQL Server from one server to another

[ctwilliams]

I want to move a SQL 2000 Server completely (all data jobs, DTS packages, logins) from one W2K SP4 machine to another, then rename the new server as the old server.

I have read related posts on this site and found the following article that describes how to do this:

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

The article does not mention detatching the databases from the old SQL Server before copying the data and transaction log files to the new server. It also doesn't mention attaching databases on the new server. Does copying all of the MSSQL files and subdirectories over prevent me from having to attach/reattach the individual databases?

Has anyone had any experience with or forsee any problems with the method listed in this article? Thanks!

 

[cyno]

If some downtime is allowed then

Shut down the application and sql service on old server
Take the backup on old server and restore them on new server
Transfer the logins,DTS packages and jobs over to new server
Connect your application to new server and test
Once everything looks good. Disable the service on Old server

 

[ctwilliams]

I have a few hours of downtime.

I was hoping to move all of the objects (databases, logins, jobs, DTS) over in one fell swoop rather than individually, which is why I was interested in the method described in that article.

 

[mrdenny]

Install SQL on the new box.
Patch both servers to the same level (SP, hot fixes, etc).
Backup all databases including master, and msdb.
Rename the old server
Shutdown the old server
Backup system databases on the new server (just in case)
Bring up the SQL Server on the new server in single user admin mode. (from a command prompt go to the MSSQL\binn folder. Run "sqlservr -c -m")
Connect with osql and restore master from the backup on the old server.
Restore msdb from the old server.
Stop and restart the SQL Service normally.
Ignore all the error messages about the databases missing.
Restore your user databases.
When you are done renaming your user databases rename the new server so the name matches the old name.
reboot.
Done.

Questions?

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.)

 

[ctwilliams]

I ended up following the instructions in the article and they worked perfectly. I didn't have to detatch/attach anything since I shut the SQL services down before copying the mdf/ldf files, and I didn't have to restore any backups. All of my logins, jobs, linked servers, and DTS packages came over.