dbatools is a free PowerShell module with over 100 SQL Server administration, best practice and migration commands included.


Start-SqlMigration

This command brings a number of commands together, which is useful when you’re looking to migrate entire SQL Server instances. It’s less flexible than using the underlying commands individually, but it’s easy and fast.

easy

Like Copy-SqlDatabases, Start-SqlMigration currently supports migrating via backup/restore and detach/attach. Mirroring and Log Shipping will be supported in the near-future. See the examples below for more information.

Migrated objects

All user databases. Use -NoDatabases to skip.
All logins. Use -NoLogins to skip.
All database mail objects. Use -NoDatabaseMail
All credentials. Use -NoCredentials to skip.
All objects within the Job Server (SQL Agent). Use -NoJobServer to skip.
All linked servers. Use -NoLinkedServers to skip.
All groups and servers within Central Management Server. Use -NoCentralManagementServer to skip.
All SQL Server configuration objects (everything in sp_configure). Use -NoSpConfigure to skip.
All user objects in system databases. Use -NoSysDbUserObjects to skip.
All system triggers. Use -NoSystemTriggers to skip.
All system backup devices. Use -NoBackupDevices to skip.
All Audits. Use -NoAudits to skip.
All Endpoints. Use -NoEndpoints to skip.
All Extended Events. Use -NoExtendedEvents to skip.
All Policy Management objects. Use -NoPolicyManagement to skip.
All Resource Governor objects. Use -NoResourceGovernor to skip.
All Server Audit Specifications. Use -NoServerAuditSpecifications to skip.
All Custom Errors (User Defined Messages). Use -NoCustomErrors to skip.

Examples

Migrate databases uses backup/restore. Also migrate everything else listed above. Note that the SQL Server service account must have access to the network share. Databases will /not/ be overwritten if they exist at the destination.

Migrate everything but databases and logins. Login to source server using SQL authentication, and to destination server using integrated (Windows) authentication.

Migrate databases using detach/copy/attach. Reattach at source and set source databases read-only. If database exists on destination, it will be dropped and recreated using attach.

Video demo

In this video, I give a tour of a SQL Server 2012 instance and migrate to an empty(ish) SQL Server 2016 instance. There’s sound because I didn’t have time to obsess 😉

Migration transcript

I know that demo went pretty fast. Here’s the transcript so that you can examine line by line.

From PowerShell, execute   Get-Help Start-SqlMigration -Detailed   for more information on this function. Want to see the source code? See it on GitHub