howto-docker-mssql [JM Knowledge Base]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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*