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.
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.
All user databases to exclude support databases such as ReportServerTempDB (Use -IncludeSupportDbs for this). 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.
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.
Start-SqlMigration -Source sql2014 -Destination sql2016 -BackupRestore -NetworkShare \\nas\sql\migration
Migrate everything but databases and logins. Login to source server using SQL authentication, and to destination server using integrated (Windows) authentication.
Start-SqlMigration -Verbose -Source sqlcluster -Destination sql2016 -SourceSqlCredential $storedcredential -NoDatabases
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.
Start-SqlMigration -Verbose -Source sqlcluster -Destination sql2016 -DetachAttach -Reattach -SetSourceReadonly -Force
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 😉
I know that demo went pretty fast. Here’s the transcript so that you can examine line by line.