LLI Wiki — My Digital Knowledge Base

Exporting thousands of Azure SQL databases to files with bcp and Azure Batch

One Batch task per database, bcp options (native vs character, terminators, UTF-8, packet size), Entra authentication, compression, blob upload, row-count checks, targeted retries and cleanup.

Exporting one database to flat files is easy. Exporting thousands of them every night, reliably, and knowing which ones failed is an engineering problem. My answer: one Azure Batch task per database, bcp for the data, compression, upload to blob storage, a row-count check, and a retry that only touches the failed tasks. (The Batch architecture, packaging and monitoring are covered in my other Azure Batch articles; here I focus on the export itself.)

The overall flow

flowchart TD
    A["Scheduler (pipeline)"] --> B["Target list (server, database)"]
    B --> C["Batch job: one task per database"]
    C --> D["bcp out: one file per table"]
    D --> E["Row count check"]
    E --> F["Compress (zip or gzip)"]
    F --> G["Upload to blob storage"]
    G --> H["Done marker blob"]
    C --> I["Task result (success / failure)"]
    I --> J["Retry only failed tasks"]
    J --> C
    K["Lifecycle rule"] --> G

Why one task per database

A task is the natural unit of failure: one database that is slow, locked or unreachable must not block the others. Batch gives me scheduling, a task state, stdout/stderr per task and a per-task retry for free. The pool size sets the parallelism; I keep it modest so that the source servers (and their elastic pools) are never overloaded. Tasks must be idempotent: rerunning a task overwrites its own output and nothing else.

Choosing the bcp options

Option Use
-n (native) Fastest and exact types, but only readable by bcp/SQL Server. Good for SQL-to-SQL moves.
-c (character) Text files any tool can read. Use it when the consumer is not SQL Server.
-w Unicode (UTF-16) character format. Heavier files.
-C 65001 UTF-8 code page for character mode (recent bcp versions). Without it, the client code page is used and accented characters can silently turn into ?.
-t / -r Field and row terminators. Pick a field terminator that cannot appear in the data (a pipe is often not safe enough: test it) and keep \n as row terminator.
-a Network packet size. The default is 4096 bytes; a larger value (for example 16384) usually speeds up big tables. Measure before and after.
-o Write the bcp messages to a file: it contains the "rows copied" line used for the check.
queryout Exports a query (views, computed columns, filtered rows) instead of a whole table.

The trade-off to remember: character mode is portable but has no quoting or escaping. If your text can contain the terminators or line breaks, the file is corrupt without any error. Choose terminators after looking at the data, and verify with row counts.

Authentication

For Azure SQL, I want Entra identities and no password. Recent versions of bcp accept -G for Entra authentication; which flavour works (interactive, password, managed identity) depends on the bcp and ODBC driver versions installed on the Batch image, so test it on the actual pool image and read the documentation of the version you ship. If -G does not fit your situation, the fallback is to read the data yourself with a SQL client library using an access token and write the file from code. It is slower to write, but fully under your control. In both cases, the identity needs only read permissions on the source.

The task script

param([string]$Server, [string]$Database, [string]$Run, [string]$Account)

$work = Join-Path $env:AZ_BATCH_TASK_WORKING_DIR $Database
New-Item -ItemType Directory -Path $work -Force | Out-Null
$tok  = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net/').Token
if ($tok -is [securestring]) { $tok = $tok | ConvertFrom-SecureString -AsPlainText }

$tables = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -AccessToken $tok `
    -Query "SELECT s.name AS sch, t.name AS tbl FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id"

$report = foreach ($t in $tables) {
    $name = "$($t.sch).$($t.tbl)"
    $file = Join-Path $work "$name.txt"
    $log  = Join-Path $work "$name.bcp.log"
    & bcp "[$($t.sch)].[$($t.tbl)]" out $file -S $Server -d $Database -G `
        -c -C 65001 -t '|' -r '\n' -a 16384 -o $log | Out-Null
    $copied = [long](Select-String -Path $log -Pattern '^(\d+) rows copied' | ForEach-Object { $_.Matches[0].Groups[1].Value })
    $source = (Invoke-Sqlcmd -ServerInstance $Server -Database $Database -AccessToken $tok `
        -Query "SELECT COUNT_BIG(*) AS n FROM [$($t.sch)].[$($t.tbl)]").n
    [pscustomobject]@{ Table = $name; Exported = $copied; Source = $source; Ok = ($copied -eq $source -and $LASTEXITCODE -eq 0) }
}
if ($report.Where({ -not $_.Ok })) { $report | Format-Table | Out-String | Write-Error; exit 1 }

The -G call here is schematic: add the credential arguments your bcp version needs.

Row counts, compression, upload

The check compares what bcp reports with a COUNT_BIG(*) run on the same source. On a live database the two can differ by a few rows; run the export against a read-only replica or a quiet window, or accept a tolerance and log it. A failed check makes the task fail, so it shows up in Batch like any other failure.

Then compress the folder (Compress-Archive or gzip per file) and upload with the Entra identity of the pool, never a storage key:

$zip = Join-Path $env:AZ_BATCH_TASK_WORKING_DIR "$Database.zip"
Compress-Archive -Path "$work\*" -DestinationPath $zip -Force
$ctx = New-AzStorageContext -StorageAccountName $Account -UseConnectedAccount
Set-AzStorageBlobContent -File $zip -Container 'exports' -Blob "$Run/$Database.zip" -Context $ctx -Force | Out-Null
'ok' | Set-Content "$work\done.txt"
Set-AzStorageBlobContent -File "$work\done.txt" -Container 'exports' -Blob "$Run/$Database.done" -Context $ctx -Force | Out-Null

The small .done blob is written last: a consumer that sees it knows the archive is complete. Delete the local working folder at the end, the Batch node disk is small.

Retrying only the failed tasks

A Batch task records its result. After the run I list the tasks whose execution result is a failure and submit them again under a new id (for example with a -r1 suffix); because tasks are idempotent and the .done marker tells what is finished, the retry costs only the failed databases. Keep a cap on automatic retries and send the remaining failures in a report instead of looping forever.

Cleaning old exports

Do not write a cleanup script if the platform already does it: a blob lifecycle management rule on the storage account deletes blobs older than N days under the export prefix. Organise blobs by run date (<run>/<database>.zip) so the rule is simple and a run can be deleted as a whole.

Lessons learned

Mastodon LLIMastodon SocialFosstodon