Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Sunday, April 5, 2009

SQL Server Migration - The easy way

I have had the experience of moving an instance of SQL Server from one machine to another a few times and tried a few different ways of doing so. In simple cases, it can be as simple as scripting a few logons, and restoring backups on a new machine. Sometimes it is a little more involved if replication is involved. The scenario I am covering here is where you would like to replace a machine with SQL and move it to a new machine. I have used this method successfully to move a SQL instance from windows server 2003 to another 2003 box and even from a windows server 2003 box to a 2008 one. Here is an example of what has worked for me, where I want to replace a server named MainServer with a Server called NewBox

  1. Backup all databases on MainServer.
  2. On NewBox, install the same version of SQL as on MainServer, and make sure it has the same service pack if any. ALSO, make sure that the data and log paths on NewBox match that of MainServer (this is important).
  3. Shut down the SQL server on MainBox and set the SQL server service to start up manually as opposed to automatic. Rename MainServer to MainBox-Old or some other name and reboot.
  4. Rename NewBox to MainServer and reboot. Now shut down SQL Server.
  5. Now, since we had the data and log paths set the same on the new server, all we need to do is copy the data and log directories over to the new machine in place of the current install. So, for example, if I had a default install where the logs and data are in the same path (C:\Program Files\Microsoft SQL Server\Data\MSSQL.1\MSSQL), I would find the MSSQL folder for my instance that has the Backup, Data, LOG, etc subfolders and rename that folder MSSQL.OLD (as a fallback). I would then copy over the MSSQL folder from my old instance to the same location as MSSQL.OLD and then I can simply restart the SQL Server and it should start with all logons, replication, and settings of the SQL instance prior to the move.