Mssql-docker: Using Volumes with 2019

[dave-fl]

What is the suggested way to use a volume at path: /var/opt/mssql/. Since 2019 operates as a non-root user by default I am getting the following error:

2019-12-07 21:52:16.89 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2019-12-07 21:52:16.94 Server      ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf':  2(The system cannot find the file specified.)
ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)

Compose file is like so:

version: "3.7"
services:
  sqlserver_2019:
    image: mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
    hostname: sqlserver_2019
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrong!Passw0rd
    volumes:
      - type: volume
        source: data
        target: /var/opt/mssql/data
volumes:
  data:
    external:
      name: sqlserver_2019

[bguessaoui]

Hello,

same issue here,

Any news?

[alemhnan]

Same here

[Henkolicious]

Same issue here.

[dbamaster]

What is the suggested way to use a volume at path: /var/opt/mssql/. Since 2019 operates as a non-root user by default I am getting the following error:

2019-12-07 21:52:16.89 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2019-12-07 21:52:16.94 Server      ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf':  2(The system cannot find the file specified.)
ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)

Compose file is like so:

version: "3.7"
services:
  sqlserver_2019:
    image: mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
    hostname: sqlserver_2019
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrong!Passw0rd
    volumes:
      - type: volume
        source: data
        target: /var/opt/mssql/data
volumes:
  data:
    external:
      name: sqlserver_2019

Hello there,

I think there was a little problem when declaring the volume name at the end. Here you have the YML syntax:

version: "3.7"
services:
  sqlserver_2019:
    image: mcr.microsoft.com/mssql/server:2019-CU2-ubuntu-16.04
    hostname: sqlserver_2019
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourStrong!Passw0rd
    volumes:
      - type: volume
        source: data
        target: /var/opt/mssql
volumes:
  data:

Testing the volume by creating a new database called "VolTest":

[dba mastery] $  sqlcmd -S localhost -USA -P'YourStrong!Passw0rd'
1> create database VolTest;
2> go
1> select name from sys.databases;
2> go
name                                                                                                                            
------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
VolTest                                                                                                                         

A few points to remark here:

• You don't need to use docker-compose to create a single container, it works best for a compose solution :)
• The /var/opt/mssql is the path where both MDF and LDF files are created, you can create one volume for each one if needed.
• I used the latest SQL Server 2019 image for Ubuntu 2019-CU2-ubuntu-16.04. You can check the complete tab list here.

Cheers,

[darkguy2008]

What if you don't want to use a named volume? I don't really like Docker telling me where my data should be, I like my data to be in the same folder where I have the project. So, in my case:

docker -v
Docker version 19.03.11, build 42e35e61f3

docker-compose.yml:

version: '3.6'
services:

  db:
    restart: always
    image: mcr.microsoft.com/mssql/server:latest
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=BLAH
      - MSSQL_PID=Express
    ports:
        - 1433:1433
    volumes:
      - ./data:/var/opt/mssql/data
    logging:
      driver: "json-file"
      options:
        max-size: "500k"
        max-file: "5"

I get this same dreaded error, so I can't start up my DB:

db_1  | 2020-06-02 02:18:32.94 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2020-06-02 02:18:33.14 Server      ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf':  2(The system cannot find the file specified.)
db_1  | ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)

What is the solution then?

[darkguy2008]

Well I found the solution: Run as root -> https://github.com/microsoft/mssql-docker/issues/13#issuecomment-641904197

[marcozm]

I found the solution in spanish
https://www.eiximenis.dev/posts/2020-06-26-sql-server-docker-no-se-ejecuta-en-root/

[mrjamiebowman]

Okay, so Marcozm's link worked for me.

Basically, I have a Dockerfile that changes the user to root by running this command "USER root" and now everything works fine.

By default, I believe it runs under the user "mssql".