5

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:
Steve Lam
  • 979
  • 1
  • 17
  • 40

1 Answers1

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
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • But how can I write in dockerfile or docker-compose file? or I just write the above command in there? – Steve Lam Mar 26 '19 at 05:50
  • 1
    @SteveLam, I added a `dockerfile` example. – Dan Guzman Mar 26 '19 at 11:53
  • Thank @Dan, it works. Could you let me know how do you know call CMD and also "& /opt/mssql/bin/sqlservr"? – Steve Lam Mar 27 '19 at 02:20
  • 2
    @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. – Dan Guzman Mar 27 '19 at 02:26
  • 1
    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. – somethingRandom Jan 16 '23 at 09:46