SQL Server Migration with dbatools: A Production Workflow from Start to Finish

---

Most articles about dbatools list its 500-plus cmdlets and show a handful of isolated examples. What they rarely show is a complete, ordered migration workflow where the sequence matters as much as the individual commands. Run them in the wrong order and you end up with databases on the new server missing logins, agents jobs referencing credentials that do not exist yet, or orphaned users blocking application connectivity.

This article covers a production-tested migration workflow using dbatools in the correct order. Each step explains what the command does, why it runs at that point in the sequence, and what breaks if you skip it or run it out of order. The workflow assumes you are migrating one or more databases from a source SQL Server instance to a destination instance and need everything to work correctly when you cut over.

1 Installing and Connecting

# Install dbatools (one time per machine)
Install-Module -Name dbatools -Force -Scope CurrentUser

# Import the module
Import-Module dbatools

# Test connectivity to both instances before starting
Test-DbaConnection -SqlInstance SourceServer
Test-DbaConnection -SqlInstance DestinationServer

# Optional: store credentials if not using Windows Auth
$sourceCred = Get-Credential
$destCred   = Get-Credential

2 Why Order Matters in a Migration

A SQL Server migration is not a flat list of tasks you can run in any sequence. It is a dependency chain. Agent jobs reference proxies. Proxies reference credentials. Credentials reference logins. Logins map to database users. If you copy jobs before copying the credentials they depend on, the jobs arrive on the destination broken. If you copy databases before copying logins, every database user is immediately orphaned.

The workflow below follows the correct dependency order. Every object that something else depends on arrives first.

3 Step 1: Copy the Database

The database itself goes first. Everything else in the migration depends on the database files being on the destination before any server-level objects are configured to reference it.

Copy-DbaDatabase with -BackupRestore is the safest method for most migrations. It performs a backup on the source and restores it on the destination using a shared network path both servers can access. This keeps the source database fully online during the copy and avoids the downtime of a detach/attach approach.

The -BackupRestore switch keeps the source database online the whole time. Users and applications continue reading and writing while the backup is in progress. The only downtime is the final cutover moment. The -SharedPath parameter must be a UNC path reachable by both the source SQL Server service account and the destination SQL Server service account, not just the machine running the PowerShell command.

# Step 1: Copy the database using backup and restore via a shared network path
# Both the source and destination SQL Server service accounts must have
# read/write access to the shared path

Copy-DbaDatabase `
    -Source      SourceServer `
    -Destination DestinationServer `
    -Database    YourDatabaseName `
    -BackupRestore `
    -SharedPath  '\\FileServer\SQLMigration\Backups'

# To copy multiple databases at once, pass a list to -Database:
Copy-DbaDatabase `
    -Source      SourceServer `
    -Destination DestinationServer `
    -Database    'DB1', 'DB2', 'DB3' `
    -BackupRestore `
    -SharedPath  '\\FileServer\SQLMigration\Backups'

4 Step 2: Copy Logins

Logins are server-level security principals that live in the master database. They must arrive on the destination before you do anything with database users, because every database user maps to a server login via a Security Identifier (SID). If logins arrive after databases are restored, every SQL Auth user in every restored database is immediately an orphan.

The database is already on the destination from Step 1. Without logins, every SQL Auth database user is already orphaned. The sooner logins land, the sooner you can verify and repair the user mappings. Copy-DbaLogin copies the login with its original SID, which is the critical detail. The SID match is what prevents orphans from occurring in the first place.

# Step 2: Copy all server logins from source to destination
# Copies with the original SID -- prevents orphan users from occurring
# Includes hashed passwords for SQL Auth logins -- no password reset needed

Copy-DbaLogin `
    -Source      SourceServer `
    -Destination DestinationServer

# To copy specific logins only:
Copy-DbaLogin `
    -Source      SourceServer `
    -Destination DestinationServer `
    -Login       'AppLogin1', 'AppLogin2', 'ReportUser'

# Verify logins arrived on destination:
Get-DbaLogin -SqlInstance DestinationServer | Select-Object Name, LoginType, IsDisabled

5 Step 3: Copy Linked Servers

Linked servers define connections from this SQL Server instance to other data sources, whether other SQL Server instances, Oracle databases, Excel files, or any OLE DB provider. They run early in the workflow because stored procedures and views in your migrated database may reference linked servers, and agent jobs may also reference them directly in their T-SQL steps.

# Step 3: Copy linked server definitions to the destination
# Copies the provider, data source, connection options, and security settings

Copy-DbaLinkedServer `
    -Source      SourceServer `
    -Destination DestinationServer

# To copy specific linked servers only:
Copy-DbaLinkedServer `
    -Source      SourceServer `
    -Destination DestinationServer `
    -LinkedServer 'LinkedServerName1', 'LinkedServerName2'

# Verify linked servers on destination:
Get-DbaLinkedServer -SqlInstance DestinationServer | Select-Object Name, DataSource, ProductName

6 Step 4: Copy Agent Operators

SQL Server Agent operators are named recipients for job notification emails and alerts. They are a prerequisite for copying agent jobs because jobs reference operator names in their notification settings. If an operator does not exist when a job is copied, the job arrives with its notification settings broken or stripped.

When dbatools copies an agent job, it attempts to map the notification operator name to an operator on the destination. If the operator does not exist yet, the mapping fails silently or the notification is removed from the job definition. Always copy operators first so jobs arrive intact.

# Step 4: Copy SQL Server Agent operators to the destination
# Operators must exist before agent jobs are copied

Copy-DbaAgentOperator `
    -Source      SourceServer `
    -Destination DestinationServer

# Verify operators on destination:
Get-DbaAgentOperator -SqlInstance DestinationServer | Select-Object Name, EmailAddress, IsEnabled

7 Step 5: Copy Agent Jobs

Agent jobs can now be copied because the operators they notify already exist on the destination. Note that agent jobs may reference credentials and proxies in their job steps. Those objects do not exist on the destination yet, so jobs that use proxy accounts will have broken step security until Steps 6 and 7 complete. Copy the jobs now, fix the proxy mappings afterward.

# Step 5: Copy SQL Server Agent jobs to the destination
# Jobs referencing proxy accounts will need proxy mapping fixed in Step 7

Copy-DbaAgentJob `
    -Source      SourceServer `
    -Destination DestinationServer

# To copy specific jobs only:
Copy-DbaAgentJob `
    -Source      SourceServer `
    -Destination DestinationServer `
    -Job         'NightlyBackup', 'DailyReport', 'WeeklyMaintenance'

# Verify jobs arrived:
Get-DbaAgentJob -SqlInstance DestinationServer |
    Select-Object Name, IsEnabled, LastRunDate, LastRunOutcome

# Disable all copied jobs on the destination until cutover is validated
Get-DbaAgentJob -SqlInstance DestinationServer |
    Set-DbaAgentJob -Disabled

8 Step 6: Copy Credentials

Credentials are server-level objects that store Windows identity information used by agent proxies, external scripts, and linked server security mappings. They must be copied before proxies because proxies are built on top of credentials. A proxy without its underlying credential cannot function.

A SQL Server Agent proxy maps a Windows credential to an agent subsystem (PowerShell, SSIS, CmdExec, etc.) to allow job steps to run under a specific Windows identity. If the credential does not exist when the proxy is created, the proxy creation fails. Credentials first, proxies second.

# Step 6: Copy server credentials to the destination
# Required before agent proxies can be created in Step 7

Copy-DbaCredential `
    -Source      SourceServer `
    -Destination DestinationServer

# Verify credentials on destination:
Get-DbaCredential -SqlInstance DestinationServer | Select-Object Name, Identity

9 Step 7: Copy Agent Proxies

Agent proxies can now be copied because the credentials they reference were created in Step 6. Once proxies exist on the destination, the agent job steps that were copied in Step 5 will have their proxy mappings available and will execute under the correct Windows identity.

# Step 7: Copy SQL Server Agent proxies to the destination
# Credentials from Step 6 must exist before this step

Copy-DbaAgentProxy `
    -Source      SourceServer `
    -Destination DestinationServer

# Verify proxies and their credential mappings:
Get-DbaAgentProxy -SqlInstance DestinationServer |
    Select-Object Name, CredentialName, IsEnabled

10 Step 8: Copy Database Mail

Database Mail configuration includes the mail profile name, SMTP server settings, and the account definitions used to send email. Agent jobs use Database Mail to send operator notifications, so the mail configuration needs to be in place before jobs start running on the destination. It also runs late enough in the workflow that the operator definitions it references (copied in Step 4) already exist.

# Step 8: Copy Database Mail configuration to the destination
# Copies mail profiles, accounts, and profile-to-account mappings

Copy-DbaDbMail `
    -Source      SourceServer `
    -Destination DestinationServer

# Verify Database Mail is configured and enabled:
Get-DbaDbMailProfile -SqlInstance DestinationServer
Get-DbaDbMailAccount  -SqlInstance DestinationServer

# Test that Database Mail can actually send on the destination:
Send-DbaDbMailMessage `
    -SqlInstance DestinationServer `
    -To          'dba@yourorg.com' `
    -Subject     'dbatools Migration: Mail Test' `
    -Body        'Database Mail is working on the destination server.' `
    -Profile     'YourMailProfileName'

11 Step 9: Fix the Database Owner

When a database is restored to a new server, the owner recorded in the database may no longer be valid on the destination. The owner SID stored in the database header references a login on the source server that may not exist on the destination, or may have a different SID. An invalid database owner can cause issues with agent jobs that use database ownership chaining, certain backup operations, and any code that checks db_owner membership through the dbo user.

Set-DbaDatabaseOwner sets the database owner to sa by default, which is the standard production setting for most databases. You can specify a different owner if your environment requires it.

# Step 9: Fix the database owner on the destination
# Sets owner to sa by default, which is the standard production setting

Set-DbaDatabaseOwner -SqlInstance DestinationServer

# To set a specific owner on all databases:
Set-DbaDatabaseOwner -SqlInstance DestinationServer -TargetLogin 'sa'

# To fix a specific database only:
Set-DbaDatabaseOwner -SqlInstance DestinationServer -Database YourDatabaseName -TargetLogin 'sa'

# Verify:
Get-DbaDatabase -SqlInstance DestinationServer |
    Select-Object Name, Owner |
    Where-Object Name -notin 'master','model','msdb','tempdb'

12 Step 10: Set Compatibility Level

Database compatibility level controls which query optimizer behaviors, cardinality estimator version, and T-SQL features are available to queries in that database. When you migrate to a newer SQL Server version, the database arrives with the source server’s compatibility level still set. Changing it immediately gives you the new server’s capabilities, but it also changes optimizer behavior, which can produce different execution plans for existing queries.

The right approach is to leave the compatibility level at the source level initially, validate that the application works correctly, and then raise it in a controlled test. The examples below show the most common scenarios.

# Step 10a: Set compatibility to SQL Server 2019 (level 150) -- most common upgrade target
Set-DbaDbCompatibility `
    -SqlInstance         DestinationServer `
    -TargetCompatibility 150

# Step 10b: Set a specific named instance to SQL Server 2016 compatibility (level 130)
# Use this when migrating to a newer engine but keeping old optimizer behavior temporarily
Set-DbaDbCompatibility `
    -SqlInstance         localhost\InstanceName `
    -TargetCompatibility 13

# Step 10c: Set a SQL Server 2017 instance to SQL Server 2014 compatibility (level 120)
Set-DbaDbCompatibility `
    -SqlInstance         localhost\sql2017 `
    -TargetCompatibility 12

# Verify compatibility levels on all user databases:
Get-DbaDatabase -SqlInstance DestinationServer |
    Where-Object Name -notin 'master','model','msdb','tempdb' |
    Select-Object Name, Compatibility

SQL Server Version	Compatibility Level	TargetCompatibility Parameter
SQL Server 2025	170	170
SQL Server 2022	160	160
SQL Server 2019	150	150
SQL Server 2017	140	14 or 140
SQL Server 2016	130	13 or 130
SQL Server 2014	120	12 or 120
SQL Server 2012	110	11 or 110

13 Step 11: Repair Orphan Users

This is the final step and one of the most important. Even when logins are copied with the correct SID in Step 2, edge cases can leave database users without a matching server login. This can happen when a login was deleted and recreated on the source between your last login sync and the migration, when Windows logins reference accounts that do not exist in the destination environment, or when the SID copy did not work correctly for certain login types.

Repair-DbaDbOrphanUser finds all orphaned database users and re-links them to the server login of the same name. It is the automated equivalent of running ALTER USER username WITH LOGIN = username for every orphaned user in every database.

# Step 11: Find and repair all orphaned users across all databases
# This re-links database users to server logins of the same name
# The login must already exist (Step 2) for the repair to succeed

Repair-DbaDbOrphanUser -SqlInstance DestinationServer

# To repair a specific database only:
Repair-DbaDbOrphanUser `
    -SqlInstance DestinationServer `
    -Database    YourDatabaseName

# Check for any remaining orphans after repair:
Get-DbaDbOrphanUser -SqlInstance DestinationServer

14 The Complete Script

All 11 steps in order, ready to copy and adapt for your environment. Replace the server names, database name, shared path, and compatibility level with your actual values.

# ============================================================
# SQLYARD: dbatools SQL Server Migration Workflow
# Replace all placeholder values before running
# ============================================================

$source      = 'SourceServer'
$destination = 'DestinationServer'
$database    = 'YourDatabaseName'
$sharedPath  = '\\FileServer\SQLMigration\Backups'
$targetCompat = 150   # set to your target compatibility level

# STEP 1: Copy the database via backup and restore
Write-Host "Step 1: Copying database..." -ForegroundColor Cyan
Copy-DbaDatabase `
    -Source      $source `
    -Destination $destination `
    -Database    $database `
    -BackupRestore `
    -SharedPath  $sharedPath

# STEP 2: Copy server logins (with original SIDs to prevent orphans)
Write-Host "Step 2: Copying logins..." -ForegroundColor Cyan
Copy-DbaLogin `
    -Source      $source `
    -Destination $destination

# STEP 3: Copy linked servers
Write-Host "Step 3: Copying linked servers..." -ForegroundColor Cyan
Copy-DbaLinkedServer `
    -Source      $source `
    -Destination $destination

# STEP 4: Copy agent operators (must exist before jobs are copied)
Write-Host "Step 4: Copying agent operators..." -ForegroundColor Cyan
Copy-DbaAgentOperator `
    -Source      $source `
    -Destination $destination

# STEP 5: Copy agent jobs (operators must already exist)
Write-Host "Step 5: Copying agent jobs..." -ForegroundColor Cyan
Copy-DbaAgentJob `
    -Source      $source `
    -Destination $destination

# Disable all jobs on destination until migration is validated
Get-DbaAgentJob -SqlInstance $destination | Set-DbaAgentJob -Disabled
Write-Host "All destination jobs disabled until cutover validation." -ForegroundColor Yellow

# STEP 6: Copy credentials (must exist before proxies are copied)
Write-Host "Step 6: Copying credentials..." -ForegroundColor Cyan
Copy-DbaCredential `
    -Source      $source `
    -Destination $destination

# STEP 7: Copy agent proxies (credentials must already exist)
Write-Host "Step 7: Copying agent proxies..." -ForegroundColor Cyan
Copy-DbaAgentProxy `
    -Source      $source `
    -Destination $destination

# STEP 8: Copy Database Mail configuration
Write-Host "Step 8: Copying Database Mail..." -ForegroundColor Cyan
Copy-DbaDbMail `
    -Source      $source `
    -Destination $destination

# STEP 9: Fix database owner on destination
Write-Host "Step 9: Setting database owner to sa..." -ForegroundColor Cyan
Set-DbaDatabaseOwner `
    -SqlInstance $destination `
    -TargetLogin 'sa'

# STEP 10: Set compatibility level
Write-Host "Step 10: Setting compatibility level to $targetCompat..." -ForegroundColor Cyan
Set-DbaDbCompatibility `
    -SqlInstance         $destination `
    -TargetCompatibility $targetCompat

# STEP 11: Repair orphan users
Write-Host "Step 11: Repairing orphan users..." -ForegroundColor Cyan
Repair-DbaDbOrphanUser -SqlInstance $destination

Write-Host "Migration workflow complete. Run post-migration validation." -ForegroundColor Green

15 Post-Migration Validation

After the workflow completes, run these checks before declaring the migration done and enabling agent jobs.

# Confirm database is online and accessible
Get-DbaDatabase -SqlInstance $destination -Database $database |
    Select-Object Name, Status, RecoveryModel, Compatibility

# Confirm no orphan users remain
$orphans = Get-DbaDbOrphanUser -SqlInstance $destination
if ($orphans) {
    Write-Warning "Orphan users still exist:"
    $orphans | Select-Object SqlInstance, Database, UserName
} else {
    Write-Host "No orphan users found." -ForegroundColor Green
}

# Confirm logins arrived with correct SIDs
# (compare source and destination SIDs for each login)
$sourceLogins = Get-DbaLogin -SqlInstance $source      | Select-Object Name, @{N='SID';E={$_.Sid -join ','}}
$destLogins   = Get-DbaLogin -SqlInstance $destination | Select-Object Name, @{N='SID';E={$_.Sid -join ','}}
Compare-Object $sourceLogins $destLogins -Property Name

# Confirm agent jobs are present
Get-DbaAgentJob -SqlInstance $destination |
    Select-Object Name, IsEnabled, OwnerLoginName

# Confirm linked server connectivity
Test-DbaLinkedServerConnection -SqlInstance $destination

# Confirm Database Mail is working
Send-DbaDbMailMessage `
    -SqlInstance $destination `
    -To          'dba@yourorg.com' `
    -Subject     'Migration Validation: Mail Check' `
    -Body        'Post-migration Database Mail test.' `
    -Profile     'YourMailProfileName'

References

• dbatools.io: Official Documentation and Command Reference
• dbatools: Copy-DbaDatabase
• dbatools: Copy-DbaLogin
• dbatools: Copy-DbaLinkedServer
• dbatools: Copy-DbaAgentOperator
• dbatools: Copy-DbaAgentJob
• dbatools: Copy-DbaCredential
• dbatools: Copy-DbaAgentProxy
• dbatools: Copy-DbaDbMail
• dbatools: Set-DbaDatabaseOwner
• dbatools: Set-DbaDbCompatibility
• dbatools: Repair-DbaDbOrphanUser
• SQLYARD: SQL Server Orphan Users
• Microsoft Docs: ALTER DATABASE Compatibility Level