RE: Data Copy

From: Peter Feakins (anonymous_at_discussions.microsoft.com)
Date: 04/08/04 

Date: Thu, 8 Apr 2004 15:06:03 -0700

I've used the following approach: http://www.support.microsoft.com/?id=314546 . Here the master and msdb databases are not copied to the new server. Instead you transfer the logins, jobs, packages etc as outlined here. If you plan on using the old server's name, you need to run sp_dropserver and sp_addserver (after renaming the server) to change @@servername ie:

1. sp_dropserver 'currentservername'
2. sp_addserver 'desiredservername' , 'local'
3. restart the service and check @@servername

I've seen description sof the approach you're asking about but have never tried it myself - it assumes that your drive set up for example is the same. Here is a link that describes this http://vyaskn.tripod.com/moving_sql_server.htm .