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

Please note that documentation and command names may be out of date while we work furiously towards 1.0


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.

Get-Help

From PowerShell, execute Get-Help Start-SqlMigration -Detailed for more information on this function.

Source Code

Want to see the source code? View Start-SqlMigration.ps1 on GitHub
 

Related commands