SqlDataPack

https://github.com/zachtbeer/sqldatapack
Admin
SqlDataPack is a .NET library and CLI tool that exports a slice of a SQL Server database into a...
Tokens:51,867
Snippets:455
Trust Score:4.4
License:MIT
Update:1 month ago
Show doc for...
Tokens:
Raw
### Install SqlDataPack via dotnet tool

Source: https://github.com/zachtbeer/sqldatapack/blob/main/README.md

Install the SqlDataPack CLI tool globally using the .NET SDK.

```bash
dotnet tool install -g SqlDataPack.Cli    # if you already have the SDK
```

--------------------------------

### Install SqlDataPack via winget

Source: https://github.com/zachtbeer/sqldatapack/blob/main/README.md

Install the SqlDataPack CLI tool using the Windows Package Manager.

```powershell
winget install zachtbeer.SqlDataPack
```

--------------------------------

### Install SqlDataPack library

Source: https://github.com/zachtbeer/sqldatapack/blob/main/README.md

Add the SqlDataPack NuGet package to your .NET project.

```bash
dotnet add package SqlDataPack
```

--------------------------------

### Install for all users with winget

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/cli.md

Installs sqldatapack for every user on a shared server using the --scope machine flag.

```powershell
winget install zachtbeer.SqlDataPack --scope machine
```

--------------------------------

### Manage central package versions

Source: https://github.com/zachtbeer/sqldatapack/blob/main/CONTRIBUTING.md

Example of defining a package version in Directory.Packages.props and referencing it without a version in a project file.

```xml
<!-- Directory.Packages.props -->
<PackageVersion Include="Some.Package" Version="1.2.3" />

<!-- YourProject.csproj -->
<PackageReference Include="Some.Package" />
```

--------------------------------

### Push more options into the deploy (deploy users + database options)

Source: https://github.com/zachtbeer/sqldatapack/blob/main/samples/SqlDataPack.DeployRepro/README.md

Deploys with additional options to get closer to prod configs. Success signal: Same as defaults. Failure signal: Script will show more ALTER DATABASE / CREATE USER lines — grep for any new error numbers.

```bash
dotnet run --project samples/SqlDataPack.DeployRepro -- --deploy-users true --deploy-database-options true --dump-script
```

--------------------------------

### Install SqlDataPack with dotnet add package

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/getting-started.md

Adds the SqlDataPack NuGet package to the project. The --prerelease flag is required to get the latest pre-release version.

```bash
dotnet add package SqlDataPack --prerelease
```

--------------------------------

### Validate and test winget manifest locally

Source: https://github.com/zachtbeer/sqldatapack/blob/main/build/winget/README.md

Run these PowerShell commands to validate the manifest folder, enable local manifest files, and install the package from the local manifest. The folder must contain only the three YAML files; pointing at build/winget/ fails because the README is not YAML.

```powershell
winget validate --manifest <folder>
winget settings --enable LocalManifestFiles
winget install --manifest <folder>
```

--------------------------------

### Set environment variables and run the workflow sample

Source: https://github.com/zachtbeer/sqldatapack/blob/main/samples/SqlDataPack.WorkflowSample/README.md

Use these bash commands to configure the source and target SQL Server connection strings and the package file path, then run the sample project. The target tables must already exist and be empty before import unless dacpac capture/deployment options are added.

```bash
export ZSB_SOURCE_SQLSERVER="Server=localhost;Database=SourceDb;User Id=sa;Password=...;TrustServerCertificate=True"
export ZSB_TARGET_SQLSERVER="Server=localhost;Database=TargetDb;User Id=sa;Password=...;TrustServerCertificate=True"
export ZSB_PACKAGE_PATH="workflow-sample.sqlite"
dotnet run --project samples/SqlDataPack.WorkflowSample/SqlDataPack.WorkflowSample.csproj
```

--------------------------------

### Run SqlDataPack.Sample with environment variables

Source: https://github.com/zachtbeer/sqldatapack/blob/main/samples/SqlDataPack.Sample/README.md

Sets the required connection strings and package path via environment variables before executing the sample project.

```bash
export ZSB_SOURCE_SQLSERVER="Server=localhost;Database=SourceDb;User Id=sa;Password=...;TrustServerCertificate=True"
export ZSB_TARGET_SQLSERVER="Server=localhost;Database=TargetDb;User Id=sa;Password=...;TrustServerCertificate=True"
export ZSB_PACKAGE_PATH="package.sqlite"
dotnet run --project samples/SqlDataPack.Sample/SqlDataPack.Sample.csproj
```

--------------------------------

### Restore and build the solution

Source: https://github.com/zachtbeer/sqldatapack/blob/main/CONTRIBUTING.md

Commands to restore dependencies and build the SqlDataPack solution.

```bash
dotnet restore SqlDataPack.slnx
dotnet build SqlDataPack.slnx
```

--------------------------------

### SQL71501 unresolved reference error example

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/troubleshooting.md

Example error message when DacFx model verification is enabled (DacpacCaptureOptions.VerifyExtraction = true). The static model validator is stricter than SQL Server's actual binder and can flag benign references.

```text
SQL71501: ... Procedure [dbo].[SomeProcedure] contains an unresolved reference to an object.
Either the object does not exist or the reference is ambiguous because it could refer to any of
the following objects: [dbo].[TableA].[SharedKey], [dbo].[TableB].[SharedKey] ...
```

--------------------------------

### ExcludeColumns option example

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/supported-types.md

Excludes a geography column from export to avoid preflight failure for unsupported types.

```csharp
options.ExcludeColumns = ["dbo.Stores.Location"]; // a geography column

```

--------------------------------

### Resolve data table name from manifest

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/package-format.md

Use `SqlDataPackReader.ReadManifestAsync` to get the manifest and find the SQLite table name for a specific source table. Avoid hardcoding data table names because the prefix is configurable.

```csharp
var manifest = await new SqlDataPackReader().ReadManifestAsync("billing-repro.sqlite");
var customers = manifest.Tables.Single(t => t.FullName == "dbo.Customers").SqliteTable;
```

--------------------------------

### Self-test: CLI parses (--help)

Source: https://github.com/zachtbeer/sqldatapack/blob/main/samples/SqlDataPack.DeployRepro/README.md

Run this first to verify the CLI parses correctly. Expect 'Usage: dotnet run' in stdout and exit code 64.

```bash
dotnet run --project samples/SqlDataPack.DeployRepro -- --help
```

--------------------------------

### Read manifest with SqlDataPackReader.ReadManifestAsync

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/getting-started.md

Reads the manifest from the SQLite file to get row counts, source types, and foreign-key import order without importing anything. The commented output shows the expected result.

```csharp
var manifest = await new SqlDataPackReader().ReadManifestAsync("billing-repro.sqlite");
foreach (var table in manifest.Tables)
    Console.WriteLine($"{table.FullName}: {table.ExportedRowCount} rows");
// dbo.Customers: 1,204 rows
// dbo.Invoices: 18,755 rows
// dbo.Orders: 22,109 rows
```

--------------------------------

### Try deploy against SQL Server 2022 image

Source: https://github.com/zachtbeer/sqldatapack/blob/main/samples/SqlDataPack.DeployRepro/README.md

Deploys against a SQL Server 2022 image instead of 2025. Success signal: 'Deploy succeeded in'. Failure signal: Exit 1 + check '=== [server] ===' for ProductVersion and adjust expectations.

```bash
dotnet run --project samples/SqlDataPack.DeployRepro -- --image mcr.microsoft.com/mssql/server:2022-latest
```

--------------------------------

### Deploy bundled fixture with defaults

Source: https://github.com/zachtbeer/sqldatapack/blob/main/samples/SqlDataPack.DeployRepro/README.md

Deploys the bundled fixture with defaults, mirroring the integration-test happy path. Success signal: 'Deploy succeeded in' followed by 'User tables on target:' with a positive number. Failure signal: Exit 1 + '=== [FAILURE after' banner.

```bash
dotnet run --project samples/SqlDataPack.DeployRepro
```

--------------------------------

### Inspect SQLite package via CLI

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/hand-it-to-an-agent.md

Demonstrates using the sqlite3 command-line tool to list tables and query data within the generated package.

```text
$ sqlite3 agent-handoff.sqlite ".tables"
dbo__customers        zsdp_columns          zsdp_import_plan      zsdp_tables
dbo__invoices         zsdp_exclusions       zsdp_schema_packages  zsdp_warnings
dbo__orders           zsdp_export_runs      zsdp_table_stats

$ sqlite3 agent-handoff.sqlite "SELECT CustomerId, Name FROM dbo__customers LIMIT 3;"
1|Northwind Traders
2|Contoso Ltd
3|Fabrikam, Inc.
```

--------------------------------

### Release versioning and tagging

Source: https://github.com/zachtbeer/sqldatapack/blob/main/CONTRIBUTING.md

Use VersionGuard to check for missing requirements before tagging and pushing a new release version.

```bash
dotnet run build/VersionGuard.cs -- 1.0.0   # tells you what is missing
git tag -a v1.0.0 -m "v1.0.0"
git push origin v1.0.0
```

--------------------------------

### Run SqlDataPack Benchmarks with dotnet run

Source: https://github.com/zachtbeer/sqldatapack/blob/main/AGENTS.md

Run the BenchmarkDotNet suite in tests/SqlDataPack.Benchmarks without Docker. Covers the export write loop in SqlitePackageWriter and per-cell conversion in ValueConverter. Results can be noisy on loaded machines; re-run and be suspicious of regressions with unchanged allocations.

```bash
dotnet run -c Release --project tests/SqlDataPack.Benchmarks -- --filter '*'
```

--------------------------------

### Run all benchmarks with dotnet run

Source: https://github.com/zachtbeer/sqldatapack/blob/main/tests/SqlDataPack.Benchmarks/README.md

Runs every benchmark in the SqlDataPack.Benchmarks project in Release configuration. Release is mandatory because BenchmarkDotNet refuses to run a Debug build.

```bash
dotnet run -c Release --project tests/SqlDataPack.Benchmarks -- --filter '*'

```

--------------------------------

### Run all tests in solution

Source: https://github.com/zachtbeer/sqldatapack/blob/main/AGENTS.md

Runs all tests in the SqlDataPack solution. This is the comprehensive test command.

```bash
dotnet test SqlDataPack.slnx
```

--------------------------------

### Self-test: Full pipeline works (--dump-script --no-deploy)

Source: https://github.com/zachtbeer/sqldatapack/blob/main/samples/SqlDataPack.DeployRepro/README.md

Run this second to verify the full pipeline works without deploying. Expect '=== [done] ===' in stdout and exit code 0. If it fails on '=== [fixture] ===', the fixture file isn't where the app looks; pass --db <path> explicitly.

```bash
dotnet run --project samples/SqlDataPack.DeployRepro -- --dump-script --no-deploy
```

--------------------------------

### Compact and Verify Database

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/editing-the-package.md

Run the VACUUM command to rewrite the database and remove freed page content, then use grep to verify that sensitive data is no longer present.

```bash
sqlite3 customer-snapshot.sqlite 'VACUUM;'
grep -a 'a.real.address@customer.com' customer-snapshot.sqlite && echo "STILL PRESENT" || echo "clean"
```

--------------------------------

### Release commands: dotnet run VersionGuard.cs, git tag, git push

Source: https://github.com/zachtbeer/sqldatapack/blob/main/docs/RELEASE.md

Commands to cut a release. Run the first command and paste the last two lines it prints. The tag must be on HEAD for the CI guard to pass.

```bash
dotnet run build/VersionGuard.cs -- 1.1.0
git tag -a v1.1.0 -m "v1.1.0"
git push origin v1.1.0
```

--------------------------------

### Export and import a database slice with schema

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/slice-with-schema.md

Demonstrates configuring ExportOptions to capture schema via Dacpac and performing an export followed by an import into a new environment.

```csharp
using SqlDataPack;
using SqlDataPack.Models;

var exportOptions = ExportOptions.Default;
exportOptions.SchemaCaptureMode = SchemaCaptureMode.Dacpac;
exportOptions.DacpacCaptureOptions.SchemaScope = DacpacSchemaScope.SelectedExportTables;
exportOptions.TableSelection = ExportTableSelectionMode.Only;
exportOptions.Tables =
[
    "dbo.Customers",
    "dbo.Orders",
    "dbo.Invoices"
];

await SqlData.ExportAsync(
    sourceSqlServerConnectionString,
    "new-env-slice.sqlite",
    exportOptions);

var importOptions = ImportOptions.Default;
importOptions.SchemaDeploymentMode = SchemaDeploymentMode.DeployDacpac;

await SqlData.ImportAsync(
    "new-env-slice.sqlite",
    targetSqlServerConnectionString,
    importOptions);
```

--------------------------------

### Verify binary provenance

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/cli.md

Use the GitHub CLI to verify the build provenance attestation for the downloaded executable.

```bash
gh attestation verify sqldatapack-win-x64.exe --repo zachtbeer/sqldatapack
```

--------------------------------

### Preview release commands: dotnet run VersionGuard.cs, git tag, git push

Source: https://github.com/zachtbeer/sqldatapack/blob/main/docs/RELEASE.md

Commands to cut a preview release with a prerelease version. The CI workflow marks the GitHub release as a prerelease and skips the winget submission.

```bash
dotnet run build/VersionGuard.cs -- 1.1.0-preview.1
git tag -a v1.1.0-preview.1 -m "v1.1.0-preview.1"
git push origin v1.1.0-preview.1
```

--------------------------------

### Run ValueConvert benchmarks with dotnet run

Source: https://github.com/zachtbeer/sqldatapack/blob/main/tests/SqlDataPack.Benchmarks/README.md

Runs only the ValueConvert benchmark class by filtering with '*ValueConvert*'. Useful for focusing on per-cell type conversion measurements.

```bash
dotnet run -c Release --project tests/SqlDataPack.Benchmarks -- --filter '*ValueConvert*'

```

--------------------------------

### Download and run Linux executable

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/cli.md

Downloads the latest Linux x64 release tarball and runs the version check. The release page also offers win-x64, win-arm64, linux-arm64, osx-x64, and osx-arm64 builds, each with a SHA256SUMS file and build provenance attestation.

```bash
curl -sSL https://github.com/zachtbeer/sqldatapack/releases/latest/download/sqldatapack-linux-x64.tar.gz | tar xz
./sqldatapack --version
```

--------------------------------

### Compact the SQLite database with VACUUM

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/masked-slice-for-dev.md

Run the VACUUM command on the SQLite file before distribution to ensure that freed page content is overwritten and original data cannot be recovered.

```bash
sqlite3 dev-slice.sqlite 'VACUUM;'
```

--------------------------------

### Report import progress with SqlData.ImportAsync

Source: https://github.com/zachtbeer/sqldatapack/blob/main/website/docs/importing.md

Report import progress by assigning a Progress<SqlDataPackProgress> to ImportOptions.Default.Progress and passing the options to SqlData.ImportAsync. Progress events include Kind, TableName, RowsProcessed, and TotalRows; warnings are reported with Kind == SqlDataPackProgressKind.Warning.

```csharp
var progress = new Progress<SqlDataPackProgress>(p =>
{
    Console.WriteLine($"{p.Kind}: {p.TableName} {p.RowsProcessed}/{p.TotalRows}");
});

var options = ImportOptions.Default;
options.Progress = progress;

await SqlData.ImportAsync("support-snapshot.sqlite", targetSqlServerConnectionString, options);
```

--------------------------------

### Reformat code with cleanup script

Source: https://github.com/zachtbeer/sqldatapack/blob/main/AGENTS.md

Reformats the entire solution or specific files using the cleanup script. CI runs the same script and then checks for diffs. The script requires a throwaway .sln because jb cleanupcode reads .slnx as zero files.

```bash
dotnet tool restore && build/cleanup.sh          # whole solution, ~30s
```

```bash
build/cleanup.sh path/to/Changed.cs              # just these files, ~20s
```

--------------------------------

### Configure git hooks for formatting

Source: https://github.com/zachtbeer/sqldatapack/blob/main/CONTRIBUTING.md

Run these commands once per clone to enable automatic formatting on commit.

```bash
dotnet tool restore
git config core.hooksPath .githooks
```
Tokens:
Raw