Move table data between instances without losing the constraints, or the proof.

"Just BCP it over" skips the parts that actually go wrong.

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.

Five steps, run in order, logged individually.

Invoke-sqmTableTransfer is the main entry point, it orchestrates the full sequence below for every table you pass in.

1. Script metadata (optional)Scripts the table DDL, columns, PK, indexes, foreign keys, defaults, checks, from the source and creates it on the target if it doesn't already exist. Existing target tables are never dropped or recreated.
2. DisableDisables foreign keys, non-clustered indexes, and DML triggers on the target table so the bulk load isn't fighting constraint checks, index maintenance, or a trigger firing per row.
3. Copy dataBulk-copies rows from source to target via dbatools' Copy-DbaDbTableData.
4. Compare row countsCounts rows on both sides and records the comparison in the report.
5. Re-enableRebuilds the disabled foreign keys and indexes, inside a 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.

Resuming an interrupted run

A run over hundreds of tables can get killed halfway, a crashed session, a rebooted box overnight, and the HTML report is only written once the run finishes, so there's normally no record of what actually made it. -SkipCompleted runs Compare-sqmTableRowCount against every requested table up front and skips any where source and target row counts already match, so re-submitting the full table list only touches what's still outstanding. No separate bookkeeping of "which tables finished", completeness is re-derived from the data itself, every time. Available on Invoke-sqmTableTransfer and as a checkbox in the GUI's options panel.

The part that usually breaks a hand-written CREATE TABLE.

With -ScriptMetadata, the module doesn't just script the table, it walks the real dependency graph so the CREATE actually succeeds on the target.

Hundreds of millions of rows, no primary key, resumable anyway.

Invoke-sqmTableTransfer is all-or-nothing: if a copy of a 300-million-row table is interrupted an hour in, there's normally no reliable way to know which rows already made it across without a key to anti-join on, and no way to resume without re-copying everything. Invoke-sqmChunkedTableTransfer splits the table by a discriminating column (typically a reporting or snapshot date) and transfers it one distinct value at a time instead.

A plain Invoke-sqmTableTransfer call on a table above a configurable row-count threshold (default 10 million, Set-sqmTransferConfig -LargeTableRowThreshold) warns instead of silently doing the all-or-nothing copy, with a ready-to-paste Invoke-sqmChunkedTableTransfer command, including a suggested -ChunkColumn picked from the table's date-typed columns by naming convention. That warning only fires once the destination already holds a meaningful share of the source's rows (default 30%, Set-sqmTransferConfig -ChunkAdviceMinExistingPercent), since chunking's advantage is resumability, on a fresh/empty destination there's nothing to resume, and the plain copy is simply faster. The GUI runs the same check before a transfer starts, since the GUI itself has no chunked path.

Fifteen functions, two orchestrators, thirteen building blocks.

Clustered indexes are never disabled, doing so blocks table access entirely, only non-clustered indexes are touched. Foreign keys and triggers are disabled/enabled individually by name, leaving CHECK/DEFAULT constraints untouched.

Install, then one call, or the GUI.

# 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

# Resume an interrupted run over the same table list - already-matching tables are skipped
Invoke-sqmTableTransfer -Source SQL01 -SourceDatabase Sales -Destination SQL02 -DestinationDatabase Sales -Table $allTables -ScriptMetadata -SkipCompleted -Confirm:$false

# A 300M-row table with no primary key, split by a reporting-date column
Invoke-sqmChunkedTableTransfer -Source SQL01 -SourceDatabase Sales -Destination SQL02 -DestinationDatabase Sales -Table dbo.Ergebnis_agg -ChunkColumn Dat_ReportingDate -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.

Move your first table today.

Built on dbatools, if it's already on the box, sqmDataTransfer is one Import-Module away.