Restores a MSSQL backup to a Docker instance of MSSQL for convenient viewing.

restore-mssql-database-docker.sh

#!/bin/bash

#
# Starts up an ephemeral Docker container of MSSQL and restores your provided backup file into it,
# using the filename as the database name. When it's done, you can connect to the container to
# browse and query the database.
#

function rand_char() {
    start=${1:-65} # Default to capital letters
    range=${2:-26} # Default to length of alphabet
    echo -e "\\0$(printf '%03o' "$((start + RANDOM % range))")"
}

image=mcr.microsoft.com/mssql/server:2022-latest
usage="$0 [--password=yourpassword] [--port=port] file.bak"
# Default randomized password that meets MSSQL password policy (upper/lower/digit/length)
password="$(rand_char)$(rand_char 97)$(rand_char 48 10)$(openssl rand -hex 3)"
port=1433

# Parse command-line options and their arguments
while getopts "hp:P:-:" OPT; do
    # Add support for long options
    if [ "$OPT" = "-" ]; then
        OPT="${OPTARG%%=*}"
        OPTARG="${OPTARG#"$OPT"}"
        OPTARG="${OPTARG#=}"
    fi
    case "$OPT" in
        p | port) port="$OPTARG";;
        P | password) password="$OPTARG";;
        h | help) echo "Usage: $usage"; exit;;
        \?) echo "Invalid option: -$OPTARG" >&2; echo "Usage: $usage"; exit 1;;
        :) echo "Option -$OPTARG requires an argument." >&2; exit 1;;
    esac
done
shift $((OPTIND-1))
backup_file=$1
container_backup=/var/backups/database.bak
database_name=$(basename -s .bak "$backup_file")

# Parameter validation (You must provide the backup file)
if [ -z "$backup_file" ]; then
    echo "Usage: $usage"
    exit 1
fi

# Start the container
container=$(docker run -d \
    --platform linux/amd64 \
    -p "$port:1433" \
    -e "ACCEPT_EULA=Y" \
    -e "MSSQL_SA_PASSWORD=$password" \
    "$image")

# Wait for container to initialize before copying backup file
echo -n Waiting for SQL to start...
attempts=0
while ! docker exec "$container" /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$password" -Q "select 1" &> /dev/null; do
    if [ "$((attempts++))" -gt 9 ]; then
        echo " Something is wrong with the SQL server. Aborting." >&2
        exit 1
    fi
    sleep 1
    echo -n .
done

docker cp "$backup_file" "$container:$container_backup"

# Get the logical files from the backup so we can restore properly. Build the restore SQL.
echo Getting the logical files of the backup...
restore_sql="RESTORE DATABASE $database_name FROM DISK='$container_backup' WITH"
# When querying the file list, we tell sqlcmd to exclude the header (-h-1) and unnecessary whitespace (-W)
# and then ignore the last two lines (head -n -2) which just output the row count.
while IFS=' ' read -r logical_name _; do
    echo "  - $logical_name"
    restore_sql="$restore_sql MOVE '$logical_name' TO '/var/opt/mssql/data/$database_name-$logical_name',"
done < <(docker exec "$container" sh -c "/opt/mssql-tools/bin/sqlcmd -h-1 -W -S localhost -U sa -P '$password' -Q \"RESTORE FILELISTONLY FROM DISK='$container_backup';\" | head -n -2")
restore_sql="$restore_sql REPLACE;"

# Run the restore!
echo Restoring database...
docker exec "$container" /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$password" -Q "$restore_sql"

echo 🎉 "Done! Now open the database '$database_name' with a MSSQL client on port $port, user 'sa', password '$password'."
echo Warning: This container will not persist the database. It will cease to exist when the container is removed.