如何创建含备份文件的SQL Server Docker镜像，使衍生容器自带该文件？

I’ve helped a few teams set this up before, so here’s a straightforward, production-ready approach to creating a SQL Server Docker image that includes a default backup file—every container spun up from it will have that backup ready to use:

1. Build a Custom Image with the Backup Embedded #

The core idea is to extend the official SQL Server Docker image and copy your backup file directly into it. Here’s how to pull it off:

Step 1: Create Your Dockerfile #

Drop this in a new folder next to your backup file (let’s say my-db-backup.bak):

# Use the official SQL Server 2022 image (swap to your preferred version)
FROM mcr.microsoft.com/mssql/server:2022-latest

# Required environment variables for SQL Server to initialize
ENV ACCEPT_EULA=Y
# WARNING: Don't hardcode this in production—use runtime env vars instead!
ENV SA_PASSWORD=YourStrong!InitialPassw0rd

# Create a dedicated backup directory in the container
RUN mkdir -p /var/opt/mssql/backup

# Copy your local backup into the container's backup folder
COPY ./my-db-backup.bak /var/opt/mssql/backup/

# Fix permissions: SQL Server runs as the 'mssql' user, so it needs access to the backup
RUN chown -R mssql:mssql /var/opt/mssql/backup

Step 2: Build the Custom Image #

Run this command in the same directory as your Dockerfile and backup:

docker build -t sql-server-with-default-backup .

2. Optional (But Useful): Auto-Restore the Backup on Container Startup #

If you want the backup to automatically restore when the container first starts (instead of doing it manually), you can add an initialization script. SQL Server’s Docker image automatically executes .sql or .sh scripts in the /opt/mssql/scripts/ directory during the first run.

Step 1: Write the Restore Script #

Create a file named restore-db.sql with this content (replace all placeholders with your actual database details):

RESTORE DATABASE MyTargetDatabaseName
FROM DISK = '/var/opt/mssql/backup/my-db-backup.bak'
WITH REPLACE,
-- Map the source data/log files to the container's data directory
MOVE 'MySourceDatabase_Data' TO '/var/opt/mssql/data/MyTargetDatabaseName.mdf',
MOVE 'MySourceDatabase_Log' TO '/var/opt/mssql/data/MyTargetDatabaseName.ldf';

Step 2: Add the Script to Your Dockerfile #

Update your Dockerfile to copy the script into the SQL Server initialization directory:

# ... (keep all existing lines from the first Dockerfile)

# Copy the auto-restore script to the init directory
COPY ./restore-db.sql /opt/mssql/scripts/

Re-build the image, and now any container started from it will automatically restore the database on its first launch.

Critical Best Practices & Troubleshooting Tips #

• Permissions Are Non-Negotiable: Always run chown -R mssql:mssql on the backup directory. If you skip this, SQL Server won’t be able to read the backup file, and you’ll get permission errors.
• Secure Your SA Password: Never hardcode SA_PASSWORD in your Dockerfile for production. Instead, pass it as an environment variable when starting the container:

  docker run -d -p 1433:1433 -e SA_PASSWORD=YourSecure!ProductionPassw0rd sql-server-with-default-backup
  

• Watch Image Size: Embedding a large backup will increase your image size. If this is a problem, you can use a multi-stage build to copy the backup without adding extra bloat, but for most use cases, the simple approach works perfectly.
• Validate the Setup: After starting a container, connect via SSMS or sqlcmd to confirm:
  • The backup file exists at /var/opt/mssql/backup/my-db-backup.bak
  • The database is restored (if you added the auto-restore script)

内容的提问来源于stack exchange，提问作者lucas.bomfonti