[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 的方法：

I generated the image via DALL-E-3

Navigator

1. Checking Rosetta 2 Installation
2. Installing sqlcmd using Homebrew
3. Installing Docker Desktop for Mac
4. Pulling and Running the MSSQL Server Container
5. Creating TSQL Database and Restoring .bak File
6. Open with Database Studio
7. Ending

I generated via DALL-E-3

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 指令的應用程序。

the Official Docker Docs for Mac user

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

This command attempts to install Rosetta 2. If Rosetta 2 is already installed, the system will report that Rosetta is already installed, and no further action will be taken. If it’s not installed, this command will install it after you agree to the license (the — agree-to-license flag automatically agrees to the license for you). 此命令嘗試安裝 Rosetta 2。如果 Rosetta 2 已經安裝,系統將報告 Rosetta 已經安裝,並且不會採取進一步行動。如果尚未安裝,此命令將在您同意許可證後安裝它( — agree-to-license 旗標會自動為您同意許可證)。

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 installed, the output will be similar to the example above in the Official Microsoft Docs. If not, it will show an error message. (如果已安裝 sqlcmd,輸出將類似於上面 Official Microsoft Docs 中的範例。如果尚未安裝,它將顯示錯誤訊息。)

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

sqlcmd (Go) is installed successfully (sqlcmd（Go）安裝成功)

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 仿真”的選項。

In the Docker Desktop settings, enable “Use Rosetta for x86_64/amd64 emulation on Apple Silicon” to accelerate x86_64/amd64 binary emulation on Apple Silicon.(在 Docker Desktop 設定中,啟用”在 Apple Silicon 上使用 Rosetta 進行 x86_64/amd64 模擬”選項,以加速在 Apple Silicon 上的 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)。可用版本可在官方文件中找到。

Official Doc

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

For example(像這樣)

To check which images you have successfully pulled, type the following command:

要檢查您已成功拉取了哪些映像,請輸入以下命令:

docker images

You should see the image `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> 替換為密碼,該密碼至少需要八個字符並且符合密碼策略要求。

After running the command, just ignore the warning if you encounter one and run docker ps to see if it ran successfully. When you see “Up” in the Status, it means it ran successfully🎉. (在執行命令後,如果遇到警告,可以忽略並執行 docker ps 來查看是否運行成功。如果狀態顯示為”Up”(正在運行),表示運行成功🎉)

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 容器內創建一個備份資料夾:

official doc

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 need sudo + 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 the cd 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

If you successfully copied the .bak file to the specified path, it will show something like this(如果你成功將 .bak 檔案複製到指定的路徑,它會顯示類似這樣)

Next, connect to the SQL Server(container) in docker by running the following command:

接著, 透過執行以下命令來連線到 Docker 中的 SQL Server (container):

official doc

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`

Output from that sqlcmd

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

Restore successful 成功🎉

Congrats!

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 服務器。

I used the popSQL Studio GUI to connect to the MSSQL Server and open the newly created WWI123 database(我使用了 popSQL Studio GUI 介面來連接 MSSQL Server 並開啟了新建立的 WWI123 數據庫)

Ending

Wish my software development engineer’s note helps you :)

希望我的軟件開發筆記能幫助到你 :)