Moving Database Between SQL Servers

This includes two stages:

1. Testing stage:

This stages includes two steps:

  1. Moving the database from one SQL server to another without affecting the operating database.
    • Backup the database.
    • Restore the database from the backup file to the new server suing Microsoft SQL Server Management Studio. Choose the same name and the database will be created acoordingly. Pay attention to the options page that allows you choose the location of data base files (.mdf and .ldf)
    • Transfer logins and passwords.
    • Configure firewall to allow port 1433 (for connection) and 1434 (for SQL browsing).
    • Transfer SSIS packages.
  2. Change application settings to use the new server. A well designed application should have only one place storing the database location, so redirecting to the new database usually means changing one string.
  3. Fully testing the applications using the new database.

2. Final migration:

  1. Notify users of the temporary server stopping
  2. Detach the database from the original server.
  3. Move the detached database files (.mdf and .ldf) to the desired new location.
  4. Attach the database to the destination server.
  5. Transfer logins and passwords.
  6. Configure firewall to allow port 1433 (for connection) and 1434 (for SQL browsing).
  7. Transfer SSIS packages.
  8. Deploy all new versions of applications using the database at the new location.

 

(originally written on 2009-10-25)

You should also read: