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:

1. 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.

Additional Reading

• https://docs.dbatools.io/Copy-DbaDbTableData.html

• https://docs.dbatools.io/Write-DbaDbTableData.html