Created
May 15, 2024 17:13
-
-
Save joshuacurtiss/307226edee9a7f4021014de4e17440bb to your computer and use it in GitHub Desktop.
Restores a MSSQL backup to a Docker instance of MSSQL for convenient viewing.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment