How to run MSSQL on Linux using Docker

# Pull MSSQL image (version 2017-CU20-ubuntu-16.04) docker pull mcr.microsoft.com/mssql/server:2017-CU20-ubuntu-16.04

# see list of image in current local repo docker image ls

# see list of container in current local repo (both runnning and exited) docker container ls -a

# see list of volume in current local repo docker volume ls

# Start container with some host filesystem mounted to the container : docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=Abcd1234*' -p 14330:1433 \ –name mssql01 \ -v ~/codespike/mssql-docker/mssql01/data:/var/opt/mssql/data \ -v ~/codespike/mssql-docker/mssql01/log:/var/opt/mssql/log \ -v ~/codespike/mssql-docker/mssql01/secrets:/var/opt/mssql/secrets \ -d mcr.microsoft.com/mssql/server:2017-CU20-ubuntu-16.04

# Stop the container docker stop mssql01

to restore db : 1. copy the *.bak file into the container let say we have folder /var/opt/mssql/junction as ada transit spot

docker cp NUTRIFOOD_ISM.bak mssql01:/var/opt/mssql/junction

2. connect using squirrel from host, login as the intended owner of the database (nutrifood) 3. run the following query to find out the file inside the *.bak

RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/junction/NUTRIFOOD_ISM.bak'

will give output something like ISM_NUTRIFOOD C:\CINOVASI\SQLSERVER_DB_TEST\ISM_NUTRIFOOD.mdf ISM_NUTRIFOOD_log C:\CINOVASI\SQLSERVER_DB_TEST\ISM_NUTRIFOOD_1.ldf

4. execute restore by specifying also the new location for the .mdf and .ldf files ** don't create empty database “ism01”, otherwise the script will fail !!

RESTORE DATABASE ism01 FROM DISK = '/var/opt/mssql/junction/NUTRIFOOD_ISM.bak' WITH MOVE 'ISM_NUTRIFOOD' TO '/var/opt/mssql/data/ism01.mdf', MOVE 'ISM_NUTRIFOOD_log' TO '/var/opt/mssql/data/ism01_log.ldf'

5. still using squirrel, connect using sa

use ism01;

create user nutrifood for login nutrifood;

6. utk kasus multiple schema (cinovasi01), munculkan dulu daftar seluruh schema

select * from sys.schemas;

lalu grant access ke role db_owner

EXEC sp_addrolemember 'db_owner' , 'nutrifood'

To get into currently running container :

docker exec -it <container name> <command>

ex: docker exec -it mssql01 /bin/bash

to run mssql tools command line, go inside the container bash shell, then run : /opt/mssql-tools/bin/sqlcmd -U sa -P Abcd1234*