Program.cs

using System.Diagnostics;
using System.Text;
using System.Text.Json;

const string dockerCli = "docker";

Console.WriteLine("=== Docker Database Backup Tool ===");

var backupRoot = Path.Combine(AppContext.BaseDirectory, "backups", DateTime.UtcNow.ToString("yyyyMMdd_HHmmss"));
Directory.CreateDirectory(backupRoot);
Console.WriteLine($"Backups will be stored under: {backupRoot}");

// Optional VHDX optimization settings (configured via environment variables)
// DB_BACKUP_ENABLE_VHD_OPTIMIZATION: "true"/"false"/"1"/"0" (default: false)
// DB_BACKUP_VHDX_PATH: full path to the VHDX file to optimize (e.g. Docker Desktop/WSL2 disk)
var enableVhdOptimization = IsTruthy(Environment.GetEnvironmentVariable("DB_BACKUP_ENABLE_VHD_OPTIMIZATION"));
var vhdxPath = Environment.GetEnvironmentVariable("DB_BACKUP_VHDX_PATH");

// 1. Discover running containers that look like database servers
var containers = await ListRunningContainersAsync();
if (containers.Count == 0)
{
    Console.WriteLine("No running containers found.");
    return;
}

var dbContainers = containers
    .Select(c => (Container: c, DbKind: DetectDatabaseKind(c)))
    .Where(t => t.DbKind is not null)
    .ToList();

if (dbContainers.Count == 0)
{
    Console.WriteLine("No database-like containers detected (Postgres/MySQL/MariaDB/SQL Server).");
    return;
}

Console.WriteLine();
Console.WriteLine("Discovered database containers:");
foreach (var (container, dbKind) in dbContainers)
{
    Console.WriteLine($" - {container.Name} ({container.Image}) -> {dbKind}");
}

Console.WriteLine();

var successes = new List<string>();
var failures = new List<string>();

foreach (var (container, dbKind) in dbContainers)
{
    Console.WriteLine($"--- Backing up container: {container.Name} ({dbKind}) ---");

    try
    {
        var env = await GetContainerEnvAsync(container.Id);

        var timestamp = DateTime.UtcNow.ToString("yyyyMMdd_HHmmss");
        string containerBackupPath;
        string localFileName;

        switch (dbKind)
        {
            case "postgres":
                {
                    var user = GetFirstNonEmptyEnv(env, "POSTGRES_USER", "POSTGRES_USERNAME") ?? "postgres";
                    var db = GetFirstNonEmptyEnv(env, "POSTGRES_DB") ?? user;
                    var password = GetFirstNonEmptyEnv(env, "POSTGRES_PASSWORD");

                    if (password is null)
                    {
                        throw new InvalidOperationException("POSTGRES_PASSWORD environment variable not found; cannot run non-interactive pg_dump.");
                    }

                    localFileName = $"{SanitizeFileName(container.Name)}_postgres_{db}_{timestamp}.dump";
                    var fileInContainer = $"{Guid.NewGuid():N}.dump";
                    containerBackupPath = $"/tmp/{fileInContainer}";

                    var shellCommand =
                        $"PGPASSWORD='{password}' pg_dump -U '{user}' -d '{db}' -F c -Z 9 -f '{containerBackupPath}'";

                    await RunDockerExecShellAsync(container.Id, shellCommand);
                    await CopyFromContainerAsync(container.Id, containerBackupPath, Path.Combine(backupRoot, localFileName));
                    await RemoveFileInContainerAsync(container.Id, containerBackupPath);
                    break;
                }

            case "mysql":
                {
                    var user = GetFirstNonEmptyEnv(env, "MYSQL_USER", "MYSQL_USERNAME", "MYSQL_ROOT_USER") ?? "root";
                    var password = GetFirstNonEmptyEnv(env, "MYSQL_PASSWORD", "MYSQL_ROOT_PASSWORD");
                    var db = GetFirstNonEmptyEnv(env, "MYSQL_DATABASE");

                    if (password is null)
                    {
                        throw new InvalidOperationException("MySQL password environment variable (MYSQL_PASSWORD / MYSQL_ROOT_PASSWORD) not found; cannot run non-interactive mysqldump.");
                    }

                    if (string.IsNullOrWhiteSpace(db))
                    {
                        throw new InvalidOperationException("MYSQL_DATABASE environment variable not found; cannot determine which database to dump.");
                    }

                    localFileName = $"{SanitizeFileName(container.Name)}_mysql_{db}_{timestamp}.sql.gz";
                    var fileInContainer = $"{Guid.NewGuid():N}.sql.gz";
                    containerBackupPath = $"/tmp/{fileInContainer}";

                    var shellCommand =
                        $"mysqldump -u'{user}' -p'{password}' '{db}' | gzip > '{containerBackupPath}'";

                    await RunDockerExecShellAsync(container.Id, shellCommand);
                    await CopyFromContainerAsync(container.Id, containerBackupPath, Path.Combine(backupRoot, localFileName));
                    await RemoveFileInContainerAsync(container.Id, containerBackupPath);
                    break;
                }

            case "sqlserver":
                {
                    var user = GetFirstNonEmptyEnv(env, "SA_USER", "MSSQL_USER") ?? "sa";
                    var password = GetFirstNonEmptyEnv(env, "SA_PASSWORD", "MSSQL_SA_PASSWORD", "MSSQL_PASSWORD");

                    if (password is null)
                    {
                        throw new InvalidOperationException("SQL Server password environment variable (SA_PASSWORD / MSSQL_SA_PASSWORD / MSSQL_PASSWORD) not found; cannot run non-interactive backup.");
                    }

                    var sqlcmd = await EnsureSqlcmdAvailableAsync(container.Id);

                    Console.WriteLine("Discovering SQL Server databases to back up (excluding system databases)...");

                    var listQuery =
                        "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb');";

                    var (listCode, listStdout, listStderr) = await RunDockerExecShellRawAsync(
                        container.Id,
                        $"{sqlcmd} -C -S localhost -U '{user}' -P '{password}' -h -1 -W -Q \"{listQuery}\""
                    );

                    if (listCode != 0)
                    {
                        throw new InvalidOperationException(
                            $"Failed to list SQL Server databases. Exit code: {listCode}. Error: {listStderr}");
                    }

                    var databaseNames = listStdout
                        .Split(new[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)
                        .Select(x => x.Trim())
                        .Where(x => !string.IsNullOrWhiteSpace(x))
                        .ToList();

                    if (databaseNames.Count == 0)
                    {
                        Console.WriteLine("No user databases found to back up in this SQL Server instance.");
                        break;
                    }

                    Console.WriteLine("Databases to back up:");
                    foreach (var dbName in databaseNames)
                    {
                        Console.WriteLine($" - {dbName}");
                    }

                    foreach (var dbName in databaseNames)
                    {
                        var safeDbName = dbName.Replace("]", "]]");

                        localFileName = $"{SanitizeFileName(container.Name)}_sqlserver_{dbName}_{timestamp}.bak";
                        var fileInContainer = $"{Guid.NewGuid():N}.bak";
                        containerBackupPath = $"/tmp/{fileInContainer}";

                        var backupQuery =
                            $"BACKUP DATABASE [{safeDbName}] TO DISK = N'/tmp/{fileInContainer}' WITH INIT, COMPRESSION;";

                        var backupCommand =
                            $"{sqlcmd} -C -S localhost -U '{user}' -P '{password}' -Q \"{backupQuery}\"";

                        Console.WriteLine($"Backing up database '{dbName}'...");
                        await RunDockerExecShellAsync(container.Id, backupCommand);
                        await CopyFromContainerAsync(container.Id, containerBackupPath,
                            Path.Combine(backupRoot, localFileName));
                        await RemoveFileInContainerAsync(container.Id, containerBackupPath);
                    }

                    break;
                }

            default:
                Console.WriteLine($"Unsupported database kind '{dbKind}' for container {container.Name}. Skipping.");
                continue;
        }

        successes.Add(container.Name);
        Console.WriteLine($"Backup for container '{container.Name}' completed.");
        Console.WriteLine();
    }
    catch (Exception ex)
    {
        failures.Add($"{container.Name}: {ex.Message}");
        Console.WriteLine($"ERROR while backing up '{container.Name}': {ex.Message}");
        Console.WriteLine();
    }
}

Console.WriteLine("=== Summary ===");
if (successes.Count > 0)
{
    Console.WriteLine("Successful backups:");
    foreach (var name in successes)
    {
        Console.WriteLine($" - {name}");
    }
}
else
{
    Console.WriteLine("No successful backups.");
}

if (failures.Count > 0)
{
    Console.WriteLine();
    Console.WriteLine("Failures:");
    foreach (var failure in failures)
    {
        Console.WriteLine($" - {failure}");
    }
}

Console.WriteLine();

await MaybeOptimizeVhdAsync(enableVhdOptimization, vhdxPath);

Console.WriteLine("Done.");

// --- Helper methods and models ---

static string? DetectDatabaseKind(DockerContainer c)
{
    var image = c.Image.ToLowerInvariant();
    var name = c.Name.ToLowerInvariant();

    if (image.Contains("postgres") || name.Contains("postgres"))
        return "postgres";

    if (image.Contains("mysql") || image.Contains("mariadb") || name.Contains("mysql") || name.Contains("mariadb"))
        return "mysql";

    if (image.Contains("mssql") || image.Contains("sqlserver") || name.Contains("mssql") || name.Contains("sqlserver"))
        return "sqlserver";

    return null;
}

static string SanitizeFileName(string name)
{
    foreach (var c in Path.GetInvalidFileNameChars())
    {
        name = name.Replace(c, '_');
    }

    return name;
}

static bool IsTruthy(string? value)
{
    if (string.IsNullOrWhiteSpace(value)) return false;
    var v = value.Trim();
    return v.Equals("true", StringComparison.OrdinalIgnoreCase)
           || v.Equals("yes", StringComparison.OrdinalIgnoreCase)
           || v.Equals("y", StringComparison.OrdinalIgnoreCase)
           || v.Equals("1", StringComparison.OrdinalIgnoreCase);
}

static string? GetFirstNonEmptyEnv(Dictionary<string, string> env, params string[] keys)
{
    foreach (var key in keys)
    {
        if (env.TryGetValue(key, out var value) && !string.IsNullOrWhiteSpace(value))
        {
            return value;
        }
    }

    return null;
}

static async Task<List<DockerContainer>> ListRunningContainersAsync()
{
    var (code, stdout, stderr) = await RunProcessAsync(
        dockerCli,
        "ps --format \"{{.ID}}|{{.Image}}|{{.Names}}\""
    );

    if (code != 0)
    {
        throw new InvalidOperationException($"Failed to list containers via 'docker ps'. Exit code: {code}. Error: {stderr}");
    }

    var list = new List<DockerContainer>();
    using var reader = new StringReader(stdout);
    string? line;
    while ((line = await reader.ReadLineAsync()) is not null)
    {
        if (string.IsNullOrWhiteSpace(line)) continue;
        var parts = line.Split('|');
        if (parts.Length < 3) continue;
        list.Add(new DockerContainer
        {
            Id = parts[0].Trim(),
            Image = parts[1].Trim(),
            Name = parts[2].Trim()
        });
    }

    return list;
}

static async Task<Dictionary<string, string>> GetContainerEnvAsync(string containerId)
{
    var (code, stdout, stderr) = await RunProcessAsync(
        dockerCli,
        $"inspect {containerId}"
    );

    if (code != 0)
    {
        throw new InvalidOperationException($"Failed to inspect container {containerId}. Exit code: {code}. Error: {stderr}");
    }

    var envDict = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);

    using var jsonDoc = JsonDocument.Parse(stdout);
    var root = jsonDoc.RootElement;
    if (root.ValueKind != JsonValueKind.Array || root.GetArrayLength() == 0)
    {
        return envDict;
    }

    var first = root[0];
    if (first.TryGetProperty("Config", out var config) &&
        config.TryGetProperty("Env", out var envArr) &&
        envArr.ValueKind == JsonValueKind.Array)
    {
        foreach (var envItem in envArr.EnumerateArray())
        {
            if (envItem.ValueKind != JsonValueKind.String) continue;
            var kv = envItem.GetString();
            if (string.IsNullOrEmpty(kv)) continue;
            var idx = kv.IndexOf('=', StringComparison.Ordinal);
            if (idx <= 0) continue;
            var key = kv[..idx];
            var value = kv[(idx + 1)..];
            envDict[key] = value;
        }
    }

    return envDict;
}

static async Task RunDockerExecShellAsync(string containerId, string shellCommand)
{
    var (code, stdout, stderr) = await RunDockerExecShellRawAsync(containerId, shellCommand);
    if (code != 0)
    {
        var message = new StringBuilder();
        message.AppendLine($"docker exec failed for container {containerId}.");
        message.AppendLine($"Exit code: {code}");
        if (!string.IsNullOrWhiteSpace(stdout))
        {
            message.AppendLine("STDOUT:");
            message.AppendLine(stdout);
        }
        if (!string.IsNullOrWhiteSpace(stderr))
        {
            message.AppendLine("STDERR:");
            message.AppendLine(stderr);
        }

        throw new InvalidOperationException(message.ToString());
    }
}

static Task<(int ExitCode, string StdOut, string StdErr)> RunDockerExecShellRawAsync(string containerId, string shellCommand)
{
    // We wrap the command in sh -c "..." and escape inner double-quotes
    var escaped = shellCommand.Replace("\"", "\\\"");
    var args = $"exec {containerId} sh -c \"{escaped}\"";
    return RunProcessAsync(dockerCli, args);
}

static async Task<string> EnsureSqlcmdAvailableAsync(string containerId)
{
    // Try to find sqlcmd from mssql-tools(/18) or on PATH
    async Task<string?> DetectSqlcmdAsync()
    {
        var (code, stdout, _) = await RunDockerExecShellRawAsync(
            containerId,
            "if [ -x /opt/mssql-tools18/bin/sqlcmd ]; then echo /opt/mssql-tools18/bin/sqlcmd; " +
            "elif [ -x /opt/mssql-tools/bin/sqlcmd ]; then echo /opt/mssql-tools/bin/sqlcmd; " +
            "elif command -v sqlcmd >/dev/null 2>&1; then echo sqlcmd; " +
            "else echo ''; fi"
        );

        if (code != 0)
        {
            return null;
        }

        var token = stdout
            .Split(new[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)
            .FirstOrDefault()
            ?.Trim();

        return string.IsNullOrWhiteSpace(token) ? null : token;
    }

    var existing = await DetectSqlcmdAsync();
    if (!string.IsNullOrEmpty(existing))
    {
        return existing;
    }

    Console.WriteLine("sqlcmd was not found inside the SQL Server container. Trying to install it automatically (this may take a few minutes)...");

    var packageManager = await DetectPackageManagerAsync(containerId);

    switch (packageManager)
    {
        case "apt":
            await InstallSqlcmdWithAptAsync(containerId);

            var installed = await DetectSqlcmdAsync();
            if (!string.IsNullOrEmpty(installed))
            {
                Console.WriteLine("sqlcmd (from mssql-tools) was installed successfully inside the container.");
                return installed!;
            }

            throw new InvalidOperationException(
                "Tried to install mssql-tools (sqlcmd) with apt-get inside the SQL Server container, but it is still not available. Please see the README for manual installation steps."
            );

        case "yum":
        case "dnf":
        case "zypper":
            throw new InvalidOperationException(
                $"sqlcmd is not available inside the SQL Server container and automatic installation is currently only supported for apt-based images. Detected package manager: {packageManager}. Please install the SQL Server command-line tools (mssql-tools) inside the container manually as described in the README."
            );

        default:
            throw new InvalidOperationException(
                "sqlcmd is not available inside the SQL Server container and a supported package manager (apt/yum/dnf/zypper) was not found. Please install the SQL Server command-line tools (mssql-tools) inside the container manually as described in the README."
            );
    }
}

static async Task<string> DetectPackageManagerAsync(string containerId)
{
    var (code, stdout, _) = await RunDockerExecShellRawAsync(
        containerId,
        "if command -v apt-get >/dev/null 2>&1; then echo apt; " +
        "elif command -v yum >/dev/null 2>&1; then echo yum; " +
        "elif command -v dnf >/dev/null 2>&1; then echo dnf; " +
        "elif command -v zypper >/dev/null 2>&1; then echo zypper; " +
        "else echo unknown; fi"
    );

    if (code != 0 || string.IsNullOrWhiteSpace(stdout))
    {
        return "unknown";
    }

    var token = stdout
        .Split(new[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)
        .FirstOrDefault()
        ?.Trim();

    return string.IsNullOrWhiteSpace(token) ? "unknown" : token!;
}

static async Task InstallSqlcmdWithAptAsync(string containerId)
{
    var script = string.Join(" && ", new[]
    {
        "set -e",
        "if ! command -v curl >/dev/null 2>&1; then apt-get update && apt-get install -y curl gnupg; fi",
        "if [ -f /etc/os-release ]; then . /etc/os-release; else ID=ubuntu; VERSION_ID=22.04; fi",
        "curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - >/dev/null 2>&1",
        "curl https://packages.microsoft.com/config/$ID/$VERSION_ID/prod.list > /etc/apt/sources.list.d/mssql-release.list",
        "apt-get update",
        "ACCEPT_EULA=Y apt-get install -y mssql-tools18 unixodbc-dev",
        "ln -sf /opt/mssql-tools18/bin/sqlcmd /usr/local/bin/sqlcmd || true"
    });

    var (code, stdout, stderr) = await RunDockerExecShellRawAsync(containerId, script);

    if (code != 0)
    {
        var message = new StringBuilder();
        message.AppendLine("Automatic installation of sqlcmd using apt-get inside the SQL Server container failed.");
        message.AppendLine($"Exit code: {code}");
        if (!string.IsNullOrWhiteSpace(stdout))
        {
            message.AppendLine("STDOUT:");
            message.AppendLine(stdout);
        }
        if (!string.IsNullOrWhiteSpace(stderr))
        {
            message.AppendLine("STDERR:");
            message.AppendLine(stderr);
        }

        throw new InvalidOperationException(message.ToString());
    }
}

static async Task MaybeOptimizeVhdAsync(bool enableVhdOptimization, string? vhdxPath)
{
    if (!enableVhdOptimization)
    {
        return;
    }

    if (!OperatingSystem.IsWindows())
    {
        Console.WriteLine("VHDX optimization is enabled but this operating system is not Windows. Skipping VHDX optimization.");
        return;
    }

    if (string.IsNullOrWhiteSpace(vhdxPath))
    {
        Console.WriteLine("VHDX optimization is enabled but DB_BACKUP_VHDX_PATH is not set. Skipping VHDX optimization.");
        return;
    }

    if (!File.Exists(vhdxPath))
    {
        Console.WriteLine($"VHDX optimization is enabled but the VHDX file was not found: {vhdxPath}. Skipping VHDX optimization.");
        return;
    }

    Console.WriteLine();
    Console.WriteLine("=== VHDX Optimization ===");
    Console.WriteLine("About to run Optimize-VHD on the configured VHDX file.");
    Console.WriteLine("IMPORTANT: Before using this feature, quit Docker Desktop and stop WSL2 so that the VHDX is not in use.");

    try
    {
        await OptimizeVhdAsync(vhdxPath);
    }
    catch (Exception ex)
    {
        Console.WriteLine($"VHDX optimization failed: {ex.Message}");
    }
}

static async Task OptimizeVhdAsync(string vhdxPath)
{
    // Escape single quotes for PowerShell
    var escapedPath = vhdxPath.Replace("'", "''");
    var psCommand =
        $"Import-Module Hyper-V -ErrorAction SilentlyContinue; Optimize-VHD -Path '{escapedPath}' -Mode Full";

    var args =
        $"-NoProfile -NonInteractive -ExecutionPolicy Bypass -Command \"{psCommand}\"";

    var (code, stdout, stderr) = await RunProcessAsync("powershell", args);

    if (code != 0)
    {
        var message = new StringBuilder();
        message.AppendLine("Optimize-VHD failed.");
        message.AppendLine($"Exit code: {code}");
        if (!string.IsNullOrWhiteSpace(stdout))
        {
            message.AppendLine("STDOUT:");
            message.AppendLine(stdout);
        }
        if (!string.IsNullOrWhiteSpace(stderr))
        {
            message.AppendLine("STDERR:");
            message.AppendLine(stderr);
        }

        throw new InvalidOperationException(message.ToString());
    }

    Console.WriteLine("VHDX optimization completed successfully.");
}

static async Task CopyFromContainerAsync(string containerId, string sourcePathInContainer, string localDestinationPath)
{
    Directory.CreateDirectory(Path.GetDirectoryName(localDestinationPath)!);

    var args = $"cp {containerId}:{sourcePathInContainer} \"{localDestinationPath}\"";
    var (code, stdout, stderr) = await RunProcessAsync(dockerCli, args);

    if (code != 0)
    {
        throw new InvalidOperationException(
            $"docker cp failed (container {containerId}, source {sourcePathInContainer}). Exit code: {code}. Error: {stderr}"
        );
    }
}

static async Task RemoveFileInContainerAsync(string containerId, string pathInContainer)
{
    var args = $"exec {containerId} rm -f \"{pathInContainer}\"";
    var (code, _, stderr) = await RunProcessAsync(dockerCli, args);

    if (code != 0)
    {
        Console.WriteLine($"Warning: failed to remove temp file {pathInContainer} in container {containerId}. Error: {stderr}");
    }
}

static async Task<(int ExitCode, string StdOut, string StdErr)> RunProcessAsync(string fileName, string arguments)
{
    var psi = new ProcessStartInfo
    {
        FileName = fileName,
        Arguments = arguments,
        RedirectStandardOutput = true,
        RedirectStandardError = true,
        UseShellExecute = false,
        CreateNoWindow = true
    };

    using var process = new Process { StartInfo = psi };

    var stdOutTaskCompletion = new TaskCompletionSource<string>();
    var stdErrTaskCompletion = new TaskCompletionSource<string>();

    var stdOutBuilder = new StringBuilder();
    var stdErrBuilder = new StringBuilder();

    process.OutputDataReceived += (_, e) =>
    {
        if (e.Data == null)
        {
            stdOutTaskCompletion.TrySetResult(stdOutBuilder.ToString());
        }
        else
        {
            stdOutBuilder.AppendLine(e.Data);
        }
    };

    process.ErrorDataReceived += (_, e) =>
    {
        if (e.Data == null)
        {
            stdErrTaskCompletion.TrySetResult(stdErrBuilder.ToString());
        }
        else
        {
            stdErrBuilder.AppendLine(e.Data);
        }
    };

    if (!process.Start())
    {
        throw new InvalidOperationException($"Failed to start process '{fileName}'.");
    }

    process.BeginOutputReadLine();
    process.BeginErrorReadLine();

    await Task.Run(() => process.WaitForExit());

    var stdout = await stdOutTaskCompletion.Task;
    var stderr = await stdErrTaskCompletion.Task;

    return (process.ExitCode, stdout, stderr);
}

file sealed class DockerContainer
{
    public string Id { get; init; } = string.Empty;
    public string Image { get; init; } = string.Empty;
    public string Name { get; init; } = string.Empty;
}