Allow Copy-DbaDbTableData to copy between different databases on the same SQL instance #9723
Description
Currently Copy-DbaDbTableData throws an error if the source and destination SQL instances are the same — even when the source and destination databases are different. This restriction blocks a valid and useful use case: copying table data between databases on the same SQL Server instance.
Problem
If I run:
powershell
Copy-DbaDbTableData -SqlInstance "myserver.database.windows.net" -Database "SourceDB" -DestinationDatabase "TargetDB" -Table "MyTable"
Even though the databases are different, the command throws:
"Source and destination must be different."
This restriction makes sense only if the source and destination databases are the same, which could lead to overwriting data in-place. However, copying between different databases on the same instance is a very common and safe use case — especially for dev/test refreshes, environment snapshots, or internal migrations.
Proposed Change
Relax the instance comparison logic to allow copying when:
The source and destination database names differ, even if the SQL instance is the same
Only throw an error when:
The instance and database and table are all the same — i.e., truly an in-place operation
Benefits
Enables valid use cases without forcing users to fall back to Invoke-DbaQuery + Write-DbaDataTable which is lot slower
Aligns with typical admin workflows (e.g., prod → dev DB refreshes)
Reduces confusion, since this limitation isn’t clearly documented in the command's help
Workaround
We’re currently working around this by manually using:
$data = Invoke-DbaQuery ...
Write-DbaDataTable ...
But this is more verbose and less efficient than Copy-DbaDbTableData.