Moving SQL2K DB to new server also with SQL2k

[mattisontara]

Folks,
I am having problems finding documentation on steps necessary to move my sql2k db from the current production server, to a new and more robust server that we have purchased. Everything on the new server, including the server name, needs to be identical. We would like to leave the current production server accessible, if possible, during the move. Ideally, when the objects were all copied to the new server, we would be able to shut down the old one and have the new one take over.
I would greatly appreciate your help. I was unable to find this exact scenario anywhere but I did spend a great deal of time looking.

 

[Dynapen]

The easiest way is to make a OS copy of the database and the copy it to the new server and stand it up. This requires that the current server goes down for a minute or two tops, and then is right back up..

Go to you enterpise manager.
Select the database, and right click
Select detach database.
This will make the database disappear from the window.
Go to the folder on the computer where the MDF file for that database is located, and make a copy of both the MDF and LDF for that database. (If you select the properties of the database before you detach it, one of the tabs will tell you this information)
Once you have a local copy of the database, go back to enterprise manager.
One the database folder, right click and select attach database
Point it back to the original MDF file and select OK. That will turn you current production database back on.

Move the copy of the MDF and LDF files from the old to the new server, and repeat the attach database process.

The only difference now between the two should be the transactions that have happened while you were attaching the new database.

Note* This will not move the users, login, and things like that from one machine to another. You will need to do those before. Also, you don't have the have the machines/servers named the same while this is happening.

That's how I have had the most luck doing it. But there are still other ways

Scripting the database,
Database replication,
etc.....

You will have to find which one works for you. Which depends alot on network speed, server speed, and useage of the database.

 

[mattisontara]

Gee, thanks! Do you know off hand of any sites or KB articles with all the different ways spelled out like you just did for me?

 

[tlbroadbent]

Try the following links.

Moving Databases to a New Server (SQL Server Central)

http://www.sqlservercentral.com/columnists/awarren/20010425205439_1.asp

Moving SQL Server Databases to a New Location with Detach/Attach
[ignore]

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071

Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304692

[/ignore] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.