How to Perform a SQL Server Migration?

Why Plan a SQL Server Migration?

Four events trigger most migrations:

• Hardware. Old servers reach the end of their life and need replacement.
• Versions. Older releases lose support. Teams move to a newer SQL Server.
• Cloud. Companies move databases to Azure to cut maintenance.
• Mergers. Combined companies consolidate databases on fewer servers.

What Types of SQL Server Migration Exist?

Four types fit almost every project:

Which Checks Come Before a SQL Server Migration?

Seven checks come first:

1. Record the source version, edition and service pack.
2. Decide how much downtime the business accepts.
3. Create backups before anything else. The Explore Different Types of Backups in SQL Server guide has every backup script.
4. Note the collation of your databases and match it on the destination.
5. List the logins, linked servers and Agent tasks of the application.
6. Confirm free disk space on the destination for the data and log files.
7. Prepare a test copy and a rollback plan.

5 Methods for SQL Server Migration

Four methods use tools from Microsoft and the fifth adds software for live databases and MDF files.

Method #1: Restore a Backup on the New Server (Free, Best Default)

Backup and restore is the standard path because the source database remains untouched. Five steps restore the database:

1. Right click the database in Object Explorer and select Tasks and then Back Up.
2. Add a destination path and click OK.
3. Move the backup file to the new server.
4. Right click Databases on the new server and select Restore Database.
5. Select Device, add the file and click OK.

Note: Logins sit in the master database. Create them on the new server by hand. Restore dialogs are explained in Restore a SQL Database From a BAK File.

Method #2: Detach and Attach the Data Files (Free, Source Goes Offline)

Detach frees the MDF and LDF files so you can move them. Four steps move the files:

1. Right click the database and select Tasks and then Detach.
2. Move the MDF and LDF files to the destination server.
3. Right click Databases on the destination and select Attach.
4. Click Add, select the MDF file and click OK.

Note: Attach upgrades the database to the newer release when the destination is newer. Keep your backup because the upgrade cannot be reversed.

Method #3: Use Copy Database Wizard (Free, Moves Logins)

Copy Database Wizard moves the database and the logins that belong to it. The wizard needs the sysadmin role on both servers. Five steps copy the database:

1. Right click the database and select Tasks and then Copy Database.
2. Enter the destination server and the authentication.
3. Select the transfer method.
4. Mark Copy so the source database remains online.
5. Add the logins on the server objects page and finish the wizard.

Note: Express edition does not include the wizard. The Copy SQL Database From One Server to Another guide compares both transfer methods.

Method #4: Move a BACPAC File (Free, Works Across Editions)

A BACPAC file packs the schema and data of a single database. Four steps export and import the file:

1. Right click the database and select Tasks and then Export Data-tier Application.
2. Save the file to a local disk with the .bacpac extension.
3. Right click Databases on the destination and select Import Data-tier Application.
4. Browse to the file and type the new database name.

Note: BACPAC files include the database but not the logins. Microsoft recommends the SqlPackage tool for large databases.

Method #5: Migrate a Live SQL Server Database With EmailDoctor (Free Preview, Paid Export)

EmailDoctor SQL Server Data Migration Tool handles four routes from one window. The migration software moves data from a live server to a live server or to SQL scripts and exports offline MDF files to a live server or to SQL scripts.

Trial mode previews every object. Exports in trial stop at 25 records per table and 5 other categories until you buy a license.

Connect to the Source Server

Six steps connect to the source and show its objects:

1. Start the software and click Open.
2. Select Online mode for a live server.
3. Add the Server Name with Authentication of the source.
4. Select the database from the Drop Down List and click OK.
5. Expand the tree and inspect tables, views and stored procedures.
6. Click Export.

Note: Offline mode opens an MDF file that no server uses. Select Advance Scan when the file is damaged.

Export to the Destination Server

Seven steps send the database to the destination:

7. Select SQL Server Database to export to a server or SQL Server Compatible SQL Scripts to create script files.
8. Add the Server Name with Authentication of the destination.
9. Choose Create New Database or Export to Existing Database.
10. Set Collation Settings to match the source. The software uses the server default when you select nothing.
11. Tick the objects to move.
12. Choose With only Schema for structure or With Schema & Data for rows.
13. Click Save and close the Export Completed message.

Note: The software does not perform a redundancy check. Export to a new database when the destination already has objects with the same names.

Which Tasks Follow a SQL Server Migration?

Five tasks follow every method:

1. Create a fresh backup of every migrated database.
2. Verify the database owners and the logins of the application:

SELECT name, SUSER_SNAME(owner_sid) AS owner FROM sys.databases;

3. Refresh the statistics:

USE ShopDB;
EXEC sp_updatestats;

4. Check the compatibility level and raise it only after application tests:

SELECT name, compatibility_level FROM sys.databases;

5. Test the application and save the old server for one week.

   Microsoft states that a restored backup retains its compatibility level when the level is 100 or higher. Raise the level on purpose and not by accident.

Which Problems Stop a SQL Server Migration?

Five problems appear most:

Frequently Asked Questions

Author’s Verdict

Backup and restore suits most migrations because the source remains untouched. Detach and attach saves time on large files but takes the source offline.

Copy Database Wizard moves logins. BACPAC files work across editions. Migration software fits live databases, MDF files and script output.

Test the application on the new server before the cutover. Save the old backups and the old server for a week.