I’ve recently had the dubious pleasure of migrating a SQL Server instance from one machine to another, not a simple task in complex setup. Here are some notes that might be of use to other people who need to do this.
Say we have a database server called DBSERVER and we want to upgrade to new hardware without messy stuff like moving drives around. First we install the operating system on the new machine and call it NEWSERVER, simple enough so far. Next we install SQL Server 2000 on NEWSERVER, here it’s important to make sure you use the same collation setting as the old server while installing SQL Server or you are going to end up doing it all over again.
NEWSERVER now has a completely blank SQL Server instance on it, so we can start copying stuff around. The basic procedure goes as follows:
- Copy logins
- Copy DTS packages
- Disable SQL Agent jobs on DBSERVER
- Detach the databases on DBSERVER
- Copy the database files over to NEWSERVER
- Rename DBSERVER to something else (say OLDSERVER)
- Rename NEWSERVER to DBSERVER
- Update some SQL Server internals in DBSERVER (the new one) to make sure it has the right internal name
- Attach the databases on NEWSERVER
- Recreate SQL Agent jobs on DBSERVER
Copy logins
I found a useful page here on Microsoft‘s web site that makes migrating logins relatively simple and quick, I went for Method 1 under “A complete resolution to transfer logins and passwords between different version of SQL Server”.
Copy DTS packages
Note that this only works with Local Packages. I found a page here that gives some methods of copying DTS packages between SQL Server instances, the method I went for is the one describing how to use a DTS to transfer them.
Disable SQL Agent jobs on DBSERVER
This step is done now to prevent automated jobs from trying to access offline databases. Just select each job, right-click and click “Disable job”. If “Disable job” is unavailable for any jobs, see this page for help.
Detach databases
Note that your databases will be unavailable from this point on until they are attached again on the new server. This is just a matter of right-clicking on each of your databases (not the system databases master, model, msdb and tempdb) in Enterprise Manager and selecting “Detach database” in the “All tasks” menu.
Copy databases
Now you need to copy the .MDF and .LDF files from the SQL Server Data folder on DBSERVER to the corresponding folder on NEWSERVER, again making sure not to copy system databases. This step can take some time, so grab a cup of coffee.
Rename the servers
This is an operating system task, I’ll leave it up to the gentle reader to work out. It may be a good idea to actually move IP addresses around a bit too (so give OLDSERVER some new IP address and move the IP that was on DBSERVER along with the name).
Update SQL Server internals to the new names
Again I refer you to this page on how to do this, as a minimum I suggest:
On OLDSERVER do:
sp_dropserver 'DBSERVER' sp_addserver 'OLDSERVER'
On NEWSERVER do:
sp_dropserver 'NEWSERVER' sp_addserver 'DBSERVER'
Attach databases
This is again done in Enterprise Manager, you want to attach all the databases you have copied over and do some checks to make sure they are OK (check that the logins are OK at least).
Recreate SQL Agent jobs
How this is done depends on your setup. Basically you will need to recreate all the needed SQL Agent scheduled jobs on the new server.

