aux4/db-mssql-backup

Backup and restore provider commands for Microsoft SQL Server. This package extends the db:mssql profile (owned by aux4/db-mssql) with backup and restore commands that issue BACKUP DATABASE and RESTORE DATABASE T-SQL statements through the sqlcmd system CLI.

It is packaged separately from aux4/db-mssql on purpose: the core aux4/db-mssql query commands (execute, stream) use the Node driver and need no external binaries. Only backup/restore need the sqlcmd CLI, so that system dependency is isolated here -- install this package only when you need backup/restore.

Because it declares the same db:mssql profile, its commands merge into the existing profile: after installing both packages, aux4 db mssql --help shows the query commands and backup/restore together.

Server-side backup model

Unlike MySQL or PostgreSQL where a dump lands on the client machine, SQL Server backup is a server-side operation. BACKUP DATABASE writes the .bak file to the SQL Server host's filesystem, and RESTORE DATABASE reads it from there. The --path you provide is a path on the server, not on the machine running aux4.

This has important implications:

• The backup directory must exist on the server and be writable by the SQL

Server process (e.g. /var/opt/mssql/backup/ in Docker containers).

• You cannot compute a local checksum -- the file is not on the client. The

manifest reports checksum: null.

• To retrieve the backup file, use docker cp, scp, or a shared volume.

Installation

aux4 aux4 pkger install aux4/db-mssql-backup

This pulls in aux4/db-mssql as a dependency, so the full db:mssql profile (query commands + backup/restore) is available.

Configuration

Connection details are supplied through a config.yaml profile so credentials stay out of any backup catalog:

config:
  prod:
    host: 127.0.0.1
    port: 1433
    database: myapp
    user: sa
    password: YourStrong!Passw0rd

Use it with --configFile config.yaml --config prod. Individual values can also be passed directly with --host, --port, --database, --user, --password, which take precedence over config values.

Backup options

Backup behavior is controlled by variables, so options can be set once per profile and overridden per run on the command line:

config:
  prod:
    host: 127.0.0.1
    database: myapp
    user: sa
    password: YourStrong!Passw0rd
    compression: true
    copyOnly: true

| Option | Default | Effect | |--------|---------|--------| | compression | true | Enable SQL Server native backup compression | | copyOnly | -- | Create a copy-only backup that does not affect the log chain | | differential | -- | Create a differential backup instead of a full backup | | options | -- | Additional raw T-SQL WITH options, appended verbatim |

backup

Creates a native SQL Server backup using BACKUP DATABASE. The backup file is written on the SQL Server host, not locally.

The destination path is resolved as:

• --path <file> -- full server-side path to write the backup (takes

precedence).

• --dir <directory> + --file <name> -- combined as <dir>/<file>.

If neither is given, the command fails fast. When the resolved path has no extension, .bak is appended.

The BACKUP DATABASE statement uses WITH FORMAT, INIT to overwrite any existing backup sets in the file. When compression is true (the default), COMPRESSION is added to the WITH clause.

On success the command queries msdb.dbo.backupset for the compressed backup size and prints a result manifest as a single line of JSON to stdout:

aux4 db mssql backup --configFile config.yaml --config prod --path /var/opt/mssql/backup/myapp.bak

{"path":"/var/opt/mssql/backup/myapp.bak","bytes":1234567,"checksum":null,"status":"success","format":"mssql-native"}

The manifest (path, bytes, checksum, status, format) is consumed by the aux4/backup orchestrator to catalog each run. checksum is always null because the backup file resides on the server and cannot be hashed locally.

restore

Restores a database from a native SQL Server backup file using RESTORE DATABASE. The backup file must exist on the SQL Server host.

aux4 db mssql restore --configFile config.yaml --config prod --path /var/opt/mssql/backup/myapp.bak

{"path":"/var/opt/mssql/backup/myapp.bak","database":"myapp","status":"success","action":"restore"}

Path resolution (--path or --dir + --file) is identical to backup.

The RESTORE DATABASE statement uses WITH REPLACE to overwrite the existing database. Pass --noRecovery true to leave the database in RESTORING state for additional log restores in a restore chain.

Restore options

| Option | Default | Effect | |--------|---------|--------| | noRecovery | -- | Leave the database in RESTORING state (for log restore chains) | | options | -- | Additional raw T-SQL WITH options, appended verbatim |

Binary resolution

Both commands locate the sqlcmd binary automatically:

1. Check PATH first (command -v sqlcmd).
2. Fall back to the Homebrew keg-only location

($(brew --prefix mssql-tools)/bin/sqlcmd).

This works on Linux (binaries on PATH) and on macOS (where mssql-tools may be keg-only) with no manual PATH export. If the binary cannot be found anywhere, the command fails with an install hint.

System dependency

Requires the sqlcmd CLI:

• macOS: brew install mssql-tools
• Linux: Install sqlcmd

Docker usage

When running SQL Server in Docker, the backup path must be inside the container. The default backup directory is /var/opt/mssql/backup/. Example:

# Start SQL Server in Docker with a backup volume
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourStrong!Passw0rd' \
  -p 1433:1433 -v /tmp/mssql-backup:/var/opt/mssql/backup \
  mcr.microsoft.com/mssql/server:2022-latest

# Backup (path is inside the container)
aux4 db mssql backup --host 127.0.0.1 --port 1433 --user sa \
  --password 'YourStrong!Passw0rd' --database mydb \
  --path /var/opt/mssql/backup/mydb.bak

# The .bak file appears at /tmp/mssql-backup/mydb.bak on the host via the volume mount