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:

Web
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:

Dan Guzman · Accepted Answer · 2019-03-26 11:53:22Z

8

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

edited Mar 26, 2019 at 11:53

answered Mar 25, 2019 at 12:00

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Steve Lam Over a year ago

But how can I write in dockerfile or docker-compose file? or I just write the above command in there?

Dan Guzman Over a year ago

@SteveLam, I added a dockerfile example.

Steve Lam Over a year ago

Thank @Dan, it works. Could you let me know how do you know call CMD and also "& /opt/mssql/bin/sqlservr"?

Dan Guzman Over a year ago

@SteveLam, the example in the question link shows this method. They key is that the sqlserver command needs to be last one to keep the container running, whilst the preceding ones run the background.

somethingRandom Over a year ago

This doesn't work; I get an Server is not found or not accessible. error. Before running anything with sqlcmd you have to make sure that the SQL server is actually running.

Collectives™ on Stack Overflow

docker-compose restore database in sql-server-linux

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related