接續上一篇【SQL Server on Container起手式】，這篇來實作Linux主機與container之間(或container與container之間)相互備份/還原SQL Server資料庫，以及資料磁碟區容器(Docker Volume)的初步應用。
為了方便對照，再次說明下圖是對部門內部分享SQL Server on Linux時的實作環境，後來有另外安裝一台CentOS 7.3(伺服器名稱為sqllnx4，IP為192.168.56.134)來練習Container。安裝新的Linux後，記得先執行 #yum update -y ，以更新所有使用yum下載安裝的套件，再繼續docker套件的安裝。
--實作開始(在sqllnx4上操作)
--(1) 提取 SQL Server 2017 Linux 容器映像
# docker pull mcr.microsoft.com/mssql/server:2017-latest
# docker images
--建立container，並利用參數"-v"來建立一個名為 "sql1data" 的"資料磁碟區容器(Docker Volume)"。 我們要用來保存SQL Server所建立的資料。
--後面我們會解釋參數"-v"的功用，這裡先照著做
# docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=xxxxxxxx' \
--name 'sql1' -p 1401:1433 \
-v sql1data:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2017-latest
# docker ps
# docker ps -a
--(2) 變更sa密碼
--建立您的 SQL Server 容器之後，在"容器中"執行 echo $MSSQL_SA_PASSWORD，即可探索您指定的 MSSQL_SA_PASSWORD 環境變數。
# docker exec -it sql1 echo $MSSQL_SA_PASSWORD --會找不到，這種直接進入容器執行指令的方式，無法取到容器內的變數值
# docker exec -it sql1 "bash"
root@28f9e2b5afde:/# echo $MSSQL_SA_PASSWORD --會成功，改用這種方式才抓得到容器內的變數值
root@28f9e2b5afde:/# exit
--基於安全性考量，請變更您的 SA 密碼。
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
-S localhost -U sa -P 舊密碼 \
-Q 'ALTER LOGIN sa WITH PASSWORD="新密碼"'
--(3) 使用Host主機的SSMS連接至192.168.56.134,1401，觀察SSMS，先確認此時 容器"sql1" 尚未建立資料庫
--(4) 在容器中還原資料庫
--先在Host主機以SSMS連上容器192.168.56.134,1401，然後建立一個"test"資料庫
--在容器中建立備份資料夾"backup"
# docker exec -it sql1 mkdir /var/opt/mssql/backup --建立"backup"目錄
# docker exec -it sql1 ls -l /var/opt/mssql/ --檢視"backup"目錄的owner及group
# docker exec -it sql1 ls -l /var/opt/mssql/data --檢視"data"目錄內容
--為了簡化操作流程，接著執行"test"資料庫的備份，假裝是從其他Linux主機copy過來的
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA -P 'xxxxxxxx' \
-Q 'backup database [test] to disk = "/var/opt/mssql/backup/test.bak"'
=================================================
Processed 304 pages for database 'test', file 'test' on file 1.
Processed 3 pages for database 'test', file 'test_log' on file 1.
BACKUP DATABASE successfully processed 307 pages in 0.048 seconds (49.967 MB/sec).
=================================================
--檢查邏輯檔案名稱及實體檔案名稱，[tr -s ' ']指令是取代掉重複的字元，[cut -d ' ' -f 1-2]指令是以空白字元區分欄位，並只列出第一及第二的欄位 (注意: 因語法後面有用管線"|"，所以sqlcmd指令要有-P參數才不會出現錯誤)
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost \
-U sa -P 'xxxxxxxx' \
-Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/test.bak"' \
| tr -s ' ' | cut -d ' ' -f 1-2
=================================================
LogicalName PhysicalName
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test /var/opt/mssql/data/test.mdf
test_log /var/opt/mssql/data/test_log.ldf
(2 rows
=================================================
--還原test.bak，並將資料庫更名為test2，以可利用Host主機的SSMS來查看資料庫test及test2(注意: 在RESTORE DATABASE語法中，DB邏輯名稱不能修改，否則還原DB會錯誤)
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA -P 'xxxxxxxx' \
-Q 'RESTORE DATABASE [test2] FROM DISK = "/var/opt/mssql/backup/test.bak" WITH MOVE "test" TO "/var/opt/mssql/data/test2.mdf", MOVE "test_log" TO "/var/opt/mssql/data/test2_log.ldf"'
=================================================
Processed 304 pages for database 'test2', file 'test' on file 1.
Processed 3 pages for database 'test2', file 'test_log' on file 1.
RESTORE DATABASE successfully processed 307 pages in 0.051 seconds (47.028 MB/sec).
=================================================
# docker exec -it sql1 ls -l /var/opt/mssql/data --檢查"test2"資料庫檔案是否存在
--(5) 建立資料庫物件，來驗證還原的資料庫(建議可以連上SSMS上直接操作，比較方便)
--建立資料表"tb_1"，並寫入兩筆資料，以SSMS查詢資料
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA -P 'xxxxxxxx' \
-Q 'create table [test2].[dbo].[tb_1] (id int,name varchar(10));insert into [test2].[dbo].[tb_1] values(1,"Mary"),(2,"John");'
# docker exec -it sql1 ls -l /var/opt/mssql/data/
--(6) 在容器中建立新的備份"test2.bak"，備份路徑為"/var/opt/mssql/backup"
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA -P 'xxxxxxxx' \
-Q "BACKUP DATABASE [test2] TO DISK = N'/var/opt/mssql/backup/test2.bak'"
# docker exec -it sql1 ls -l /var/opt/mssql/backup --檢查備份檔"test2.bak"
--接著，就可以利用"docker cp"指令，將容器中的備份檔案，自行複製到Linux主機上來執行還原，我們將在步驟(8)說明
# cd ~
# docker cp sql1:/var/opt/mssql/backup/test2.bak test2.bak --將備份檔從容器複製到外部的Linux主機上
# ls -l test2.bak
--(7) 使用保存的資料--使用"資料磁碟區"(此範例會先刪除容器"sql1"，再建立新的容器"sql2")
--除了使用資料庫備份來保護您的資料之外，您也可以使用"資料磁碟區容器"。 所以，我們一開始已使用 -v sql1data:/var/opt/mssql 參數建立 sql1 容器。
--sql1data 資料磁碟區容器即使在該容器被移除後，也會保存 /var/opt/mssql 資料。 下列步驟會將 sql1 容器完全移除，然後使用保存的資料來建立新容器 sql2。
# docker ps --檢視正在執行的容器
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
00b76195a200 mcr.microsoft.com/mssql/server:2017-latest "/opt/mssql/bin/no..." 28 hours ago Up 4 hours 0.0.0.0:1401->1433/tcp sql1
# docker stop sql1 --停掉容器
sql1
# docker rm sql1 --移除容器
sql1
# docker volume ls --檢視資料磁碟區"sql1data"依然存在
--建立新容器 sql2，然後重複使用 sql1data 資料磁碟區容器(對應的IP可以改變，sql1data資料還是會存在)
# docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=xxxxxxxx' \
> --name 'sql2' -e 'MSSQL_PID=Developer' -p 1401:1433 \
> -v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2017-latest
126fb94b9ecfc4116bf82dc5b9cd9a7dd493e2702b2c830a1d9495787c1a1f63
--[注意]: SA 密碼不是您為 容器"sql2" 指定的密碼 MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>，而是原本 容器"sql1" 中所設定的密碼。
# docker exec -it sql2 /opt/mssql-tools/bin/sqlcmd \
> -S localhost -U SA -P 'xxxxxxxx' \
> -Q 'select * from test2.dbo.tb_1;' --可以查詢到之前在容器"sql1"建立的資料表物件及資料
id name
----------- ----------
1 Mary
2 John
(2 rows affected)
步驟(7)的概念，大概如下圖所示
--(8) 最後，我們來完成步驟(6)未完成的部分，也就是在Linux主機還原資料庫
--請啟用sqllnx4(192.168.56.134)上的SQL Server on Linux，準備將test2.bak在sqllnx4執行資料庫還原
# systemctl status mssql-server
# systemctl start mssql-server
# systemctl status mssql-server
# netstat -tulpn
--將"text2.bak"複製到SQL Server on Linux(sqllnx4)的備份目錄
# cd ~
# cp test2.bak /var/opt/mssql/backup/test2.bak
# ll /var/opt/mssql/backup
--因為無法用SSMS來還原資料庫，所以還是得使用sqlcmd指令
--需修正備份檔案權限，否則執行時可能會出現與權限相關的錯誤訊息
# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P xxxxxxxx -Q 'restore filelistonly from disk = "/var/opt/mssql/backup/test2.bak"' --發生錯誤
----------------------------------------------------------------------
Msg 3201, Level 16, State 2, Server sqllnx4, Line 1
無法開啟備份裝置 '/var/opt/mssql/backup/test2.bak'。作業系統錯誤 5(存取被拒。)。
Msg 3013, Level 16, State 1, Server sqllnx4, Line 1
RESTORE FILELIST 正在異常結束。
----------------------------------------------------------------------
# chown mssql:mssql /var/opt/mssql/backup/test2.bak --修正備份檔案權限
--重新執行
# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P xxxxxxxx -Q 'restore filelistonly from disk = "/var/opt/mssql/backup/test2.bak"' | tr -s ' ' | cut -d ' ' -f 1-2 --檢視備份檔資訊
# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P xxxxxxxx -Q 'restore database [test2] from disk = "/var/opt/mssql/backup/test2.bak" with move "test" to "/var/opt/mssql/data/test2.mdf",move "test_log" to "/var/opt/mssql/data/test2_log.ldf"' --執行資料庫還原
----------------------------------------------------------------------
已處理資料庫 'test2' 的 352 頁，檔案 1 上的檔案 'test'。
已處理資料庫 'test2' 的 6 頁，檔案 1 上的檔案 'test_log'。
RESTORE DATABASE 已於 0.047 秒內成功處理了 358 頁 (59.424 MB/sec)。
----------------------------------------------------------------------
# ll /var/opt/mssql/data/ --查看資料庫檔案
--使用SSMS來查看sqllnx4(192.168.56.134)上的資料庫"test2"
--(10) 測試完畢，刪除相關檔案
--移除"容器"
# docker stop sql2
# docker rm sql2
-移除"資料磁碟區"
# docker volume ls
# docker volume rm sql1data --"sql1data"為"資料磁碟區名稱"
# docker volume ls
--透過SSMS刪除sqllnx4(192.168.56.134)資料庫"test2"
# ll /var/opt/mssql/data/ --資料庫"test2"的檔案被刪除了
Jay Huang