The Will Will Web

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

SQL Server 資料庫的遷移方法

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

1. 透過 資料層應用程式 (DAC) (Data-tier Applications) 遷移資料庫

   你可以透過 SSMS (SQL Server Management Studio) 或 DACFx 的 SqlPackage 工具，將要遷移的資料庫擷取或匯出成 *.dacpac 或 *.bacpac 檔案，再從目標 SQL Server 發行/匯入資料庫。

   你只要下載 DACFx 安裝之後，就可以透過 SqlPackage.exe 對 SQL Server 資料庫進行匯出/匯入/擷取/發行/編寫/報告等操作，一般來說對資料庫進行 CI/CD 幾乎都會使用這套工具，你當然也可以運用在資料庫遷移的目的上。

   參考文章: 使用 SqlPackage 對 SQL Server 資料庫進行匯出/匯入/擷取/發行/編寫/報告

2. 透過 SQL Server 的資料庫備份/還原來遷移資料庫

   你可以透過 SSMS (SQL Server Management Studio)、osql、sqlcmd 或 mssql-cli 將要遷移的資料庫備份成 *.bak 檔，再從目標 SQL Server 還原資料庫。

   參考文件：BACKUP (Transact-SQL)、RESTORE (Transact-SQL)

3. 透過產生資料庫完整的 T-SQL 命令來遷移資料庫

   你可以透過 SSMS (SQL Server Management Studio) 或 mssql-scripter 來匯出完整的 T-SQL 命令。

   這套 mssql-scripter 命令列工具相當強大，參數非常多，幾乎可以完整取代 SSMS 中的 Generate Scripts Wizard 功能。

   這種遷移資料庫的方式最具彈性，因為你會得到純文字的 T-SQL 命令腳本，無論複製到哪一台 SQL Server 都可以很輕易的執行 T-SQL，甚至可以手動微調過後執行，而且通常不太會遇到版本相容性的問題。

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

前幾天我在 Will 保哥的技術交流中心 LINE 社群 跟幾位群友聊到，我該如何透過命令列工具產生 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 社群找到了解決方案，那就是使用微軟官方製作的 mssql-scripter 命令列工具，這個工具從 Microsoft Docs 上完全找不到，但是我實測後發現功能跟 SSMS 一樣強大，完完全全可以符合我的資料庫遷移需求。

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

簡介 mssql-scripter 命令列工具

這套 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 是一套基於 Python 3 的命令列工具，你必須事先安裝 Python 3 才能安裝或執行這套工具。

安裝 mssql-scripter 命令列工具

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

python -V

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

如果是 Windows Server 的話，可以使用 Chocolatey 進行安裝：

choco install python -y

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

pip install mssql-scripter

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

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

如果你是用 Chocolatey 安裝 Python 的話，應該就沒這問題了。

對於不同作業系統平台的安裝方式可參見 Installation Guide 文件。

使用 mssql-scripter 命令列工具

由於 mssql-scripter 命令列工具的參數選項特別多，我這邊就列出幾個我比較常用的用法，與我認為重要的注意事項，其他還有很多較為少用的用法，建議可以參考 Usage Guide 文件。

1. 查詢命令列可用的選項 (非常多)

   mssql-scripter -h
   

2. 使用 Windows Authentication 連接，編寫 LocalDB 的資料庫（僅含結構描述）

   mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' > ContosoUniversity-schema.sql
   

   你可以加上 --display-progress 顯示執行進度：

   mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --display-progress > ContosoUniversity-schema.sql
   

3. 使用 Windows Authentication 連接，編寫 LocalDB 中的資料庫 (僅含資料)

   mssql-scripter -S '(localdb)\MSSQLLocalDB' -d 'ContosoUniversity' --data-only -f './ContosoUniversity-data.sql'
   

   注意: 透過 -f 輸出到特定檔案時，請記得一定要加上路徑部分，若是當前目錄，也要加上 ./ 才行！

4. 使用 Windows Authentication 連接，編寫 LocalDB 中的資料庫 (含結構描述與資料)，並且指定輸出到 SQL Server 2012 資料庫版本 (2012)

   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 參數傳入密碼。

   $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 就可以將每個物件個別建立檔案，非常適合用來當作版控用途！

   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 陳述式！

   這種非常適合經常需要將目的資料庫結構描述重建、資料重設、但資料庫本身不用重建的情況，我最近的專案遇到的就是這種情境！

   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 陳述式！

   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 表格不要重建，那麼你可以這樣匯出資料庫：

   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
  • Installation Guide
  • Usage Guide
• Download Microsoft® SQL Server® Data-Tier Application Framework
• Data-tier Applications - SQL Server | Microsoft Docs
• SQL Server 的 mssql-cli 命令列查詢工具 (預覽)
• SQL Server 的 mssql-scripter 命令列查詢工具