sqmDataTransfer is a dbatools-based PowerShell module that copies chosen tables from one SQL Server instance to another: it can script the table's DDL on the target if it doesn't exist yet, disables foreign keys and non-clustered indexes for the bulk copy, re-enables them afterward — guaranteed, even on failure — and reconciles row counts between source and target. Every run writes a full log and a self-contained HTML report.
Moving table data between instances by hand means separately handling the target DDL, disabling constraints so the bulk load doesn't choke on FK checks, remembering to re-enable everything afterward even if step 3 fails, and then manually counting rows to prove nothing was lost. sqmDataTransfer runs that whole sequence as one call.
| Without it | With sqmDataTransfer |
|---|---|
| Target table DDL scripted and re-typed by hand, dependencies missed | Table, dependent types, sequences, and FK-referenced tables scripted automatically |
| Bulk copy fails or crawls because FKs/indexes are still active | Non-clustered indexes and foreign keys disabled for the copy, every time |
| A failed step leaves constraints disabled with no one noticing | Re-enable runs in a finally block — guaranteed, even after a failure |
| "I think it copied everything" — no proof | Explicit source-vs-target row-count comparison in the report |
| Partitioned or CLR-typed tables fail the script with an opaque error | Partitioning stripped with a warning; CLR types flagged for manual assembly deploy |
Invoke-sqmTableTransfer is the main entry point — it orchestrates the full sequence below for every table you pass in.
Copy-DbaDbTableData.finally block, so this step runs even if an earlier one fails.Every step is logged to %LogPath%\sqmDataTransfer_yyyyMMdd_<FunctionName>.log and returned as a structured result object (Table, Step, Status, Message, Timestamp). A self-contained HTML report is always written at the end of the run — every processed table, anything missing or failed, and the row-count comparison — and opens automatically in the browser unless -NoOpen is passed.
With -ScriptMetadata, the module doesn't just script the table — it walks the real dependency graph so the CREATE actually succeeds on the target.
| Schema | The destination schema is created automatically if it doesn't exist |
| Dependencies | User-defined types, sequences, and FK-referenced tables the table depends on are scripted automatically too (SMO WithDependencies) |
| Partitioned tables | Still fully scripted and transferred — but the physical partitioning itself (partition function/scheme, per-partition filegroups) is stripped, since there's no way to know whether an equivalent scheme exists on the target. Lands as a normal table on PRIMARY instead of failing with a missing-partition-scheme error. Reported as a warning. |
| CLR user-defined types | Scripted, but flagged as a warning — the assembly itself has to be deployed on the target manually |
| Version targeting | The destination's actual SQL Server version is auto-detected and passed to SMO as TargetServerVersion, so scripting from a newer source (e.g. 2022) down to an older target (e.g. 2019) produces syntax the target can run |
| Function | Purpose |
|---|---|
| Invoke-sqmTableTransfer | Main entry point — orchestrates the full five-step sequence |
| Export-sqmTableSchema | Scripts table DDL from a source instance (SMO Scripter) |
| New-sqmTableFromScript | Executes scripted DDL batches against a target instance |
| Copy-sqmTableSchema | Convenience wrapper: export + create in one call |
| Disable-sqmTableConstraints | Disables FKs / non-clustered indexes on a table |
| Enable-sqmTableConstraints | Re-enables (rebuilds) previously disabled FKs / indexes — re-detects state, nothing to pass in |
| Copy-sqmTableData | Bulk-copies table data (wraps Copy-DbaDbTableData) |
| Compare-sqmTableRowCount | Compares row counts source vs. target |
| Export-sqmTransferReport | Builds the HTML summary/row-count report |
| Show-sqmTableTransferGui | WinForms GUI for the whole workflow |
| Get-sqmTransferConfig / Set-sqmTransferConfig | Module configuration (log path, batch size, etc.) |
Clustered indexes are never disabled — doing so blocks table access entirely — only non-clustered indexes are touched. Foreign keys are disabled/enabled individually by name, leaving CHECK/DEFAULT constraints untouched.
# Installation - auto-detects AllUsers (if Admin) or CurrentUser Install.cmd # Transfer two tables, scripting metadata and truncating the target first Invoke-sqmTableTransfer -Source SQL01 -SourceDatabase Sales -Destination SQL02 -DestinationDatabase Sales -Table 'dbo.Orders', 'dbo.Customers' -ScriptMetadata -Truncate -Confirm:$false # Or via GUI Show-sqmTableTransferGui
Configuration is persisted separately from sqmSQLTool (%APPDATA%\SQLDataTransfer\config.json) so both modules can be imported side by side without interfering, but LogPath/OutputPath default to the same location sqmSQLTool uses.
Built on dbatools — if it's already on the box, sqmDataTransfer is one Import-Module away.
PowerShell source, MIT-licensed. Clone or run Install.cmd to install into your module path.
The core engine this suite is built on — 130 commands for administration, health checks, and compliance reporting.
Get in touch about the module, a feature, or the wider powershelldba.de toolchain.