Backup databases inside a MS SQL Server docker container. This bashscript is made for interactivity and ease of use while exporting or importing databases running inside a microsoft-mssql-server container

ms_backup_docker.sh

#!/bin/bash
# Author: Jack Billström (jackbillstrom)
# Description: Backup and restore MS SQL databases running in docker containers with a "GUI" using gum.sh
# Dependencies: gum.sh, docker, dialog
# Usage: ./ms_backup_docker.sh
# Version: 1.1.0

# Intro
gum style --border normal --margin "1" --padding "1 2" --border-foreground 212 "🐋 $(gum style --foreground 212 'ms sql') manager"

# Find docker containers running on port 1433
CONTAINERS=$(docker ps --format '{{.Names}}' --filter "expose=1433/tcp")

# Render choices below
echo "Select a container"
SELECTED=$(echo "$CONTAINERS" | gum choose --height 15)

# Get password from container ID
SA_PASS=$(docker inspect "$SELECTED" | grep SA_PASSWORD | awk -F'=' '{print $2}' | tr -d '"')

# Show a list of actions (Backup, Restore)
echo "Select an action"
ACTION=$(gum choose --height 15 "Backup" "Restore")

# Fetch the list of databases from the selected container
DATABASES=$(docker exec -it "$SELECTED" /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "$SA_PASS" -h -1 -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');" 2>/dev/null | tr -d '\r')

if [ "$ACTION" == "Backup" ]; then
    echo "Select databases for backup"
    SELECTED_DATABASES=$(echo "$DATABASES" | gum choose --no-limit)
    echo "Select directory to save backups"
    DIRECTORY_PATH=$(dialog --stdout --title "Choose a directory" --dselect "$HOME/" 10 50)
else
    echo "Select a .bak file for restore"
    BAK_FILE=$(gum file $HOME)
    echo "Restore to existing database or create a new one?"
    CHOICE=$(gum choose --height 15 "Existing" "New")
    if [ "$CHOICE" == "New" ]; then
        echo "Enter the name of the new database:"
        read NEW_DB_NAME
    else
        echo "Select databases for restore"
        SELECTED_DATABASE=$(echo "$DATABASES" | gum choose)
    fi
fi

# Confirm before executing
gum confirm || { echo "Action not permitted"; exit 1; }

DATE=$(printf '%(%Y-%m-%d)T\n')

if [ "$ACTION" == "Backup" ]; then
    IFS=$'\n' # Set Internal Field Separator to newline for iterating over lines
    for db in $SELECTED_DATABASES; do
        EXPORT_NAME_TRIMMED=$(echo $db | xargs) # Detta tar bort extra vitt utrymme
        EXPORT_NAME_FINAL="${EXPORT_NAME_TRIMMED}_full_${DATE}"

        CONTAINER_PATH="/var/opt/mssql/data/${db}_${DATE}.bak"
        HOST_PATH="$DIRECTORY_PATH/$EXPORT_NAME_FINAL.bak"
        
        # Colorize output
        echo -e "\e[90m"
        docker exec -it $SELECTED /opt/mssql-tools/bin/sqlcmd \
           -S localhost -U SA -P "$SA_PASS" \
           -Q "BACKUP DATABASE [$db] TO DISK = N'$CONTAINER_PATH' WITH NOFORMAT, NOINIT, NAME = '$EXPORT_NAME_FINAL', SKIP, NOREWIND, NOUNLOAD, STATS = 10" \
           | sed 's/^[ \t]*//'
        echo -e "\e[0m"
        
        gum spin --spinner dot --title "Copying backup file to host..." -- true
        if ! docker cp "$SELECTED:$CONTAINER_PATH" "$HOST_PATH"; then
            echo "Failed to copy from container to host."
            exit 1
        fi
    done
else
    CONTAINER_PATH="/var/opt/mssql/data/$(basename "$BAK_FILE")"
    docker cp "$BAK_FILE" "$SELECTED:$CONTAINER_PATH"
    docker exec -u 0 -it "$SELECTED" chmod 644 "$CONTAINER_PATH"

    if [ "$CHOICE" == "New" ]; then
        DB_NAME=$NEW_DB_NAME
    else
        DB_NAME=$SELECTED_DATABASE
    fi

    docker exec -it "$SELECTED" /opt/mssql-tools/bin/sqlcmd \
        -S localhost -U SA -P "$SA_PASS" \
        -Q "RESTORE DATABASE [$DB_NAME] FROM DISK = N'$CONTAINER_PATH' WITH REPLACE"
fi

echo "✅ Operation completed."

1.1.0

Updated with Restore option from BAK-files outside the docker-container, and with the possibility to choose the target location to a Existing or New database.