Two-phase database migrations via a WinForms GUI -
even without a direct network connection between the source and target server
Uwe Janke · Senior SQL Server DBA · dtcSoftware
SQL Server migrations in complex network environments - manual, error-prone, not reproducible.
Source and target server in separate network segments, a DMZ, or different data centers - no direct SQL access possible
What was migrated? Which databases? Which version? No structured log, no evidence
Backup on the source, transfer the file, restore on the target, create logins manually, recreate jobs - different every time
A new migration? A different DBA? No defined process - reinvented every time, different every time
Logins, SQL Agent jobs, linked servers, or SSIS packages get forgotten or migrated inconsistently
No way to simulate the migration beforehand - errors only surface in production
One tool, one process, one log. Pick a role - the rest runs automatically. Even without a direct network connection.
Role selection at startup (Source / Target / Auto). Only the relevant side is shown
Phase 1 on the source server, phase 2 on the target server - a JSON state file as the secure link
Target server reachable: direct mode. Not reachable: TwoPhase mode. Automatic, no intervention needed
| Before | After |
|---|---|
| Manual multi-hour process | Automated, structured |
| Separate networks = manual data transfer | TwoPhase mode with a state file |
| Logins / jobs forgotten | Complete object migration |
| No log, no evidence | Structured log, every step |
| No testing possible | WhatIf simulates everything in advance |
Migrations work - even when the source and target don't know each other.
Core idea: the migration is split into two independent phases that communicate via a JSON state file.
All databases, logins, jobs, and linked servers are read in and offered for selection
Selected databases are secured via backup/restore or detach/attach (with compression)
Logins, Agent jobs, linked servers, and optionally SSIS packages are serialized into the state file
The JSON file with all migration information is loaded from the exchange path and validated
Databases are restored on the target server - including backup path mapping
Create logins, import jobs, set up linked servers. A complete log of every step
The classic, safe method. The database stays online on the source server while the backup runs.
| ✓ | Online operation possible |
| ✓ | Compression reduces file size |
| ✓ | Checksum validation |
| ~ | Longer migration time |
A fast method for planned maintenance windows. The database is briefly taken offline.
| ✓ | Faster data transfer |
| ✓ | No backup overhead |
| ~ | Brief downtime needed |
| ~ | A maintenance window is required |
| Object | Method | Notes |
|---|---|---|
| Databases | Backup/Restore or Detach/Attach | With path mapping for differing drives |
| Logins | SID transfer via dbatools | Windows and SQL logins, DB permissions |
| SQL Agent Jobs | Export / import | AG sync jobs can optionally be excluded |
| Linked Server | Serialized via the state file | Credentials are not transferred |
| SSIS Packages | SSISDB export / deploy | Optional, only when SSIS is installed |
All paths and default options in a single JSON file - read automatically at startup. GUI input overrides the configuration at runtime.
// migration.config.json
{
"ExchangePath":
"\\\\FILESERVER\\SQLBackup\\state",
"BackupDirectory":
"\\\\FILESERVER\\SQLBackup",
"LogPath":
"C:\\Logs\\SQLMigration",
"MigrationMethod": "BackupRestore",
"WhatIf": false,
"MigrateLogins": true,
"MigrateAgentJobs":true,
"MigrateLinkedServer":true,
"MigrateSsis": false
}| Mode | Description |
|---|---|
Source | Phase 1 only - export on the source server |
Target | Phase 2 only - import on the target server |
Auto | State file present → Target, otherwise → Source |
# Interactive (role selection via dialog)
.\SQL-Migration.ps1
# Specify the role
.\SQL-Migration.ps1 -Role Source
.\SQL-Migration.ps1 -Role Target
# WhatIf simulation
.\SQL-Migration.ps1 -WhatIfEvery migration step is fully simulated - without a single change to the system.
The SQL connection to the source and target is tested and logged
All databases, logins, and jobs to be migrated are listed
Databases or logins already present on the target server are reported
A complete WhatIf log shows what would happen - without executing anything
-WhatIf first before every production migration.
Every step is logged with a timestamp, status, and error message.
| Requirement | Note |
|---|---|
| Windows Server | 2016 or newer - source and target server |
| SQL Server (source) | 2016 or later - Enterprise or Standard |
| SQL Server (target) | 2016 to 2025 - equal to or higher than the source |
| PowerShell 5.1 | Preinstalled on WS 2022 |
dbatools ≥ 2.0 | Installed automatically |
| Local administrator | On the source and target server |
| Network share reachable | For the exchange path (TwoPhase) |
Both servers reachable on the same network → fully automatic in one run
Exchange path on a mutually accessible share → state file + backups handed over automatically
State file + backup folder transferred between sites via SFTP, USB, or courier
The Migration Tool is part of a complete DBA toolchain - each tool covers a clearly defined area of responsibility.
Standardized SQL Server installation on the target server - before the migration begins.
Transfers databases, logins, jobs, and configuration from the old system to the new one.
Ongoing operations: backup, monitoring, health checks, AG sync on the target server.
✓ In scope
✗ Not in scope
Works even without a direct network connection - a JSON state file links the source and target server
Databases, logins, Agent jobs, linked servers, and SSIS packages in one controlled process
Full simulation with no system impact - conflicts and errors are detected in advance
Direct or TwoPhase - the tool decides automatically based on network reachability
Every step logged - timestamp, status, error message. Evidence for every change
Resources:
github.com/JankeUwe/SQLMigration www.powershelldba.de