Docker Database Backup Console App

This .NET console application scans your running Docker containers, detects common database images, and creates compressed backups into a local backups folder.

Supported database types:

• PostgreSQL: images/names containing postgres
• MySQL / MariaDB: images/names containing mysql or mariadb
• SQL Server: images/names containing mssql or sqlserver

Prerequisites

• Windows 11
• Docker Desktop (or other Docker installation) with the docker CLI available on your PATH
• .NET SDK (the project targets net10.0 using your installed SDK)

Your database containers should expose standard environment variables so the tool can connect:

• PostgreSQL
  • POSTGRES_USER (or POSTGRES_USERNAME)
  • POSTGRES_PASSWORD
  • POSTGRES_DB (falls back to POSTGRES_USER if not set)
• MySQL / MariaDB
  • MYSQL_USER / MYSQL_USERNAME / MYSQL_ROOT_USER (defaults to root if none set)
  • MYSQL_PASSWORD or MYSQL_ROOT_PASSWORD
  • MYSQL_DATABASE
• SQL Server
  • SA_USER or MSSQL_USER (defaults to sa if not set)
  • MSSQL_SA_PASSWORD (preferred) or SA_PASSWORD or MSSQL_PASSWORD – the password for the main SQL Server admin account (sa)
  • (Optional) MSSQL_DATABASE / MSSQL_DB / MSSQL_DB_NAME – not required by this tool, since it backs up all non-system databases in the SQL Server instance

What is MSSQL_SA_PASSWORD?

• MSSQL_SA_PASSWORD is the password for the main SQL Server administrator account, called sa.
• This app uses that password only to connect and read your database so it can create a backup.
• You should choose a password that is easy for you to remember but hard for others to guess.
• Do not share this password with other people and do not write it in public places.

If MSSQL_SA_PASSWORD is missing or wrong, the app cannot connect to SQL Server and the backup for that container will fail.

What the tool does

For each running container:

1. Lists containers via docker ps.
2. Detects whether the container looks like a Postgres or MySQL/MariaDB database by image/name.
3. Inspects env vars via docker inspect to get credentials and database name.
4. Executes a backup inside the container:
   • Postgres: uses pg_dump -F c -Z 9 (custom format, with compression).
   • MySQL/MariaDB: uses mysqldump | gzip to produce a compressed .sql.gz.
   • SQL Server: uses sqlcmd to back up all non-system databases with compression, writing one .bak file per database.
5. Copies the backup file to the host using docker cp into a timestamped subfolder of backups.
6. Cleans up the temporary file inside the container.

Each run creates a folder like:

backups\20260225_153012\

With files named similar to:

• my-postgres-postgres_postgres_20260225_153012.dump
• my-mysql-mariadb_mysql_appdb_20260225_153012.sql.gz
• my-mssql_sqlserver_appdb_20260225_153012.bak

How to run

From the project root (DockerBackupAndRestore):

dotnet run

The app will:

• Print the backup destination folder.
• List any detected database containers.
• Attempt backups for each supported container and print a success/failure summary.

You can run it whenever you want to take a snapshot; each run creates a new timestamped subfolder under backups.

How to set MSSQL_SA_PASSWORD using Docker Desktop (simple way)

If you use Docker Desktop and are not familiar with command-line tools, you can follow these steps:

1. Open Docker Desktop on your computer.
2. Go to the Containers (or Containers / Apps) tab.
3. Find your SQL Server container in the list (its image name usually includes mssql or sqlserver).
4. If the container is running, click Stop to stop it.
5. Click on the container and look for an option like Duplicate / Edit or Edit (this may vary slightly by Docker Desktop version).
6. In the settings screen, find the Environment variables section.
7. Add a new variable:
   • Name: MSSQL_SA_PASSWORD
   • Value: a strong password that you will remember (for example, a sentence with numbers and symbols).
8. Make sure the other required SQL Server variables (like the database name) are also set if needed.
9. Save the changes and start the container again (Docker Desktop may create a new container with the updated settings).
10. After the container is running with MSSQL_SA_PASSWORD set, run dotnet run again from this app to create a backup.

Keep your MSSQL_SA_PASSWORD somewhere safe (for example, in a password manager). You may also use it later to connect from tools like SQL Server Management Studio.

How to set MSSQL_SA_PASSWORD using docker-compose (for advanced users)

If you (or someone helping you) uses a docker-compose.yml file, you can set MSSQL_SA_PASSWORD in the environment section. Here is a simple example:

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: my-sqlserver
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrongPassword123!
      - MSSQL_DATABASE=MyAppDatabase
    ports:
      - "1433:1433"

After changing docker-compose.yml, run:

docker compose up -d

More advanced users can also set the variable when starting a container directly with docker run, for example:

docker run -e MSSQL_SA_PASSWORD=YourStrongPassword123! -e ACCEPT_EULA=Y ...

If you are not comfortable with these commands, it is usually easier to use Docker Desktop and follow the steps in the previous section.

How to set MSSQL_SA_PASSWORD from the terminal (PowerShell or Command Prompt)

Sometimes you might want to set the password only for one terminal window and reuse it in your Docker commands.

• In PowerShell (recommended on Windows 11):

  1. Open Windows Terminal or PowerShell.

  2. Type this command (change the password to your own strong password) and press Enter:

     $env:MSSQL_SA_PASSWORD = "YourStrongPassword123!"

  3. Start your SQL Server container using that variable:

     docker run -e MSSQL_SA_PASSWORD=$env:MSSQL_SA_PASSWORD -e ACCEPT_EULA=Y ...

  4. The password stored in $env:MSSQL_SA_PASSWORD exists only in this PowerShell window. If you close the window, you will need to set it again next time.

• In Command Prompt (cmd.exe):

  1. Open Command Prompt.

  2. Type this command and press Enter:

     set MSSQL_SA_PASSWORD=YourStrongPassword123!

  3. Start your SQL Server container using that variable:

     docker run -e MSSQL_SA_PASSWORD=%MSSQL_SA_PASSWORD% -e ACCEPT_EULA=Y ...

  4. The password stored in MSSQL_SA_PASSWORD exists only in this Command Prompt window. If you open a new window, you will need to set it again.

If you are not comfortable with terminal commands, you can ignore this section and instead use Docker Desktop as described earlier.

How to install sqlcmd

The SQL Server backup feature in this app uses a tool called sqlcmd inside your SQL Server container. Many official SQL Server on Linux images already include it.

If sqlcmd is not available inside the container, this app will try to:

• Detect the Linux package manager in the container (for example, apt-get, yum, dnf, or zypper).
• For apt-based images (such as Ubuntu/Debian), automatically install the Microsoft SQL Server command-line tools (mssql-tools18) and make sqlcmd available.
• For other images or if the automatic installation fails, show a clear error message and ask you to install sqlcmd (or mssql-tools) inside the container manually.

Automatic installation inside the container requires:

• Internet access from the container to reach Microsoft’s package servers.
• Permission to install packages (typical for official SQL Server images, which usually run as root).

You can also install sqlcmd on your own machine if you want to use it directly from Windows, macOS, or Linux:

• Windows 11 (recommended: Go-based sqlcmd)

  • Open Windows Terminal or PowerShell.

  • Run:

    winget install sqlcmd

  • This installs the modern cross-platform sqlcmd tool. You can also follow the official Microsoft instructions at: https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-download-install.

• macOS

  • Install using Homebrew (you or someone helping you should have Homebrew installed):

    brew install sqlcmd

  • For detailed steps, see the Microsoft docs: https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-download-install.

• Linux (Ubuntu/Debian/Red Hat and others)

  • For many Linux distributions, Microsoft provides packages for the SQL Server command-line tools (sqlcmd and bcp).
  • The exact commands depend on your Linux version, so the easiest way is to follow the official guide:
    • https://learn.microsoft.com/sql/linux/sql-server-linux-setup-tools

In most cases, you do not need to install sqlcmd on your Windows host for this backup tool to work, because the backup runs inside the SQL Server container. You only need to make sure your SQL Server image has sqlcmd available, or choose an official image that already includes it.

VHDX optimization (Docker Desktop / WSL2)

If you use Docker Desktop (with WSL2) on Windows 11, Docker stores its Linux data in a large VHDX file (a virtual disk file). Over time, that file can grow quite large. This app can optionally try to shrink that file using Windows' Optimize-VHD command after backups finish.

Requirements

• You are running Windows 11.
• The Hyper-V PowerShell module is installed and the Optimize-VHD cmdlet is available.
• You have permissions to run PowerShell commands (administrator privileges may be required).
• The VHDX file is not in use when optimization runs.

For Docker Desktop / WSL2, that usually means:

• Quit Docker Desktop.
• Make sure WSL2 is stopped (for example, close any WSL terminals and run wsl --shutdown in PowerShell or Command Prompt).

If the VHDX is still in use, Optimize-VHD will fail and the app will print an error, but the database backups will still be kept.

How to enable VHDX optimization

VHDX optimization is controlled via environment variables read by the app:

• DB_BACKUP_ENABLE_VHD_OPTIMIZATION
  • Set to true, yes, or 1 to enable optimization.
  • Leave unset or set to false/0 to disable it (default).
• DB_BACKUP_VHDX_PATH
  • Full path to the VHDX file you want to optimize.
  • For Docker Desktop with WSL2, a common location is something like:
    • C:\Users\<YourUser>\AppData\Local\Docker\wsl\data\ext4.vhdx

Example (PowerShell, for Docker Desktop VHDX):

$env:DB_BACKUP_ENABLE_VHD_OPTIMIZATION = "true"
$env:DB_BACKUP_VHDX_PATH = "C:\Users\YourUser\AppData\Local\Docker\wsl\data\ext4.vhdx"
dotnet run

When optimization is enabled and correctly configured:

1. The app runs all database container backups as usual.
2. After printing the backup summary, it runs a PowerShell command similar to:
   • Optimize-VHD -Path 'C:\path\to\disk.vhdx' -Mode Full
3. If optimization succeeds, you will see a message saying VHDX optimization completed successfully.
4. If it fails (for example, because the VHDX is in use), you will see the error details, but your backups are not deleted.

Notes and limitations

• The tool relies on standard environment variables commonly used by official images. If your container uses different variable names or custom users/databases, you may need to adjust the code accordingly.
• Password values must not contain single quotes for the current shell command construction.
• For SQL Server, the sqlcmd tool must be available in the container (this is true for typical official SQL Server on Linux images, or after installing sqlcmd as described above).
• If MSSQL_SA_PASSWORD (or the other SQL Server password variables) is not set or is incorrect, the app will not be able to back up SQL Server and will show an error for that container. Once you set MSSQL_SA_PASSWORD and restart the container, running dotnet run again will include your SQL Server database in the backups.