When it comes down to actually migrating from a legacy version of SQL Server to SQL Server 2016/2017, there are several different methods for performing the migration, depending on what legacy version of SQL Server your are using. Here is a high-level overview of the various migration methods, by version:

Migrating from SQL Server 2005

Backup and Restore: You can restore a SQL Server 2005 database backup to SQL Server 2016. The database compatibility level will be automatically changed to 100 when you do this

Migration tools: You can use the Microsoft Data Migration Assistant (DMA), which is especially useful for moving server level objects

Bulk load: You can bulk copy tables from SQL Server 2005 to SQL Server 2016

 

Migrating from SQL Server 2008 or SQL Server 2008 R2

Backup and Restore: You can restore a SQL Server 2008 or SQL Server 2008 R2 database backup to SQL Server 2016

Migration tools: You can use the Microsoft Data Migration Assistant (DMA), which is especially useful for moving server level objects

Bulk load: You can bulk copy tables from SQL Server 2008 or SQL Server 2008 R2 to SQL Server 2016

Database mirroring: Database mirroring is supported if the principal instance is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Log shipping: Log shipping is supported if the primary instance is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

 

Migrating from SQL Server 2012

Backup and Restore: You can restore a SQL Server 2012 database backup to SQL Server 2016

Migration tools: You can use the Microsoft Data Migration Assistant (DMA), which is especially useful for moving server level objects

Bulk load: You can bulk copy tables from SQL Server 2012 to SQL Server 2016

Availability group: Availability Groups are supported if the primary replica is running SQL Server 2012 SP2 or later and the secondary replicas are running SQL Server 2016. The AG failover is a one-time, one-way trip

Database mirroring: Database mirroring is supported if the principal instance is running SQL Server 2012 SP1 or later, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Log shipping: Log shipping is supported if the primary instance is running SQL Server 2012 SP1 or later, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Transactional replication: SQL Server transactional replication from SQL Server 2012 to SQL Server 2016 is supported

 

Migrating from SQL Server 2014

Backup and Restore: You can restore a SQL Server 2014 database backup to SQL Server 2016

Migration tools: You can use the Microsoft Data Migration Assistant (DMA), which is especially useful for moving server level objects

Bulk load: You can bulk copy tables from SQL Server 2014 to SQL Server 2016

Availability group: Availability Groups are supported if the primary replica is running SQL Server 2014 and the secondary replicas are running SQL Server 2016. The AG failover is a one-time, one-way trip

Database mirroring: Database mirroring is supported if the principal instance is running SQL Server 2014, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Log shipping: Log shipping is supported if the primary instance is running SQL Server 2014, and the secondary instance is running SQL Server 2016. The database failover is a one-time, one-way trip

Transactional replication: SQL Server transactional replication from SQL Server 2012 to SQL Server 2016 is supported

 

Of these various methods, I generally prefer log shipping or database mirroring, since they are both easy to configure and use, and they offer very short down times for the actual Production migration. It is important that your legacy version of SQL Server has been patched to a new enough Service Pack (preferably the latest one available) so that you can use one of these methods.

 

 

Additional Resources

I have a new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017.

Here is a link to the complete series about upgrading SQL Server.