Optimizing SQL Server Backup Transfers with Robocopy

Splitting a 30 GB backup into thirty pieces saved me nothing on transfer time. At 1 MB/s it’s eight hours either way, and I spent a morning finding that out.

This morning I stared at a 30 GB SQL Server backup and a VPN that managed about 1 MB/s and dropped every 45 to 60 minutes. I thought, “If I split this into thirty 1 GB pieces and copy them in parallel, maybe I can outrun the disconnects.” Later that morning I ran a quick PowerShell script to slice the .bak into 30 parts, which took just a couple of minutes.

I tested transfer times next. At around 1 MB/s each slice copied in roughly 15 to 17 minutes. In a typical VPN window I could finish two slices and start another before the link dropped. When the VPN hiccuped I lost at most one gigabyte instead of dozens, but the overall transfer time stayed the same: moving 30 GB at 1 MB/s still takes about eight hours, plus those few extra minutes for splitting and retries. I had smaller failure domains, but no net time savings.

By midday I was frustrated and decided to tune Robocopy’s parallelism. I wrote a simple harness to copy the first handful of slices with various /MT thread counts and measured each run:

$sourceDir   = '\\server\share'
$destDir     = 'D:\migrations'
$filePattern = 'large.bak.part00[1-5]'
$mtOptions   = 2,4,8,16,32
$results     = @{}

foreach ($mt in $mtOptions) {
    $start = Get-Date
    robocopy $sourceDir $destDir $filePattern /Z /MT:$mt /R:1 /W:1 | Out-Null
    $results[$mt] = (Get-Date) - $start
}

$bestMt = ($results.GetEnumerator() |
           Sort-Object Value.TotalSeconds |
           Select-Object -First 1).Key

Write-Host "Best MT value is" $bestMt

That test took on the order of half an hour, and /MT:8 came out on top. Two things to say about that number before you copy it: it’s robocopy’s default anyway, and I don’t trust the test that produced it. Both of those are at the end of the post. Treat it as the value I happened to run with rather than a tuned result.

With that said, I launched the full copy:

robocopy \\server\share D:\migrations large.bak.part* /Z /MT:8 /R:3 /W:5

Next, to avoid a single long list of files, I ran three Robocopy sessions in parallel, each handling a batch of slices:

$parts  = Get-ChildItem '\\server\share\large.bak.part*' | Sort-Object Name
$groups = @(
    $parts[0..9],
    $parts[10..19],
    $parts[20..29]
)

foreach ($batch in $groups) {
    $fileArgs = $batch.Name -join ' '
    Start-Process robocopy -ArgumentList '\\server\share', 'D:\migrations', $fileArgs, '/Z', '/MT:8', '/R:3', '/W:5' -NoNewWindow
}

I let those jobs run unattended into the afternoon and evening, and by day’s end all 30 parts had arrived successfully.

Finally, to restore the original .bak file once all parts have transferred, you can unchunk them with a simple PowerShell loop in your migration folder:

$parts  = Get-ChildItem 'D:\migrations\large.bak.part*' | Sort-Object Name
$out    = 'D:\migrations\restored-large.bak'
if (Test-Path $out) { Remove-Item $out }
foreach ($part in $parts) {
    Get-Content $part -Encoding Byte -ReadCount 0 | Add-Content $out -Encoding Byte
}

Two warnings on that one. It only works in Windows PowerShell 5.1, since -Encoding Byte was removed in PowerShell 6 and later in favour of -AsByteStream, so on PowerShell 7 you want Get-Content $part -AsByteStream -ReadCount 0 | Add-Content $out -AsByteStream. The Get-Content documentation has -AsByteStream as “introduced in Windows PowerShell 6.0,” and Byte no longer appears in the list of accepted -Encoding values.

The -ReadCount 0 matters as much as the encoding does. The default is 1, which the docs say “reads one byte in each read operation and converts each byte into a separate object,” so on a 30 GB file you’d be creating 30 billion objects to copy one file. It’s slow even with the read count set, because the bytes are still going through the pipeline, and on 30 GB it was slow enough that I stopped using it.

The CMD version is the one I actually run:

copy /b large.bak.part* restored-large.bak

It does the same job in a fraction of the time, and the /b is what makes it binary rather than stopping at the first end-of-file character it finds. Either way, check the reassembled file size matches the original before you point RESTORE DATABASE at it, and RESTORE VERIFYONLY before you trust it.

Today I learned that chopping a large file into pieces rarely speeds up the total transfer. The bandwidth is the bandwidth. What the splitting bought me was a smaller failure domain, so a dropped VPN cost me one gigabyte instead of everything, and that turned out to be worth the morning even though the clock time was identical.

The /MT tuning is the part I’d question if I did it again. Eight threads won my test, but the test ran over a link that was dropping every 45 minutes, so I’m not sure I measured thread count so much as which run happened to avoid a disconnect. Half an hour of testing to pick a number I can’t defend.

It got worse when I went to check the documentation while writing this up. The robocopy reference says of /mt:<n> that “n must be an integer between 1 and 128. The default value for n is 8,” so eight is what I’d have got by leaving the flag off entirely. Half a morning of testing to arrive at the value robocopy was already going to use, and then I wrote it into the command where it looks like I chose it.

I should have read the rest of that page at the time too. /r defaults to “1,000,000 (one million retries)” and /w waits 30 seconds between them, so on a link that drops every 45 minutes the defaults would have had it sitting there retrying for the better part of a year, and /R:3 /W:5 was worth setting even though /MT:8 wasn’t. There’s also a /compress flag that “requests network compression during file transfer, if applicable,” and since the constraint here was bandwidth rather than threads, that’s the one I’d try first if I did this again.