# 介紹好用工具：mssql-scripter (自動將完整資料庫匯出成 T-SQL 的神器)

遷移 SQL Server 資料庫是一件感覺好像很簡單，但做起來可能會很麻煩的工作。其實透過 SSMS 就可以做到很多種不同的資料庫遷移方法，但我想找一個能夠更方便的方式，透過命令列的方式自動化整個遷移過程，最近我找到了這個 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 工具，完全可以符合我的需求。這篇文章我就來說說我的應用情境，以及 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 工具的使用方式。

### SQL Server 資料庫的遷移方法

要遷移 SQL Server 資料庫到另一台電腦有很多種方法，我所知道的就有以下三種：

1.  透過 **資料層應用程式** (DAC) ([Data-tier Applications](https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686)) 遷移資料庫
    
    你可以透過 [SSMS](https://learn.microsoft.com/zh-tw/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686) (SQL Server Management Studio) 或 [DACFx](https://aka.ms/dacfx-msi) 的 `SqlPackage` 工具，將要遷移的資料庫擷取或匯出成 `*.dacpac` 或 `*.bacpac` 檔案，再從目標 SQL Server 發行/匯入資料庫。
    
    你只要下載 [DACFx](https://aka.ms/dacfx-msi) 安裝之後，就可以透過 `SqlPackage.exe` 對 SQL Server 資料庫進行匯出/匯入/擷取/發行/編寫/報告等操作，一般來說對資料庫進行 CI/CD 幾乎都會使用這套工具，你當然也可以運用在資料庫遷移的目的上。
    
    參考文章: [使用 SqlPackage 對 SQL Server 資料庫進行匯出/匯入/擷取/發行/編寫/報告](/post/2021/10/14/Using-SqlPackage-to-Extract-DeployReport-DriftReport-Publish-Script-Export-Import)
    
2.  透過 SQL Server 的**資料庫備份/還原**來遷移資料庫
    
    你可以透過 [SSMS](https://learn.microsoft.com/zh-tw/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686) (SQL Server Management Studio)、[osql](https://learn.microsoft.com/zh-tw/sql/tools/osql-utility?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686)、[sqlcmd](https://learn.microsoft.com/zh-tw/sql/tools/sqlcmd-utility?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686) 或 [mssql-cli](https://learn.microsoft.com/zh-tw/sql/tools/mssql-cli?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686) 將要遷移的資料庫備份成 `*.bak` 檔，再從目標 SQL Server 還原資料庫。
    
    參考文件：[BACKUP (Transact-SQL)](https://learn.microsoft.com/zh-tw/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686)、[RESTORE (Transact-SQL)](https://learn.microsoft.com/zh-tw/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686)
    
3.  透過**產生資料庫完整的 T-SQL 命令**來遷移資料庫
    
    你可以透過 [SSMS](https://learn.microsoft.com/zh-tw/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686) (SQL Server Management Studio) 或 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 來匯出完整的 T-SQL 命令。
    
    這套 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 命令列工具相當強大，參數非常多，幾乎可以完整取代 SSMS 中的 **Generate Scripts Wizard** 功能。
    
    這種遷移資料庫的方式最具彈性，因為你會得到純文字的 T-SQL 命令腳本，無論複製到哪一台 SQL Server 都可以很輕易的執行 T-SQL，甚至可以手動微調過後執行，而且通常不太會遇到版本相容性的問題。
    

### 為何我要透過產生資料庫完整的 T-SQL 命令來遷移資料庫

前幾天我在 [Will 保哥的技術交流中心 LINE 社群](https://line.me/ti/g2/Uhq2NM0bA6j3C5O0XBAVnm9CGMxJZgY_UMBsPA) 跟幾位群友聊到，我該如何透過命令列工具產生 T-SQL 命令腳本，做到類似 SSMS 的 **Generate Scripts Wizard** 功能，方便我進行自動化的資料庫遷移動作。但是在交流的過程中，一直被建議使用 **資料層應用程式** (DAC) 的方式來進行資料庫遷移，但我有實測過，透過 DAC 的方式並不適用於我所遭遇的狀況，以下我就概要說明一下無法使用 DAC 遷移資料庫的原因。

首先，我用的是 Azure SQL Database 且我的 SQL Server 有啟用 Failover groups 功能，因此資料庫本身會自動管理所有自動複寫與容錯移轉能力，在這樣的架構底下，資料庫不允許被刪除後建立。而移轉資料庫的三種方法中，第一種 **資料層應用程式** (DAC) 在匯入含資料的 BACPAC 檔案時，只能建立全新的資料庫才能匯入，不能針對同一個資料庫進行更新；第二種的方法在資料庫備份的時候，會整個覆蓋資料庫，一樣不適用於有 Replications 的情況；第三種透過 T-SQL 的方式，我就很方便能夠先 `DROP` 刪除現有物件，再透過 `CREATE` 重建物件，並透過 `INSERT` 寫入資料。

事實上，我們的部署環境有兩份資料庫（**生產環境**與**業演環境**），若加上開發環境就有三份資料庫，我們需要定期將本地的開發測試區的資料庫，上傳到客戶的**業演環境**上，但是每次要做這件事情的時候，都要透過 SSMS 的 **Generate Scripts Wizard** 手動產生 T-SQL 語法，再針對客戶的業演環境進行更新。我理想的作業方式，就是透過命令列工具，讓負責遷移資料庫的工程師，直接滑鼠雙擊一個批次檔就可以完成資料庫匯出，藉此降低操作 SSMS 的過程設定錯誤的機會，畢竟 **Generate Scripts Wizard** 的選項設定超多，一不小心確實很有可能會出錯。

為了這個需求，我找了很久，最後終於在我的 [LINE 社群](https://line.me/ti/g2/Uhq2NM0bA6j3C5O0XBAVnm9CGMxJZgY_UMBsPA)找到了解決方案，那就是使用微軟官方製作的 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 命令列工具，這個工具從 Microsoft Docs 上完全找不到，但是我實測後發現功能跟 SSMS 一樣強大，完完全全可以符合我的資料庫遷移需求。

> 我的 [LINE 社群](https://line.me/ti/g2/Uhq2NM0bA6j3C5O0XBAVnm9CGMxJZgY_UMBsPA)有超過 3,000 個群友，裡面許多不同專業領域的大大，常常能夠激盪出一些相當不錯的想法，也能問出一些相對冷門的經驗，大家如果不怕 LINE 的訊息太多，有興趣可以加入一起交流。

### 簡介 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 命令列工具

這套 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 是一套跨平台的命令列工具，你可以安裝在 Linux、macOS 與 Windows 作業系統上，專門用來將整個 SQL Server 資料庫編寫成 T-SQL 的樣子 (DDL/DML)，其功能與 SSMS 的 **Generate Scripts Wizard** 完全一樣。你透過這個工具輸出的 T-SQL 可以設定相容於任何 SQL Server 版本，也包含雲端的 Azure SQL Database 與 Azure SQL Data Warehouse，可以輸出到一個或多個檔案，也可以直接輸出到 STDOUT 結合標準的 Unix 工具命令 (sed, awk, grep, ..) 來進行自動化的操作。

[mssql-scripter](https://github.com/microsoft/mssql-scripter) 是一套基於 Python 3 的命令列工具，你必須事先安裝 Python 3 才能安裝或執行這套工具。

### 安裝 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 命令列工具

基本上，你要先有 Python 3 才能透過 `pip` 進行安裝，你可以用以下命令先查詢一下自己之前有沒有裝過與安裝過的版本：

```powershell
python -V
```

如果沒有安裝過的話，Windows 10 在第一次使用的時候會自動提示你到 Microsoft Store 進行安裝，因此安裝的過程應該是十分的便利，沒有什麼門檻。

如果是 Windows Server 的話，可以使用 [Chocolatey](https://chocolatey.org/install) 進行安裝：

```powershell
choco install python -y
```

裝好 Python 之後，就可以用以下命令自動安裝完畢：

```powershell
pip install mssql-scripter
```

這裡有個地方要特別注意，我在安裝好之後有遇到一個問題，就是 `PATH` 環境變數並沒有加入 `pip` 必要的工具路徑進去，這樣會導致我無法順利的執行 `mssql-scripter` 命令。在我的電腦，其路徑在以下這裡，這裡的路徑未來很有可能會變更，不同電腦在不同時期也有可能因為版本不同，路徑就不太一樣：

```
"%localappdata%\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\Scripts\"
```

如果你是用 Chocolatey 安裝 [Python](https://community.chocolatey.org/packages/python/3.10.2#individual) 的話，應該就沒這問題了。

> 對於不同作業系統平台的安裝方式可參見 [Installation Guide](https://github.com/microsoft/mssql-scripter/blob/dev/doc/installation_guide.md) 文件。

### 使用 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 命令列工具

由於 [mssql-scripter](https://github.com/microsoft/mssql-scripter) 命令列工具的參數選項特別多，我這邊就列出幾個我比較常用的用法，與我認為重要的注意事項，其他還有很多較為少用的用法，建議可以參考 [Usage Guide](https://github.com/microsoft/mssql-scripter/blob/dev/doc/usage_guide.md) 文件。

1.  查詢命令列可用的選項 (非常多)
    
    ```bash
    mssql-scripter -h
    ```
    
2.  使用 Windows Authentication 連接，編寫 LocalDB 的資料庫（僅含**結構描述**）
    
    ```bash
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' > ContosoUniversity-schema.sql
    ```
    
    你可以加上 `--display-progress` 顯示執行進度：
    
    ```bash
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --display-progress > ContosoUniversity-schema.sql
    ```
    
3.  使用 Windows Authentication 連接，編寫 LocalDB 中的資料庫 (僅含**資料**)
    
    ```bash
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --data-only -f './ContosoUniversity-data.sql'
    ```
    
    > 注意: 透過 `-f` 輸出到特定檔案時，請記得一定要加上路徑部分，若是當前目錄，也要加上 `./` 才行！
    
4.  使用 Windows Authentication 連接，編寫 LocalDB 中的資料庫 (含**結構描述**與**資料**)，並且指定輸出到 SQL Server 2012 資料庫版本 (`2012`)
    
    ```bash
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --schema-and-data -f './ContosoUniversity-full.sql' --target-server-version '2012'
    ```
    
    > 這裡的 `--target-server-version` 可以指定輸出的 T-SQL 要相容於哪個 SQL Server 版本，有效的選項有：`2005`,`2008`,`2008R2`,`2012`,`2014`,`2016`,`vNext`,`AzureDB`,`AzureDW`
    
5.  使用 **SQL Server Authentication** 連接，編寫 Azure SQL Database 中的資料庫 (僅含**結構描述**)
    
    使用 **SQL Server Authentication** 建議多利用 `MSSQL_SCRIPTER_PASSWORD` **環境變數**的方式傳入 `mssql-scripter` 執行，不要用 `-P` 參數傳入密碼。
    
    ```bash
    $env:MSSQL_SCRIPTER_PASSWORD = '3G7tmAv36Vy9'
    mssql-scripter -S 'my-db.database.windows.net' -U 'dbadmin' -d 'mydbname' -f './mydbname.sql'
    ```
    
6.  使用 Windows Authentication 連接，編寫 LocalDB 中的資料庫，並且一個物件一個檔案 (含**結構描述**與**資料**)
    
    加上 `--file-per-object` 就可以將每個物件個別建立檔案，非常適合用來當作版控用途！
    
    ```bash
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --schema-and-data --file-per-object -f 'objects/'
    ```
    
    這裡的 `-f` 參數在有 `--file-per-object` 選項的情況下，必須設定一個資料夾來輸出多個檔案，此命令會建立一個 `objects` 資料夾，並將所有 SQL Server 物件輸出到這個目錄下，每個物件就會有一個檔案。
    
    > 注意: 當 `objects/` 目錄有檔案的情況下，你將無法執行這個命令，你必須先確保資料夾內沒有任何檔案。
    
7.  使用 Windows Authentication 連接，編寫 LocalDB 中的資料庫，並**同時編寫** `DROP` 與 `CREATE` 陳述式！
    
    這種非常適合經常需要將目的資料庫結構描述重建、資料重設、但資料庫本身不用重建的情況，我最近的專案遇到的就是這種情境！
    
    ```bash
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --schema-and-data --file-per-object -f 'objects/' --script-drop-create --check-for-existence --exclude-types 'DATABASE' --exclude-use-database
    ```
    
    這裡用到了 4 個重要參數：
    
    1.  `--script-drop-create` 用來產生 `DROP` 與 `CREATE` 命令
    2.  `--check-for-existence` 用來產生 `DROP --- IF EXISTS` 語法
    3.  `--exclude-types 'database'` 用來排除 `DATABASE` 這個類型的物件，否則他會連同 `DROP DATABASE` 的語法一同產生
    4.  `--exclude-use-database` 用來避免產生 `USE [ContosoUniversity]` 這種語法，比較適合用來重建到不同 DB 名稱的資料庫
8.  使用 Windows Authentication 連接，編寫 LocalDB 中的資料庫，並**分開編寫** `DROP` 與 `CREATE` 陳述式！
    
    ```bash
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --schema-and-data --file-per-object -f 'objects-drop/' --script-drop --check-for-existence --exclude-types 'DATABASE' --exclude-use-database
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --schema-and-data --file-per-object -f 'objects-create/' --script-create --check-for-existence --exclude-types 'DATABASE' --exclude-use-database
    ```
    
    > 若同時使用 `--script-create` 與 `--check-for-existence` 的話，在建立物件的時候如果現有資料庫已經含有同名物件，物件就不會被建立。這個用法對於想要保留特定表格，又要重建部分表格時有用。
    
9.  使用 Windows Authentication 連接，編寫 LocalDB 中的資料庫，並**選擇編寫特定物件**的 `DROP` 與 `CREATE` 陳述式！
    
    假設我們想要重建測試資料庫，但須保留 `Person` 表格不要重建，那麼你可以這樣匯出資料庫：
    
    ```bash
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --schema-and-data -f './ContosoUniversity-drop.sql' --script-drop --check-for-existence --exclude-types 'DATABASE' --exclude-objects 'Person' --exclude-use-database
    mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --schema-and-data -f './ContosoUniversity-create.sql' --script-create --check-for-existence --exclude-types 'DATABASE' --exclude-objects 'Person' --exclude-use-database
    ```
    
    > 若同時使用 `--script-create` 與 `--check-for-existence` 的話，在建立物件的時候如果現有資料庫已經含有同名物件，物件就不會被建立。這個用法對於想要保留特定表格，又要重建部分表格時有用。
    

### 相關連結

-   [microsoft/mssql-scripter: Repository for the new SQL cross-platform command line tools](https://github.com/microsoft/mssql-scripter)
    -   [Installation Guide](https://github.com/microsoft/mssql-scripter/blob/dev/doc/installation_guide.md)
    -   [Usage Guide](https://github.com/microsoft/mssql-scripter/blob/dev/doc/usage_guide.md)
-   [Download Microsoft® SQL Server® Data-Tier Application Framework](https://www.nuget.org/packages/Microsoft.SqlServer.DacFx)
-   [Data-tier Applications - SQL Server | Microsoft Docs](https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686)
-   [SQL Server 的 mssql-cli 命令列查詢工具 (預覽)](https://learn.microsoft.com/zh-tw/sql/tools/mssql-cli?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686)
-   [SQL Server 的 mssql-scripter 命令列查詢工具](https://github.com/microsoft/mssql-scripter)