Copy-DbaDbTableData can get wrong order when copying a field with varbinary (because it's doing autocreate(?))

[mbourgon]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

I ran into an odd, occasionally-reproducible data issue with copy-dbadbtabledata.

sometimes, when copying over a table (in my case 2 fields) with a varbinary, the data gets copied but not with the right ID field. This seems to happen with autocreatetable but I've had it work and not work.

Oddly, the data can match, but with the wrong ID field. I lucked into that - I forgot to order by capture_id, and came across that first image.

showing data same for different id

showing data different with same ID

Steps to Reproduce

--create hashed values from a table
SELECT  capture_id, HASHBYTES('SHA2_256',CONCAT(capture_id, '|',b, '|',REPLACE(CONVERT(NVARCHAR(MAX),c_xml),' />','/>'), '|',d, '|',e, '|',f)) AS field_hash
INTO hashed_original FROM complete_original 

this gives a table with this definition:

CREATE TABLE [dbo].[hashed_original]
(
[capture_id] BIGINT NOT NULL,
[field_hash] VARBINARY(8000)
)

Copying the table over sometimes results in the data getting copied, but the fields don't match (ID 1 had the data before, but in the target it's 961.

$SqlUserName = "username"
$SqlAdminPassword = "password"
$cred = [System.Management.Automation.PSCredential]::New(
    $SqlUserName,
    (ConvertTo-SecureString -String $SqlAdminPassword -AsPlainText -Force))

Copy-DbaDbTableData -SqlInstance "myserver.database.windows.net" -SqlCredential $cred -Destination "myserver2.database.windows.net" -DestinationSqlCredential $cred `
-Database dba -DestinationDatabase dbb -table hashed_original -destinationtable hashed_original_from_source -AutoCreateTable

Copy-DbaDbTableData -SqlInstance "myserver.database.windows.net" -SqlCredential $cred -Destination "myserver2.database.windows.net" -DestinationSqlCredential $cred `
-Database dba -DestinationDatabase dbb -table hashed_original -destinationtable hashed_original_from_source_table_already_created 

When I create the table by hand on the target, it succeeded and data matched. And adding a clustered index on the source may fix it as well, but I've been staring at this too long now so I'm not sure.

Worth noting, maybe: copying data from Azure SQL Database to Azure Managed Instance.

Please confirm that you are running the most recent version of dbatools

2.1.17

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.17763.6893
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.6893
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

source: Microsoft SQL Azure (RTM) - 12.0.2000.8 Jan 29 2025 16:27:12 Copyright (C) 2022 Microsoft Corporation
target: Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 29 2024 04:10:31 Copyright (C) 2022 Microsoft Corporation

.NET Framework Version

.NET Framework 4.7.4126.0