PowerShell Learning – SQL Migration for the SQL DBA — Part 2 of 46 min read

Continuing on my PowerShell learning journey, this is part two of my series for the SQL DBA.

This will focus on the DBATools utility that was originally built for SQL Migration by Chrissy LeMaire (t) and has evolved into an awesome tool that will help your average DBA automate tasks and be able to get a glimpse in to all of their SQL Servers without having to learn the guts of PowerShell. It is all presented to you with great help and community support.

Today I have a project that is migrating SQL Server 2012 to SQL Server 2016. This process will be performed for 12 development environments before finally happening in production. The DBATools utility is perfect for this effort. I can create the process with the appropriate functions and then we have a repeatable process I can use to make this happen for each of my servers in this project.

Pre-Migration Steps:

There are a few features we use in our environment that the DBATools utility does not handle, so I am performing these manually until I can learn enough PowerShell to script these items on my own.

Service Start Mode Settings

Note: Originally we did not deploy all services when servers were built.  However, as things evolved we decided to install all services and disable the ones not in use.  This allows us to enable and configure them if the request came in without having to install and reboot.

Current Server Services Startup

New Server Services Startup

Startup Parameters

Current Server Startup Parameters

New Server Startup Parameters

Custom SQL Port

Current Server TCP SQL Port

New Server TCP SQL Port

SSL Certificate

Current Server SSL Certificate

New Server SSL Certificate

TDE Master Key

On the new server I run this command to create a Master Key for use with TDE.

Encryption Certificate

From the old server I run the BACKUP Certificate then copy the files created to the new server and CREATE Certificate from files that were backed up from old server.

Configure SSIS Catalog on New Server

We use SSIS Catalog and the best migration method I have found is to perform the generic configuration on the new server then migrate the database with the DBATools migration.



Copy Files on File System

This server hosts a 3rd Party database that uses a couple of files stored on the file system.  I have copied these files over manually to the proper location.

Migration Steps:

Now on to the fun stuff;  Let’s fire up DBATools and get a major SQL migration going.

New Server SQL Display before Migration

New Server SQL Agent Display before Migration

Now on to the DBATools migration steps.

First thing we do is load the DBATools module.

Then we are going execute the Start-SqlMigration function with all of our parameters:

So here is the one command that will save you time and frustration.  Here is what the Start-SqlMigration is doing.

  • -Source = Your current server
  • -Destination = Your new server
  • -NetworkShare = The shared network path that both servers can access
  • -NoRecovery = Will leave the migrated databases in NO RECOVERY state
  • -BackupRestore = This will take a full backup of the databases to migrate and then restore on new server
  • -DisableJobsOnDestination = This will migrate SQL Agent Jobs and leave them in a disabled state
  • -NoSaRename = Will not change SA account on new server
  • -WithReplace = This will overwrite the database if exists.  I did this because I had to pre-build SSISDB

New Server after Migration

New Server SQL Agent after Migration

So in just 1 minute and 34 seconds you have migrated all of the following from one server to another.

  • All SP_Configure settings
  • Any Custom Error Messages
  • Any SQL Credentials
  • All Linked Servers
  • Database Mail along with Configuration and Profiles and Accounts
  • All user objects in System Databases
  • All Backup Devices
  • Any System Triggers
  • All User Databases
  • All Logins
  • Any Data collectors
  • Any Security Audits
  • All Endpoints, Policy Management, Resource Governor, Extended Events
  • And Finally All SQL Server Agent Jobs, Schedules, Operators, Alerts

Post-Migration Steps:

Bring Databases Online


Due to dependencies we have for our 3rd party product it is crucial we set Trustworthy to TRUE.

DB Owner

This will set all database owners to SA where they do not currently match SA

Enable SQL Agent Jobs

I migrated all SQL Agent Jobs in a disabled state, so they would not attempt to run before all migration steps have been confirmed.

Enable all jobs via SQL Agent

Disable ReportServer databases


In conclusion while I have a lot of steps outside of the DBATool set, that is specific to my environment.  The core of the migration is done with DBATools and took a little over a minute.

I love DBATools and it has many more functions that help extend the reach of the DBA.

Leave a Reply

Your email address will not be published. Required fields are marked *