The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

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

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

SQL Server 資料庫的遷移方法

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

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

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

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

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

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

    你可以透過 SSMS (SQL Server Management Studio)、osqlsqlcmdmssql-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 中的資料庫,並同時編寫 DROPCREATE 陳述式!

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

    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 用來產生 DROPCREATE 命令
    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 中的資料庫,並分開編寫 DROPCREATE 陳述式!

    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 中的資料庫,並選擇編寫特定物件DROPCREATE 陳述式!

    假設我們想要重建測試資料庫,但須保留 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 的話,在建立物件的時候如果現有資料庫已經含有同名物件,物件就不會被建立。這個用法對於想要保留特定表格,又要重建部分表格時有用。

相關連結