Linux 命令行下高效备份与还原 SQL Server 数据库的完整实践方案-php教程-PHP中文网

Linux 命令行下高效备份与还原 SQL Server 数据库的完整实践方案

在 linux 环境（尤其是 docker 容器化场景）中，可使用 `mssql-scripter` + `sqlcmd` 组合实现类比 `pg_dump`/`psql` 的自动化数据库快照与恢复，适用于测试环境数据基线重置。

对于 PHP + Docker 架构下的自动化浏览器测试流程，频繁重置 SQL Server 数据库至初始状态是关键需求。遗憾的是，Microsoft 官方并未提供完全对标 pg_dump 的轻量级单二进制导出工具，但通过组合使用开源命令行工具 mssql-scripter（官方维护的跨平台脚本生成器）与 sqlcmd（官方 CLI 查询执行器），可构建稳定、可脚本化、符合 CI/CD 流程的数据库快照方案。

✅ 核心工具链说明

mssql-scripter：基于 Python 的命令行工具，支持连接 SQL Server 实例并生成包含架构（DDL）和数据（INSERT 语句）的 T-SQL 脚本。等效于 --schema-and-data 模式。
sqlcmd：Microsoft 官方提供的轻量级 CLI 工具，用于执行 T-SQL 脚本或查询，支持 -i 参数从文件导入。

⚠️ 注意：mssql-scripter 依赖底层 sqltoolsservice（SQL Tools Service Layer），旧版本在较新 Linux 发行版（如 Ubuntu 21.04+）上易因 OpenSSL 或编码兼容性问题失败，需手动指定新版服务层并修复 UTF 编码 bug。

? 推荐 Docker 构建方案（精简可靠）

以下 Dockerfile 片段已验证可在 Ubuntu 21.04+ 及后续 LTS（如 22.04）中稳定运行，适配现代 OpenSSL 和 Python 3.9+ 环境：

FROM ubuntu:22.04

# 安装基础依赖与 Microsoft ODBC 驱动、sqlcmd
RUN apt-get update && apt-get install -y \
      curl \
      gnupg \
      python3 \
      python3-pip \
      libunwind8 \
      && rm -rf /var/lib/apt/lists/*

# 添加 Microsoft GPG 密钥与仓库
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
  && curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/msprod.list

RUN apt-get update && apt-get install -y \
      msodbcsql18 \
      mssql-tools18 \
      && echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc

# 安装 mssql-scripter（需 Python 3.9+）
RUN pip3 install --no-cache-dir mssql-scripter

# 下载并部署兼容新版 OpenSSL 的 sqltoolsservice（v3.0.0-release.205）
ARG SQLTOOLS_URL=https://github.com/microsoft/sqltoolsservice/releases/download/v3.0.0-release.205/Microsoft.SqlTools.ServiceLayer-rhel-x64-net6.0.tar.gz
RUN mkdir -p /opt/Microsoft.SqlTools.ServiceLayer \
  && curl -sSL $SQLTOOLS_URL | tar -xzf - -C /opt/Microsoft.SqlTools.ServiceLayer --strip-components=1

# 修复 mssql-scripter 的 UTF-8 编码 bug（应为 UTF-16 输出以兼容 SQL Server BOM 处理）
RUN sed -i 's/utf-8/utf-16/g' /usr/local/lib/python3.*/dist-packages/mssqlscripter/main.py

# 设置环境变量供 mssql-scripter 自动发现服务层
ENV MSSQLTOOLSSERVICE_PATH=/opt/Microsoft.SqlTools.ServiceLayer
ENV PYTHONIOENCODING=utf-16

# 验证安装
RUN mssql-scripter --help >/dev/null && sqlcmd -? >/dev/null || exit 1

构建镜像后，即可在容器内执行标准化操作：

Seasonal Keyword Planner

将电商关键词映射到12个月需求曲线，生成逐月的listing、广告和内容发布时间表。

下载

? 导出数据库快照（等效 pg_dump）

mssql-scripter \
  -S mssql-server \          # SQL Server 主机名（Docker 内部网络）
  -d myapp_test \            # 目标数据库名
  -U sa \                    # 用户名
  -P 'MyStrongPass123!' \   # 密码（生产环境建议用 -i 交互或 AZURE_TOKEN）
  --schema-and-data \        # 同时导出结构 + 全量数据（含 INSERT）
  --target-server-version "SQL Server 2019" \  # 显式指定兼容版本，提升可靠性
  --file-path ./dump.sql

✅ 输出为 UTF-16 LE 编码 .sql 文件，含 BOM，确保 sqlcmd 正确解析中文/特殊字符。

? 导入快照（等效 psql）

/opt/mssql-tools18/bin/sqlcmd \
  -S mssql-server \
  -d myapp_test \
  -U sa \
  -P 'MyStrongPass123!' \
  -i ./dump.sql \
  -I  # 启用带引号标识符（推荐，兼容含空格/关键字的表名）

? 在 PHP 测试流程中集成示例（伪代码）

// beforeTest(): 重置数据库
exec("docker exec php-test-container mssql-scripter -S db -d testdb -U sa -P '$pass' --schema-and-data > /tmp/base.sql");
exec("docker exec php-test-container /opt/mssql-tools18/bin/sqlcmd -S db -d testdb -U sa -P '$pass' -i /tmp/base.sql");

// afterTest(): 可选清理或保留快照用于调试

⚠️ 关键注意事项

密码安全：避免在命令行明文传递密码（易被 ps 泄露）。推荐改用 sqlcmd 的 -G（Azure AD）或通过 .env + sqlcmd -v 传参；或使用 mssql-scripter --password-env 结合环境变量。
大数据量优化：--schema-and-data 对百万级以上行性能较差。如仅需结构重置，改用 --schema-only + --data-only 分离策略，并配合 BULK INSERT 或 bcp 工具加速导入。
权限要求：执行用户需具备 db_owner 角色或 VIEW DEFINITION + SELECT 权限。
替代方案评估：
- bcp：原生高性能批量导入导出，但不生成可读 SQL，适合大数据迁移；
- sqlpackage（.NET Core）：支持 DACPAC 导出，但 Linux 支持需 .NET 6+ 且无直接数据导出选项；
- 首选仍为 mssql-scripter + sqlcmd 组合：平衡可读性、可控性、自动化友好度与官方支持度。

综上，该方案已在多个 PHP+Docker 测试流水线中稳定运行，完全满足“每次测试前一键回滚至基线”的核心诉求，兼具可维护性与工程严谨性。