replace_container_commands.md

Replace container while retaining volume

This script is intended to demonstrate how a Docker volume can be # reused by a new container. The image we'll use is SQL Server 2017

The scripts are baed on the official Microsoft documentation

Create a new container and volume

Create a new container from the mssql-server-linux image

docker container run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=MyStrong!Passw0rd' \
   --name 'sql1' -p 1433:1433 \
   -v sql1data:/var/opt/mssql \
   -d microsoft/mssql-server-linux:2017-latest

Confirm the container is running

$ docker container ls
CONTAINER ID        IMAGE                                      COMMAND                  CREATED             STATUS              PORTS                    NAMES
8d1e5fdca39e        microsoft/mssql-server-linux:2017-latest   "/opt/mssql/bin/sqls…"   2 seconds ago       Up 4 seconds        0.0.0.0:1433->1433/tcp   sql1

Confirm the volume is created

$ docker volume ls
DRIVER              VOLUME NAME
local               sql1data

OPTIONALLY: get the example backup file from the Microsoft Github repository

curl -L -o wwi.bak 'https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak'

Copy the backup file into the container

docker container exec -it sql1 mkdir /var/opt/mssql/backup
docker cp wwi.bak sql1:/var/opt/mssql/backup

List out logical file names and paths inside the container

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost \
   -U SA -P 'MyStrong!Passw0rd' \
   -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/wwi.bak"' \
   | tr -s ' ' | cut -d ' ' -f 1-2

LogicalName   PhysicalName
------------------------------------------
WWI_Primary   D:\Data\WideWorldImporters.mdf
WWI_UserData   D:\Data\WideWorldImporters_UserData.ndf
WWI_Log   E:\Log\WideWorldImporters.ldf
WWI_InMemory_Data_1   D:\Data\WideWorldImporters_InMemory_Data_1

Restore the database

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'MyStrong!Passw0rd' \
   -Q 'RESTORE DATABASE WideWorldImporters FROM DISK = "/var/opt/mssql/backup/wwi.bak" WITH MOVE "WWI_Primary" TO "/var/opt/mssql/data/WideWorldImporters.mdf", MOVE "WWI_UserData" TO "/var/opt/mssql/data/WideWorldImporters_userdata.ndf", MOVE "WWI_Log" TO "/var/opt/mssql/data/WideWorldImporters.ldf", MOVE "WWI_InMemory_Data_1" TO "/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1"'

Processed 1464 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 53096 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
Processed 33 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
Processed 3862 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Converting database 'WideWorldImporters' from version 852 to the current version 869.
Database 'WideWorldImporters' running the upgrade step from version 852 to version 853.
Database 'WideWorldImporters' running the upgrade step from version 853 to version 854.
Database 'WideWorldImporters' running the upgrade step from version 854 to version 855.
Database 'WideWorldImporters' running the upgrade step from version 855 to version 856.
Database 'WideWorldImporters' running the upgrade step from version 856 to version 857.
Database 'WideWorldImporters' running the upgrade step from version 857 to version 858.
Database 'WideWorldImporters' running the upgrade step from version 858 to version 859.
Database 'WideWorldImporters' running the upgrade step from version 859 to version 860.
Database 'WideWorldImporters' running the upgrade step from version 860 to version 861.
Database 'WideWorldImporters' running the upgrade step from version 861 to version 862.
Database 'WideWorldImporters' running the upgrade step from version 862 to version 863.
Database 'WideWorldImporters' running the upgrade step from version 863 to version 864.
Database 'WideWorldImporters' running the upgrade step from version 864 to version 865.
Database 'WideWorldImporters' running the upgrade step from version 865 to version 866.
Database 'WideWorldImporters' running the upgrade step from version 866 to version 867.
Database 'WideWorldImporters' running the upgrade step from version 867 to version 868.
Database 'WideWorldImporters' running the upgrade step from version 868 to version 869.
RESTORE DATABASE successfully processed 58455 pages in 2.690 seconds (169.767 MB/sec).

Delete the container we just created

At this point we have a running container with the database setup. The next step is to stop and delete the current container while maintaining the volume, so it can be reused later

$ docker container rm --force sql1
sql1

Verify the volume still exists

$ docker volume ls
DRIVER              VOLUME NAME
local               sql1data

Create a new container that uses the existing volume

$ docker container run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=MyStrong!Passw0rd' \
   --name 'sql2' -p 1433:1433 \
   -v sql1data:/var/opt/mssql \
   -d microsoft/mssql-server-linux:2017-latest
f127e0a4cd45a42deaa625c6b5a2eb7e55134d9be1b64f39e810b0094e884489

Now a new container named sql2 is created and it is using volume sql1data

To verify we are using the previously created volume, we'll run a simple query that should return some rows

$ docker exec -it sql2 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'MyStrong!Passw0rd' \
   -Q 'SELECT TOP 10 StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems ORDER BY StockItemID'
StockItemID StockItemName
----------- ----------------------------------------------------------------------------------------------------
          1 USB missile launcher (Green)
          2 USB rocket launcher (Gray)
          3 Office cube periscope (Black)
          4 USB food flash drive - sushi roll
          5 USB food flash drive - hamburger
          6 USB food flash drive - hot dog
          7 USB food flash drive - pizza slice
          8 USB food flash drive - dim sum 10 drive variety pack
          9 USB food flash drive - banana
         10 USB food flash drive - chocolate bar

(10 rows affected)