A faster copy command in dbatools
If you want to copy huge data from one SQL server to another, try using dbatools which has powershell module underneath.
In the demo here, I’ve compared 2 dbatools commands to move data from one SQL server to another:
Write-DbaDbTableData vs Copy-DbaDbTableData
Let’s see which one is faster:
Write-DbaDbTableData
Writes data to SQL Server table using SQL Bulk Copy function
Imports from csv to table or from table to table
This will buffer the contents of source table in memory of the machine running the commands.
By default, this operation will lock the destination table while running.
Demo:
Create a source table dbo.t1 with 1000 rows in SSMS and then run the copy from powershell
USE DBATEST
GO
DROP TABLE IF EXISTS dbo.t1
GO
TRUNCATE TABLE dbo.t2;
SELECT t1.k AS id
,'a_' + cast(t1.k AS VARCHAR) AS a
,'b_' + cast(t1.k / 2 AS VARCHAR) AS b
INTO t1
FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY a.object_id
) AS k
FROM sys.all_columns
,sys.all_columns a
) t1
WHERE t1.k < 1001 --Modify record count insertion as needed
--Verify count
SELECT COUNT(*) as t1_count FROM dbo.t1;
# Run the copy from source table t1 to target table t2 in Powershell
# Start timing
$sw = [System.Diagnostics.Stopwatch]::StartNew()
# Read data from source
$DataTable = Invoke-DbaQuery -SqlInstance haripriya\sql2022 -Database dbatest -Query "SELECT * FROM dbo.t1"
# Write data to target
Write-DbaDbTableData -SqlInstance haripriya\sql2019 -InputObject $DataTable -Database dbatest -Table dbo.t1
# Stop timing
$sw.Stop()
# Get elapsed time
Write-Output "Elapsed time: $($sw.Elapsed.TotalSeconds) seconds"
To copy 1000 rows from a table on one server to another server, it took 4.5 seconds.
Let’s try another method and see how long that takes.
Copy-DbaDbTableData
Copies data between SQL Server tables using SQL Bulk Copy.
Does NOT buffer the contents in memory of the machine running commands.
Faster than Write-DbaDbTableData, due to the above reason
Least resource-intensive way
By default, this operation will lock the destination table while running.
Specify –NoTableLock if needed, but makes the operation go slower
Default batchsize = 50000
# Run the copy from source table t1 to target table t2 in Powershell Copy-DbaDbTableData -SqlInstance haripriya\sql2022 -Destination haripriya\sql2019 -Database dbatest -Table dbo.t1
To copy 1000 rows from a table on one server to another server, it took 0.067 second using Copy-DbaDbTableData compared to 4.5 seconds using Write-DbaDbTableData.
To copy millions of records, Copy-DbaDbTableData does make a huge difference.
There is also autocreate table option if you want to the command to create a new destination table automatically.
Summary
Copy-DbaDbTableData copies data faster than Write-DbaDbTableData because in the former method, table contents are not buffered in the machine running the command.