Best way to move all DB / security / jobs from one server to another

Question

Best way to move all DB / security / jobs from one server to another

Cory E.

SSCertifiable

Points: 7076
More actions
January 5, 2006 at 6:42 am

#89099

Hey all,
I am looking for the best way to retire one server by moving all DB's / Security / jobs from one server to the new server. I have approx. 1 TB of data among all DB's, windows 2000 with SQL Server 2000 sp4 going to a diff server with diff drive locations, windows 2003, SQL Server 2000 sp4. when all is done, it needs to be identical (there will be a rename of the server upon completion)
Any ideas / gotchas would be GREATLY appreciated.
Cory
-- Cory

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

isd83 Ten Centuries Points: 1066 More actions · Answer 1

I will be moving db from an old server to a new server this weekend. I only have 7 Gig. of data to move. I plan to shutdown the old server and copy the data files to the new server then attach them via a script that I created. I also would have capture all the login ID prior to the move with sp_help_revlogin http://support.microsoft.com/kb/246133/ I guess build a DTS package to migrate the db and backup / restore would be other options, but I feel more comfortable with re-attaching the dbs. May be someone else have better method? Thank.

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 2

It depends on how much you have to move. My thought would be the best way would be to back up master, msdb, and your databases and restore on your new server.

Joe Nakanishi-3 SSC Eights! Points: 828 More actions · Answer 3

You can detach, move files and reattach, but Cory was asking about moving logins, jobs, etc... as well as the data.

I haven't had to move logins, let me know how that KB works. Otherwise, you'll need sp_change_users_login.

Joe

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 4

Hi,

make sure that on the new server each file is in exactly the same path as on the old server, and of course the service pack/hotfix level has to match exactly.

Then the easiest way would be to shutdown the old server, copy all files to the new server (including master, msdb, model) and restart the new server.

A more safer way vould be to do a backup of all databases on the old server, copy the backups, and restore them on the new server in the order master, msdb, model, user databases...

regards

karl

Best regards
karl