Migrating SQL Server Databases with dbatools

I worked on a migration project where two production databases were moved from a legacy standalone SQL Server 2014 instance to a high availability environment running SQL Server 2022. The goal was to improve scalability, enhance performance, and reduce the risk of downtime. With dbatools, the entire process (backup, restore, login migration, and Availability Group setup) was handled with minimal effort and solid control.

All credit to Chrissy LeMaire, who created dbatools and made SQL Server migrations like this so much more approachable. For this particular migration, I followed a plan inspired by a LifeHacks video by Jess Pomfret, which walks through the process in a really clear, practical way. What you’ll see below is a version of that workflow, adjusted slightly to fit our environment.

Before the Migration Window

We started by listing what needed to move and making sure the environment was ready.

Migration Targets

Database	DataSizeMB	LogSizeMB	TotalSizeMB	Compatibility
DB_VIDEO	5636	3087	8723	Version120
DB_VIDEO_V2	622	71.69	693	Version120

And the associated application logins:

	Type
app_vid_1	SQL login
app_vid_2	SQL login
app_vid_3	SQL login

During the Migration Window

1. Confirm Application Shutdown

Before anything else, the application team stopped all running services to avoid incoming traffic during the move.

2. Check and Kill Active Connections

We made sure no connections were hanging around:

# Double check Processes
$processSplat = @{
	SqlInstance = 'PRO-SQL14-DB1'
	Database    = 'DB_VIDEO_V2','DB_VIDEO'
}
Get-DbaProcess @processSplat |
Select-Object SqlInstance, Database, Host, Login, Program | Format-Table

# Kill Left-over Processes
Get-DbaProcess @processSplat | Stop-DbaProcessCopyEdit

3. Start Database Migration

This did a backup/restore and set the source database to read-only in one go:

$migrateDbSplat = @{
	Source            = 'PRO-SQL14-DB1'
	Destination       = 'PRO-SQL22-DB2'
	Database          = 'DB_VIDEO_V2','DB_VIDEO'
	BackupRestore     = $true
	SharedPath        = '\\PRO-SQL22-DB2\SQLBackups\Migration\DB_VIDEO\Primary'     
	SetSourceReadOnly = $true     
	Verbose           = $true 
} 
Copy-DbaDatabase @migrateDbSplat

4. Add the Databases to the SQL Server 2022 Availability Group

$adddbAGSplat = @{     
	SqlInstance       = 'PRO-SQL22-DB2'     
	Secondary         = 'PRO-SQL22-DB1'     
	AvailabilityGroup = 'ag-XX-dbcluster'     
	Database         = 'DB_VIDEO','DB_VIDEO_V2'     
	SharedPath       = '\\PRO-SQL22-DB2\SQLBackups\Migration\DB_VIDEO\Secondary'     
	Verbose           = $true 
} 
Add-DbaAgDatabase @adddbAGSplat -SeedingMode Automatic

5. Migrate Application Logins

This kept the original passwords and SIDs:

$migrateLoginSplat = @{     
	Source       = 'PRO-SQL14-DB1'     
	Destination  = 'PRO-SQL22-DB2'     
	Login        = 'app_vid_agt','app_vid_uploader','app_vidcore'    
	Verbose      = $true 
} 
Copy-DbaLogin @migrateLoginSplat -ExcludeSystemLogins -Force

6. Upgrade Compatibility Level and Run Maintenance

We upgraded the compatibility and ran integrity checks to finalize the upgrade:

# Get databases
$databaseSplat = @{
    SqlInstance = 'PRO-SQL22-DB2'
    ExcludeSystem = $true
    OutVariable   = "dbs"  # OutVariable to also capture this to use later
}
Get-DbaDatabase @databaseSplat -Database 'DB_VIDEO_V2','DB_VIDEO'|
Select-Object Name, Status, RecoveryModel, Owner, Compatibility | FT

# Updates compatibility level
# runs CHECKDB with data_purity - make sure column values are in range, e.g. datetime
# DBCC updateusage
# sp_updatestats
# sp_refreshview against all user views
$upgradeSplat = @{     
SqlInstance = 'PRO-SQL22-DB2'      
Database    = 'DB_VIDEO_V2','DB_VIDEO' 
} 
Invoke-DbaDbUpgrade @upgradeSplat -Force -Verbose

Post-Migration Checklist

• Confirmed that the application could connect successfully to the new SQL Server (PRO-SQL22-DB2)

• Enabled encrypted connections for the video apps

• Validated connections and processes

$processSplat = @{     
	SqlInstance = 'PRO-SQL22-DB2'     
	Database    = 'DB_VIDEO','DB_VIDEO_V2' 
} 
Get-DbaProcess @processSplat | Select-Object SqlInstance, Database, Host, Login, Program | Format-Table

• Set the old databases to offline (but kept them intact as a fallback)

$offlineSplat = @{     
	SqlInstance = 'PRO-SQL14-DB1'     
	Database    = 'DB_VIDEO_V2','DB_VIDEO'     
	Offline     = $true     
	Force       = $true 
} 
Set-DbaDbState @offlineSplat

Backout Plan

In case something had gone wrong, we could easily reverse by bringing the source DBs back online and redirecting the apps:

$offlineSplat = @{     
	SqlInstance = 'PRO-SQL14-DB1'     
	Database    = 'DB_VIDEO_V2','DB_VIDEO'     
	Online      = $true     
	Force       = $true 
} 
Set-DbaDbState @offlineSplat

Final Thoughts

This was a smooth and structured migration, thanks in large part to the tools available in dbatools. The ability to backup, restore, kill processes, sync AGs, migrate logins, and upgrade (all in a single toolkit) makes a huge difference when you're under tight downtime constraints.

We also opened up discussions around implementing data retention policies for this database post-migration. Because once you're on a stable setup, it's a good time to rethink long-term strategy.

If you’re doing migrations like this often, or just want to reduce risk and save time, take a look at what dbatools offers. It’s free, open-source, and there’s a whole community behind it.