[Docker & Linux ]: How to Run MSSQL Server (T-SQL) and Restore .bak File on Linux MacOS Pro M1/M2 Apple Silicon Using Terminals
[SDE notes | 軟件開發筆記]
sqlcmd | Docker | Linux | MacOS
Microsoft SQL Server (MSSQL) is a relational database management system developed by Microsoft that currently only supports running on Windows systems, and there is no official version available to run on Mac. The following are the steps to run MSSQL Server (T-SQL) using the terminal and restore a .bak file to MSSQL Server on your MacOS Pro M1/M2 Apple Silicon:
Microsoft SQL Server (MSSQL) 是由 Microsoft 開發的關係式資料庫管理系統,目前僅支援在 Windows 系統上執行,並沒有官方版本可以在 Mac 上運行。 以下是如何在你的 MacOS Pro M1/M2 Apple Silicon 上使用終端機運行 MSSQL Server(T-SQL)並將 .bak 檔案還原到 MSSQL Server 的方法:
1. Checking for Rosetta 2 Installation
According to the Official Docker Docs, Rosetta 2 is a translation process that allows users to run applications containing x86_64 instructions on Mac with Apple Silicon.
根據官方 Docker 文件,Rosetta 2 是一個翻譯過程,允許用戶在配備 Apple Silicon的 Mac 上運行包含 x86_64 指令的應用程序。
To check if Rosetta 2 is installed on your Mac with an Apple Silicon chip, you can use the Terminal.
- Open Terminal: You can find Terminal in the Utilities folder within your Applications folder, or you can search for it using Spotlight (Command + Space and type “Terminal”).
- Install Rosetta 2: In Terminal, type the command from the Official Docker Docs and press Enter:
要檢查您擁有 Apple Silicon 芯片的 Mac 上是否已安裝 Rosetta 2,您可以使用終端機。
- 打開終端機:您可以在應用程序資料夾內的實用程序資料夾中找到終端機,或者您可以使用 Spotlight (Command + Space,然後鍵入”終端機”)來搜索它。
- 安裝 Rosetta 2:在終端機中,鍵入官方 Docker 文件中的命令,然後按 Enter:
softwareupdate --install-rosetta
2. Installing sqlcmd using Homebrew
Check if you have installed the sqlcmd utility by running the following command, according to the Official Microsoft Docs:
根據官方 Microsoft 文件,通過運行以下命令來檢查是否已安裝了 sqlcmd 實用程序:
sqlcmd "-?"
If you haven’t installed it, there are two ways to install sqlcmd, according to the Official Microsoft Docs: sqlcmd (Go) and sqlcmd (ODBC)
根據官方 Microsoft 文件,如果您還沒有安裝 sqlcmd,有兩種方式可以安裝 sqlcmd:sqlcmd (Go) 和 sqlcmd (ODBC)。
sqlcmd (Go) is much easier to install. I recommend that one. (sqlcmd (Go) 安裝起來容易得多,我建議您選擇那個) If you also choose to install sqlcmd (Go), you can run the command below, according to the Official Microsoft Docs :
# Install Homebrew if not already installed.
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
# Install sqlcmd using Homebrew.
brew install sqlcmd
3. Installing Docker Desktop for Mac with Apple Silicon Support
Download and install Docker Desktop for Mac with Apple Silicon support from the Docker website.
從 Docker 網站下載並安裝支持 Apple Silicon 芯片的Docker Desktop for Mac。
After downloading the docker desktop and registering a docker account, tick the box “Use Rosetta for x86_64/amd64 emulation on Apple Silicon” in the setting of the docker desktop
在下載 Docker Desktop 並註冊 Docker 帳戶後,請在 Docker Desktop 的設置中勾選”在 Apple Silicon上使用 Rosetta 進行 x86_64/amd64 仿真”的選項。
4. Pulling and Running the SQL Server Linux Container Image
Choose the SQL Server image version (e.g., SQL Server versions: 2022, 2019, 2017) you want. Available versions can be found in the Official Docs:
請選擇您想要的 SQL Server 映像版本(例如 SQL Server 版本:2022、2019、2017)。可用版本可在官方文件中找到。
You can run the command below to pull the image mcr.microsoft.com/mssql/server in 2022-latest version
, according to the Official Doc:
根據官方文件, 您可以運行以下命令來拉取 image mcr.microsoft.com/mssql/server in 2022-latest version
sudo docker pull mcr.microsoft.com/mssql/server:2022-latest
To check which images you have successfully pulled, type the following command:
要檢查您已成功拉取了哪些映像,請輸入以下命令:
docker images
mcr.microsoft.com/mssql/server
` that you have pulled. (您應該會看到您提取的圖像)Then, set up and run the container for the image you have just pulled mcr.microsoft.com/mssql/server
, following the official documentation:
根據官方文件,接下來可以針對您剛剛拉取的 mcr.microsoft.com/mssql/server image,設置並運行container:
sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" \
-p 1433:1433 --name sql1 --hostname sql1 \
-d \
mcr.microsoft.com/mssql/server:2022-latest
- Replace
<YourStrong@Passw0rd>
with your own strong password that is at least eight characters and meets the password policy. - 將
<YourStrong@Passw0rd>
替換為密碼,該密碼至少需要八個字符並且符合密碼策略要求。
5. Creating a TSQL Database and Restoring .bak File to Docker MSSQL Container
First, use docker exec
to create a backup folder inside the SQL Server container:
首先,使用 docker exec
在 SQL Server 容器內創建一個備份資料夾:
sudo docker exec -it sql1 mkdir /var/opt/mssql/backup
Let me explain the various components of this command:
我來解釋一下這個命令中的各個組件:
- sudo: This command is executed with superuser privileges. On most Unix-like systems, you need to enter your computer’s password to allow permissions when performing administrative tasks. You can try ignoring it (i.e., only type
docker exec -it sql1 mkdir /var/opt/mssql/backup
), and you’ll know why you needsudo
+docker exec
. - sudo: 這個命令是以超級用戶權限執行的。在大多數類Unix系統上執行管理任務時,需要輸入您的電腦密碼,允許權限。你可以試著忽略它(i.e.,
docker exec -it sql1 mkdir /var/opt/mssql/backup
),你就會知道為什麼需要sudo
+docker exec
- docker exec: This command allows you to execute a command inside a running Docker container.
- docker exec: 這個命令允許你,在正在運行的Docker容器中,執行命令。
- it: These are two separate flags. The -i flag keeps STDIN open, allowing you to enter commands interactively. The -t flag allocates a pseudo-terminal, which is required for running interactive commands.
- -it: 這是兩個獨立的標誌。-i標誌保持STDIN開啟,允許你進行交互式輸入命令。-t標誌分配一個虛擬終端,這是運行交互式命令所需要的。
- sql1: This is the name or ID of the Docker container you want to execute the command in.
- sql1: 這是你想執行命令的Docker container的name或ID。
- mkdir: This means to create a new folder. You can try entering
mkdir homework
in thecd Document
directory, and you will have a new folder named “homework”. - mkdir: 意思是創建一個新folder。你可以試著在
cd Document
輸入mkdir homework
,您將有一個新資料夾名稱“homework”, - /var/opt/mssql/backup: This is the path where you want to create the new directory inside the container. It is typically used for storing SQL Server backup files.
- /var/opt/mssql/backup: 這是你想在容器中創建新目錄的路徑。它通常用於存儲SQL Server備份文件。
Then, copy your .bak file to /var/opt/mssql/backup
using docker cp
:
接著,使用docker cp
將你的 .bak 檔案複製到 /var/opt/mssql/backup
:
sudo docker cp /Users/Document/wwi.bak sql1:/var/opt/mssql/backup
Next, connect to the SQL Server(container) in docker by running the following command:
接著, 透過執行以下命令來連線到 Docker 中的 SQL Server (container):
sudo docker exec -it sql1 "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourStrong@Passw0rd>"
Finally, Create the database and restore the .bak file:
最後,創建數據庫並還原 .bak 文件:
- Create a new database named WWI123
- 創建WWI123數據庫
1> CREATE DATABASE WWI123
2> GO
- List out file names and paths inside the backup
- 列出backup中的文件名稱和路徑,可以使用以下命令:
1 > RESTORE FILELISTONLY
2 > FROM DISK = `/var/opt/mssql/backup/wwi.bak`
In /var/opt/mssql/backup/wwi.bak
, it contains:
- WWI_Primary
MSSQL\DATA\WideWorldImporters.mdf
- WWI_UserData
MSSQL\DATA\WideWorldImporters_UserData.ndf
- WWI_Log
MSSQL\DATA\WideWorldImporters.ldf
- WWI_InMemory_Data_1
MSSQL\DATA\WideWorldImporters_InMemory_Data_1
Thus, here is the logic and way of using the MOVE option to restore the backup file to a new database:
這是使用 MOVE 選項將備份文件還原到新數據庫的邏輯和方式:
1> RESTORE DATABASE WWI123
2> FROM DISK = '/var/opt/mssql/backup/WideWorldImporters-Full.bak'
3> WITH REPLACE, MOVE 'WWI_Primary' TO '/var/opt/mssql/data/WideWorldImporters.mdf',
4> MOVE 'WWI_UserData' TO '/var/opt/mssql/data/WideWorldImporters_UserData.ndf',
5> MOVE 'WWI_Log' TO '/var/opt/mssql/data/WWI123.ldf',
6> MOVE 'WWI_InMemory_Data_1' TO '/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1';
7> GO
Let me explain the various components of this command:
讓我解釋一下這個命令中的各個組件:
- RESTORE DATABASE WWI123: you want to restore the backup to the WWI123 database.
- RESTORE DATABASE WWI123: 指定要將備份還原到 WWI123 數據庫
FROM DISK = ‘/var/opt/mssql/backup/WideWorldImporters-Full.bak’ 指定備份文件的路徑。 - FROM DISK = ‘/var/opt/mssql/backup/WideWorldImporters-Full.bak’: specifies the path to the backup file
- FROM DISK = ‘/var/opt/mssql/backup/WideWorldImporters-Full.bak’: specifies the path to the backup file
- WITH REPLACE: 允許覆蓋任何同名的現有數據庫文件
- WITH REPLACE: allows you to overwrite any existing database files with the same name
- MOVE ‘WWI_Primary’ TO ‘/var/opt/mssql/data/WWI123.mdf’: moves the primary data file to the specified path and renames it to WWI123.mdf.
- MOVE ‘WWI_Primary’ TO ‘/var/opt/mssql/data/WWI123.mdf’: 將主數據文件移動到指定路徑並重命名為 WWI123.mdf
- MOVE ‘WWI_UserData’ TO ‘/var/opt/mssql/data/WWI123_UserData.ndf’: moves the user data file to the specified path and renames it to WWI123_UserData.ndf
- MOVE ‘WWI_UserData’ TO ‘/var/opt/mssql/data/WWI123_UserData.ndf’: 將用戶數據文件移動到指定路徑並重命名為 WWI123_UserData.ndf
- MOVE ‘WWI_Log’ TO ‘/var/opt/mssql/data/WWI123.ldf’: moves the log file to the specified path and renames it to WWI123.ldf
- MOVE ‘WWI_Log’ TO ‘/var/opt/mssql/data/WWI123.ldf’: 將日誌文件移動到指定路徑並重命名為 WWI123.ldf
- MOVE ‘WWI_InMemory_Data_1’ TO ‘/var/opt/mssql/data/WWI123_InMemory_Data_1’: moves the in-memory data file to the specified path and renames it to WWI123_InMemory_Data_1
- MOVE ‘WWI_InMemory_Data_1’ TO ‘/var/opt/mssql/data/WWI123_InMemory_Data_1’: 將內存中的數據文件移動到指定路徑並重命名為 WWI123_InMemory_Data_1
Open with Database Studio
Now, you may use your preferred Database studio to connect to the MSSQL server and open the restored database. Or connect your developing application (either an app or website) to the MSSQL server.
現在, 你可以使用你偏好的數據庫工作室來連接 MSSQL 服務器並開啟已還原的數據庫。或者將你正在開發的應用程式(無論是app還是網站)連接到 MSSQL 服務器。
Ending
Wish my software development engineer’s note helps you :)
希望我的軟件開發筆記能幫助到你 :)