sqlpackage

Status: Reviewed Stack: PowerShell → SQL / stored procs → Blazor Depends on: SQL-CLI-01 helpful for post-deploy verify; SQL-CLI-03 for Backup-SqlDatabase alternatives Goal: Use sqlpackage from the CLI for extract/publish/script/export/import of DACPAC/BACPAC in ways that fit PowerShell deploy pipelines feeding SQL → Blazor.

1. When to use sqlpackage vs SSMS Deploy vs Backup/Restore vs bcp vs manual scripts

Rick’s loop: AD/Windows/PowerShell → SQL stored procs → Blazor Server. sqlpackage is the DacFx CLI: schema-as-artifact (.dacpac) and portability (.bacpac). It is not a backup tool and not a bulk loader.

Rule of thumb

• Desired-state schema (procs, tables, views that Blazor calls) → sqlpackage Publish from a .dacpac.
• Review first → Script or DeployReport, then apply (or pipe Script through sqlcmd).
• Full instance recovery / PITO → Backup-SqlDatabase / native backup — not BACPAC.
• Bulk CSV / inventory loads → bcp into staging → stored proc → Blazor. sqlpackage is the wrong hammer.
• One-off DBA click-ops → SSMS is fine; pipelines and repeatable labs → sqlpackage CLI.

DACPAC vs BACPAC (memorize this)

Files are compressed, not encrypted. Treat both as sensitive.

2. Install on Windows (2026)

Official Learn (sql-server-ver17 / June 2026): latest build 170.4.83.3 (3 Jun 2026). Prefer standalone sqlpackage over the copy bundled with SSMS/VS — release cadence is faster and pipelines stay predictable.

Recommended: .NET global tool

Requires .NET SDK 8+ (Learn recommends the .NET 10 SqlPackage line).

dotnet --list-sdks
dotnet tool install -g microsoft.sqlpackage
# pin when you care:
# dotnet tool install -g microsoft.sqlpackage --version 170.4.83.3
dotnet tool update -g microsoft.sqlpackage
sqlpackage /Version

If NuGet can’t find the package: ensure nuget.org is a source (dotnet nuget list source). Newer SDK without matching runtime: add --allow-roll-forward.

winget (MSI path)

winget search SqlPackage
winget install --id Microsoft.SqlPackage --exact
# or pin:
# winget install --id Microsoft.SqlPackage --exact --version 170.4.83.3
# silent (Intune / SCCM style):
# winget install --id Microsoft.SqlPackage --exact --silent --accept-package-agreements --accept-source-agreements

MSI (.NET Framework) / ZIP (.NET 10 self-contained)

# Verify
sqlpackage /Version
# or full path after MSI:
& "C:\Program Files\Microsoft SQL Server\170\DAC\bin\SqlPackage.exe" /Version
Get-Command sqlpackage -ErrorAction SilentlyContinue | Format-List Source, Version

Pipeline tip: install at job start with dotnet tool install -g microsoft.sqlpackage rather than depending on image-bundled copies (ubuntu-latest no longer guarantees it).

3. Actions — what matters for Rick

Ops notes

• Publish needs db_owner on existing DB (or CREATE DATABASE for new).
• Export is not transactionally consistent unless you quiesce writes or export from a snapshot/copy. Learn guidance: best under ~200 GB; larger → Parquet / other strategies.
• Import targets a new/empty database story — not an incremental merge into a busy prod DB.
• Script/DeployReport first in any pipeline that touches shared UAT; then Publish with the same properties.

4. Auth & connection

SqlPackage uses Microsoft.Data.SqlClient auth. Prefer /SourceConnectionString + /TargetConnectionString (or short forms /scs / /tcs) so Encrypt/Trust settings live in one place.

TLS (do this explicitly)

# Windows Integrated — on-prem
$tcs = "Server=SQLPROD01;Database=AppDb;Integrated Security=True;Encrypt=True;TrustServerCertificate=False"

# SQL auth
$tcs = "Server=SQLPROD01;Database=AppDb;User ID=deploy;Password=$env:SQL_DEPLOY_PWD;Encrypt=True;TrustServerCertificate=False"

# Azure SQL + managed identity
$tcs = "Server=tcp:myapp.database.windows.net,1433;Initial Catalog=AppDb;Authentication=Active Directory Managed Identity;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"

Cross-link: for ad-hoc verify after publish, use sqlcmd or Invoke-Sqlcmd (SQL-CLI-03) — same Encrypt/Trust discipline.

5. Practical properties & flags cheat sheet (ops-focused)

Syntax: SqlPackage {parameters} {properties } {SQLCMD variables}

Core parameters

Publish properties you will actually touch

Extract data knobs (prefer BACPAC for full data moves): /p:ExtractAllTableData=True or repeated /p:TableData=schema.table.

Export size: /p:TempDirectoryForTableData=D:\sqlpkg-temp when TEMP is small.

6. PowerShell orchestration patterns

sqlpackage is an exe → use $LASTEXITCODE (same discipline as sqlcmd / bcp). Do not assume throw-on-failure.

function Invoke-SqlPackage {
    param([Parameter(Mandatory)][string[]]$ArgumentList)
    & sqlpackage @ArgumentList
    if ($LASTEXITCODE -ne 0) {
        throw "sqlpackage failed with exit code $LASTEXITCODE"
    }
}

CI-friendly publish

1. Install tool (dotnet tool install -g microsoft.sqlpackage).
2. Script or DeployReport → publish artifact for review.
3. Publish with identical /p: set + /df:sqlpackage.log + /q:True.
4. Verify with sqlcmd/Invoke-Sqlcmd (row counts, proc smoke tests).
5. Fail the job on non-zero exit.

Extract prod → publish lab

prod (Extract) → AppDb.dacpac → lab (Publish, CreateNewDatabase or empty DB)
                 └─ Script against lab for human review (optional gate)

Exclude users/logins/permissions when lab auth ≠ prod.

Script for review before apply

Invoke-SqlPackage @(
  '/Action:Script'
  "/SourceFile:$dacpac"
  "/TargetConnectionString:$labCs"
  "/DeployScriptPath:$outSql"   # /dsp — also accepts /OutputPath
  '/p:BlockOnPossibleDataLoss=True'
)
# Apply later:
# sqlcmd -S ... -d ... -b -i $outSql   # SQL-CLI-01

Publish can emit the same artifacts without applying-only mode via /dsp: (script) and /drp: (report) alongside /Action:Publish.

Exit codes: treat anything non-zero as failure. Capture /DiagnosticsFile on failure paths in CI.

7. Gotchas

8. Copy-paste recipes

1) Extract DACPAC (schema only) from prod

$src = "Server=SQLPROD01;Database=AppDb;Integrated Security=True;Encrypt=True;TrustServerCertificate=False"
sqlpackage /Action:Extract `
  /SourceConnectionString:$src `
  /TargetFile:"D:\dac\AppDb.dacpac" `
  /p:VerifyExtraction=True `
  /OverwriteFiles:True
if ($LASTEXITCODE -ne 0) { throw "Extract failed: $LASTEXITCODE" }

2) Publish DACPAC to lab (safe defaults)

$tgt = "Server=SQLLAB01;Database=AppDb;Integrated Security=True;Encrypt=True;TrustServerCertificate=True"
sqlpackage /Action:Publish `
  /SourceFile:"D:\dac\AppDb.dacpac" `
  /TargetConnectionString:$tgt `
  /p:BlockOnPossibleDataLoss=True `
  /p:DropObjectsNotInSource=False `
  "/p:ExcludeObjectTypes=Users;Logins;Permissions;RoleMembership;ExtendedProperties" `
  /DiagnosticsFile:"D:\dac\publish-lab.log" `
  /Quiet:True
if ($LASTEXITCODE -ne 0) { throw "Publish failed: $LASTEXITCODE" }

3) Generate deploy script for review (no apply)

sqlpackage /Action:Script `
  /SourceFile:"D:\dac\AppDb.dacpac" `
  /TargetConnectionString:$tgt `
  /DeployScriptPath:"D:\dac\AppDb-upgrade.sql" `
  /p:BlockOnPossibleDataLoss=True `
  "/p:ExcludeObjectTypes=Users;Logins;Permissions;RoleMembership"
if ($LASTEXITCODE -ne 0) { throw "Script failed: $LASTEXITCODE" }
# Review AppDb-upgrade.sql, then apply via SQL-CLI-01 sqlcmd -b -i ...

4) DeployReport (CI XML artifact)

sqlpackage /Action:DeployReport `
  /SourceFile:"D:\dac\AppDb.dacpac" `
  /TargetConnectionString:$tgt `
  /DeployReportPath:"D:\dac\AppDb-deploy.xml" `
  /p:BlockOnPossibleDataLoss=True
if ($LASTEXITCODE -ne 0) { throw "DeployReport failed: $LASTEXITCODE" }

5) Export / Import BACPAC (schema + data)

# Export (quiesce writes or use a copy)
sqlpackage /Action:Export `
  /SourceConnectionString:$src `
  /TargetFile:"D:\dac\AppDb.bacpac" `
  /OverwriteFiles:True `
  /p:TempDirectoryForTableData:"D:\sqlpkg-temp"
if ($LASTEXITCODE -ne 0) { throw "Export failed: $LASTEXITCODE" }

# Import into NEW database name on lab
$newCs = "Server=SQLLAB01;Database=AppDb_Copy;Integrated Security=True;Encrypt=True;TrustServerCertificate=True"
sqlpackage /Action:Import `
  /SourceFile:"D:\dac\AppDb.bacpac" `
  /TargetConnectionString:$newCs
if ($LASTEXITCODE -ne 0) { throw "Import failed: $LASTEXITCODE" }
# Reset contained-user passwords after import

6) PowerShell wrapper with $LASTEXITCODE + diagnostics

function Invoke-SqlPackage {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)][ValidateSet('Extract','Publish','Script','Export','Import','DeployReport','DriftReport')]
        [string]$Action,
        [Parameter(Mandatory)][hashtable]$Params,
        [string]$DiagnosticsFile
    )
    $args = @("/Action:$Action")
    foreach ($k in $Params.Keys) { $args += "/${k}:$($Params[$k])" }
    if ($DiagnosticsFile) { $args += "/DiagnosticsFile:$DiagnosticsFile" }

    Write-Verbose ($args -join ' ')
    & sqlpackage @args
    $code = $LASTEXITCODE
    if ($code -ne 0) {
        throw "sqlpackage $Action failed with exit code $code$(if ($DiagnosticsFile) { "; see $DiagnosticsFile" })"
    }
}

Invoke-SqlPackage -Action Publish -DiagnosticsFile 'D:\dac\pub.log' -Params @{
    SourceFile = 'D:\dac\AppDb.dacpac'
    TargetConnectionString = $tgt
    'p:BlockOnPossibleDataLoss' = 'True'
    Quiet = 'True'
}

7) Azure SQL + Entra (managed identity)

$tcs = "Server=tcp:myapp.database.windows.net,1433;Initial Catalog=AppDb;Authentication=Active Directory Managed Identity;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"
sqlpackage /Action:Publish `
  /SourceFile:"D:\dac\AppDb.dacpac" `
  /TargetConnectionString:$tcs `
  /p:BlockOnPossibleDataLoss=True `
  "/p:ExcludeObjectTypes=Users;Logins;Permissions;RoleMembership"
if ($LASTEXITCODE -ne 0) { throw "Azure publish failed: $LASTEXITCODE" }

8) Azure SQL + access token (service principal)

# Requires Az.Accounts; audience for Azure SQL
Connect-AzAccount -ServicePrincipal -Tenant $TenantId -Credential $SpCredential | Out-Null
$token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token
$tcs = "Server=tcp:myapp.database.windows.net,1433;Initial Catalog=AppDb;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"
sqlpackage /Action:Publish `
  /SourceFile:"D:\dac\AppDb.dacpac" `
  /TargetConnectionString:$tcs `
  /AccessToken:$token `
  /p:BlockOnPossibleDataLoss=True
if ($LASTEXITCODE -ne 0) { throw "Token publish failed: $LASTEXITCODE" }

Quick reference — Rick’s defaults

Docs (Learn, sql-server-ver17): SqlPackage overview · Download/install · Publish · Extract · Export · Release notes 170.4.83.3