Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Moving Databases to a New Server

By Andy Warren,

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.

1 Install 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.
2 Stop 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.
3 If 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.
4 Make 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.
5 Shut 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!
6 Back 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.
7 Change 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.
8 Start 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.
9 Stop 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.
10 Start 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!
11 Merge 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!

Total article views: 9331 | Views in the last 30 days: 3
 
Related Articles
FORUM

Access Sql server Agent Windows service on remote machine

Error while accessing service form remote machine

FORUM

SQL 2008 Analysis services on Virtual Machine

SQL 2008 Analysis services on Virtual Machine

FORUM

Running a sql server 2008 job to back up to network folder

Running a sql server 2008 job to back up to network folder

FORUM

Service pack 2 installation

Reporting Services authentication issue during installation

BLOG

Failed To Lock Virtual Machine’s Configuration

[Updated] I rebuilt my Hyper-V virtual machines / virtual network by deleting the old ones and buil...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones