Tuesday, 17 April 2012

Move all SQL databases from one server to another

When it comes to moving databases there a number of different methods that you can use. The best method will depend on the situation, but most comments/guides/blogs online concentrate on moving individual databases, either by importing/exporting/copying the data between servers using SSMS, backing up and restoring the databases, or detaching and attaching them.

The method described here is a way to move all of the MS SQL databases from one server to another. The basic idea is to stop all of the MS SQL Server services on both servers, copy all of the data files including the system database files (eg the .mdf, and .ldf files) to the same location(s) on the new server, and then start SQL Server on the new server.

There are a few key points to note about this method when considering if it is suitable to your situation :

  • The new and old servers must have the same version and build of SQL Server installed.
  • The file paths for your data files must be the same on both servers.
  • It doesn't matter if the two servers have different names.
  • The new server must have enough storage space to hold the data files in their current location.
  • Depending on the amount of data this method can involve a long window of downtime while the data copies over the network

Before stopping SQL Server and starting the move it's worth considering any housekeeping tasks that might need doing. A couple of things I've done before starting a move :

  • Remove any old / unwanted databases. This process moves all of them, so if they're no longer required why waste time copying a database you don't need.
  • Consider shrinking database files. Keeping your transaction log files large can avoid situations where the file has to grow, and shrinking isn't generally recommended, however, copying gigs of empty space over a network will needlessly increase the time this process takes. Therefore it may be worth shrinking the file initially, and then manually increasing it again once it's running on the new server.

The steps to the move are :

  • Install SQL Server on the new server, ensuring it's the same version and build as the old server, and with the same options installed. Ensure that your system database location is configured to be the same as what you have on the old server.
  • Stop all of the SQL Server services on both servers.
  • Copy all of the database mdf, ldf and ndf files from the old server to the new server, including all of the system database files. Each file must be copied to the same location on the new server as it was on the old server, otherwise SQL won't be able to find them when when it starts.
  • Copy any other related files that will be required, eg if any jobs are configured to access or use files on the server then copy them as well.
  • Start the SQL Server services on the new server.
  • Open SSMS and confirm that all of the databases are being shown and are accessible.
  • Either update your application(s) or site(s) to use the new server, or change the server name and network details to match that of the old server so they can connect with the existing details.
  • Test and confirm that everything is working correctly.


I've successfully used this method on SQL Server 2008 R2, however it should also work on all other versions and in fact other people have confirmed it to work with SQL 7 and 2000.

The installations were fairly straight forward setups, with no replication, clustering, Analysis or Reporting services configured, so while I doubt they would stop this from working I can't confirm that for certain.

If you're unsure of the exact setup of your existing installation, check the "Server Server Installation Center" on the old server, go to Tools, and click on "Installed SQL Server features discovery report". This will generate a report showing all of the components installed and what build that are currently.