docker-compose restore database in sql-server-linux

Question

I'm following https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-2017 to config and run standalone sqlserver container in macos with persistent data successfully. Now I want to create a docker-compose file which contains 2 things:

1. Web
2. Database (will copy bak file from a host into the container and restore)

How do I achieve (2)?

My docker-compose file:

version: "3"
services:
    web:
        build: 
            context: ./webapi
            dockerfile: Dockerfile
        ports:
            - "4010:80"
        environment: 
            - ASPNETCORE_ENVIRONMENT=Development
        depends_on:
            - db
    db:
        image: "mcr.microsoft.com/mssql/server:2017-latest"
        ports:
            - "4009:1433"
        volumes:
            - "sqlvolume:/var/opt/mssql"
        environment:
            SA_PASSWORD: "<password>"
            ACCEPT_EULA: "Y"
    redis:
        image: "redis:alpine"
volumes:
    sqlvolume:

Answer 1

There are a number of different ways to accomplish the restore task. If your backup is on the mounted volume, you could restore using sqlcmd like:

run docker exec -it db /opt/mssql-tools/bin/sqlcmd -Usa -P<password> -Q"RESTORE DATABASE YourDatabase FROM DISK='/var/opt/mssql/backup/your-backup.bak';"

You could similarly copy the backup into the container using docker cp and restore from there.

If your objective is to restore the database from the mounted volume each time the container starts, you can build a custom SQL Server image with the RESTORE command (which could be encapsulated in a shell script). Below is a dockerfile example:

FROM mcr.microsoft.com/mssql/server:2017-latest

CMD /opt/mssql-tools/bin/sqlcmd -U 'sa' -P $MSSQL_SA_PASSWORD -Q"RESTORE DATABASE YourDatabase FROM DISK='/var/opt/mssql/backup/your-backup.bak';" & /opt/mssql/bin/sqlservr