SQLServerCentral Article

Moving Databases to a New Server


Got a plan for moving your

data to the new server? Try this one! I've had the luck(!) to have had the need

to do this several times. This checklist works if you're moving to a different

machine with the same logical drive layout. If you use it, I recommend that you

print it and mark it up as you go, so you have your own customized version of it

for the next time and it also serves to document your installation.

1Install the OS and the same service pack as on your

existing machine. The computername cannot be the same as the one with your

data right now, so use a "temp" computername. Put the computer

on the network.

2Stop the SQL service on your old machine. Copy the entire

SQL7/SQL 2000 folder and it's subfolders, plus any separate folders

containing logs or data, to the new machine. Also copy over any other

folders containing data that you want to move to the new machine.

3If you have any shares on the old machine, you'll want to

transfer them. The easiest way is to script out the registry key [HKLM\SYSTEM\CurrentControlSet\Services\lanmanserver\Shares]

to a .REG file. Copy that to the new machine as well.

4Make notes about how your drive permissions are

configured, any jobs that you have set up in the OS (backup usually),

email profiles, anything else you can think of! Using the /3GB switch? Be

sure to add it to the new machine's boot.ini.

5Shut the old machine down and disconnect the network

cable. Have your network administrator remove it from the domain. Don't

delete anything yet! This machine is your backup plan!

6Back to the new machine. Rename the folders for now. For

example if your data folder is D:\Data, rename it to D:\DataGood. If your

SQL install folder is C:\MSSQL7, rename it to C:\MSSSQL7Good. The reason

for this step is that when you install SQL, you don't want it to overwrite

the contents of any of your folders.

7Change the name of the computer to that of the old

machine and reboot. Once you have it on the network, install SQL and the

same service pack that you are using on the old machine. Make sure that

during the install you use the same folder names you have been using on

the old machine.

8Start the service. Now is a good time to set up your

email profile(s) on the machine and verify that you can get SQL Agent to

send a test message.

9Stop the service. Rename the folders that SQL created

during the installation to have a suffix of OLD, so that C:\MSSQL7 becomes

C:\MSSQL7OLD. Now rename the 'GOOD' folders to their original name, so

that C:\MSSQL7GOOD becomes C:\MSSQL7, etc.

10Start the service. Verify you have your databases, make

sure the agent is running and that it can send email. Check the error log.

Resolve any problems you have before continuing. Verify connectivity using

Query Analyzer from your workstation. Do a quick test of ALL your

applications that connect to this machine - now is the time to find any

issues. If you run into problem, just start up the old machine so you can

double check how it was configured. Just DO NOT let it have network

access. Two machines with the same name on the network is bad!

11Merge the .REG file to add the shares to your new

machine. Set drive permissions. Set up any jobs you need.

You're done!

Got a question or a comment? It may take a day or two depending on my

schedule, but I WILL reply!


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating