A robust Bash script for backing up a SQL Server database from a Docker container to the host, with secure password handling, customisable options, optional compression, and enhanced error management.

backup_from_container.sh

#!/usr/bin/env bash
# SQL Server Docker Backup Script

set -euo pipefail  # Exit on error, undefined var, pipe failure

# Default values
DEFAULT_CONTAINER_NAME="sqlserver"
DEFAULT_DATABASE_NAME="TestDb"
DEFAULT_SA_PASSWORD="YourStrong@Passw0rd"

# Usage help
usage() {
  cat << EOF
Usage: $0 [options]

Options:
  -c, --container NAME     Docker container name (default: $DEFAULT_CONTAINER_NAME)
  -d, --database NAME      Database name to backup (default: $DEFAULT_DATABASE_NAME)
  -p, --password PASS      SA password (if not provided, uses MSSQL_SA_PASSWORD env var or prompts)
  -o, --output DIR         Host output directory for backups (default: ./backups)
  -z, --compress           Compress the .bak file with gzip after copying (saves space)
  -h, --help               Show this help

Environment Variables:
  MSSQL_SA_PASSWORD        Recommended: Set this to your SA password instead of passing via flag

Example:
  MSSQL_SA_PASSWORD='MySecretPass' $0 -c my-sql-container -d MyDb -z
EOF
  exit 1
}

# Parse arguments
CONTAINER_NAME="$DEFAULT_CONTAINER_NAME"
DATABASE_NAME="$DEFAULT_DATABASE_NAME"
OUTPUT_DIR="./backups"
COMPRESS=false
SA_PASSWORD=""

while [[ $# -gt 0 ]]; do
  case $1 in
    -c|--container) CONTAINER_NAME="$2"; shift 2 ;;
    -d|--database) DATABASE_NAME="$2"; shift 2 ;;
    -p|--password) SA_PASSWORD="$2"; shift 2 ;;
    -o|--output) OUTPUT_DIR="$2"; shift 2 ;;
    -z|--compress) COMPRESS=true; shift ;;
    -h|--help) usage ;;
    *) echo "Unknown option: $1"; usage ;;
  esac
done

# Password handling (priority: flag > env var > prompt > default)
if [[ -z "$SA_PASSWORD" ]]; then
  if [[ -n "${MSSQL_SA_PASSWORD:-}" ]]; then
    SA_PASSWORD="$MSSQL_SA_PASSWORD"
  elif [[ -z "${SA_PASSWORD:-}" ]]; then
    read -s -p "Enter SA password: " SA_PASSWORD
    echo
  else
    SA_PASSWORD="$DEFAULT_SA_PASSWORD"
    echo "Warning: Using default dev password. Set MSSQL_SA_PASSWORD env var for security!"
  fi
fi

# Validate required tools
command -v docker >/dev/null || { echo "Error: docker not found in PATH"; exit 1; }

# Create host backup directory
mkdir -p "$OUTPUT_DIR"

# Generate filename with seconds: e.g., 2026-01-06_13-30-45_TestDb.bak
FILE_NAME="$(date +%Y-%m-%d_%H-%M-%S)_${DATABASE_NAME}.bak"
CONTAINER_BACKUP_PATH="/var/opt/mssql/backups/$FILE_NAME"

echo "Backing up database '$DATABASE_NAME' from container '$CONTAINER_NAME'..."
echo "Backup file: $FILE_NAME"

# Ensure backup directory exists inside container
docker exec "$CONTAINER_NAME" mkdir -p /var/opt/mssql/backups

# Trap to cleanup partial backup on failure
trap 'echo "Error occurred. Attempting cleanup..."; docker exec "$CONTAINER_NAME" rm -f "$CONTAINER_BACKUP_PATH" 2>/dev/null || true' ERR

# Perform the backup using sqlcmd
# Removed -it (not needed for non-interactive), added -b to abort on error
docker exec "$CONTAINER_NAME" /opt/mssql-tools18/bin/sqlcmd \
  -S localhost \
  -U SA \
  -P "$SA_PASSWORD" \
  -C \
  -b \
  -Q "BACKUP DATABASE [$DATABASE_NAME] TO DISK = N'$CONTAINER_BACKUP_PATH' WITH NOFORMAT, NOINIT, NAME = N'$DATABASE_NAME-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

echo "Backup created inside container."

echo "Copying backup to host ($OUTPUT_DIR/$FILE_NAME)..."
docker cp "$CONTAINER_NAME:$CONTAINER_BACKUP_PATH" "$OUTPUT_DIR/$FILE_NAME"

# Optional compression
if [[ "$COMPRESS" = true ]]; then
  echo "Compressing backup file..."
  gzip "$OUTPUT_DIR/$FILE_NAME"
  echo "Compressed backup: $OUTPUT_DIR/${FILE_NAME}.gz"
else
  echo "Backup saved: $OUTPUT_DIR/$FILE_NAME"
fi

# Remove from container to save space
docker exec "$CONTAINER_NAME" rm "$CONTAINER_BACKUP_PATH"

echo "Backup completed successfully!"