# 使用 SqlPackage 對 SQL Server 資料庫進行匯出/匯入/擷取/發行/編寫/報告

今天有同事來問我，他用 SQL Server 資料庫專案管理 SQL Server 資料庫的結構描述 (Schema)，在開發環境與測試環境的更新與比對都沒有問題，但偏偏正式機上的 SQL Server 資料庫因為防火牆的關係，通常無法直接從本機直接連接，這導致他沒有辦法比對**正式資料庫**與**開發資料庫**之間的差異，如果有人異動了正式資料庫，日後的資料庫更新就很容易出問題。這篇文章，我就來介紹這套 [SqlPackage](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686) 命令列工具。

![SqlPackage](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/ee10c9b7b7aec02390b7-194925653-c8ab0c20-c573-432d-ba8a-566d978ceee5.png)

### 安裝 SqlPackage

[SqlPackage](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 是一套跨平台的命令列工具，支援 Windows、Linux 與 macOS 等作業系統。你可以到 [Download and install sqlpackage](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 頁面下載最新版本。

> Linux 平台有提供一個永遠指向最新版的短連結 ( https://aka.ms/sqlpackage-linux )，可以幫助你輕鬆實現 CI/CD 自動化。

> Windows 平台有提供一個永遠指向最新版的短連結 ( https://aka.ms/sqlpackage-windows )，可以幫助你輕鬆實現 CI/CD 自動化。

Windows 平台還有額外提供一個 [MSI 安裝檔](https://go.microsoft.com/fwlink/?linkid=2209512)可以快速安裝，其他平台都僅提供 ZIP 下載，解壓縮之後就能執行。另外，在 [Chocolatey](https://chocolatey.org/install) 套件管理器也有 [SqlPackage](https://community.chocolatey.org/packages/SqlPackage) 套件可以安裝。

```batch
choco install sqlpackage -y
```

### 常見 SqlPackage 使用情境

-   查詢版本資訊
    
    ```bash
    sqlpackage /Version
    ```
    
-   查詢使用說明
    
    ```bash
    sqlpackage /Help
    ```
    
    這裡最重要的參數是 `/Action:{Extract|DeployReport|DriftReport|Publish|Script|Export|Import}` 參數，這個參數會決定你可以用哪些命令列參數。
    
    雖然此 `/Help` 命令會顯示很多可用的選項參數，但是不是每個都能用，也很難看得出能不能用，建議搭配官方文件 [SqlPackage.exe](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686) 所提供的參數範例與說明，上面還有一份**可用參數**與**動作**的對照表。
    
-   匯出資料庫 (Export Database) ([`/Action:Export`](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686))
    
    **匯出資料庫**與**擷取資料層應用程式**在連接**來源資料庫**的部分，參數用法是完全相同的，所以這部分就不再提供範例，僅提供差異部分。
    
    > 注意：**匯出資料庫**並沒有包含 DAC 相關屬性，所以匯出的內容並不是一個 **資料層應用程式**(Data-tier Application)！
    
    匯出完整資料庫（包含**所有結構描述**與**所有表格中的資料**）
    
    ```bash
    sqlpackage /Action:Export /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.bacpac"
    ```
    
    > 注意：**匯出資料層應用程式**的目標檔案應該是 `*.bacpac` 副檔名！
    
    匯出資料庫**所有結構描述**與**部分表格中的資料**（你可以透過多組 `/p:TableData=` 參數指定多個表格名稱）
    
    ```bash
    sqlpackage /Action:Export /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.bacpac" /p:TableData="[dbo].[CourseInstructor]" /p:TableData="[dbo].[Course]" /p:TableData="[dbo].[Person]" /p:TableData="[dbo].[Department]"
    ```
    
    > 注意：如果含有 Foreign Key 的表格，卻沒有匯出關連的表格，匯出的過程會發生錯誤，無法匯出成功！
    
-   匯入資料庫 ([`/Action:Import`](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686))
    
    唯有 DAC 的備份檔 (`*.bacpac`) 才能用來**匯入資料庫**！
    
    DAC 的備份檔 (`*.bacpac`) 並**未**包含**應用程式名稱**與**應用程式版本**，所以就是很單純的匯入資料庫而已，並不會自動註冊資料層應用程式。
    
    匯入資料庫
    
    ```bash
    sqlpackage /Action:Import /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB"
    ```
    
-   擷取資料層應用程式 (Extract Data-tier Application) ([`/Action:Extract`](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-extract?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686))
    
    擷取資料層應用程式會自動**下載**/**擷取**資料層應用程式(`*.dacpac`)，你可以在本機進行結構描述比對(Schema compare)，用以確認本地資料庫專案與遠端資料庫之間的結構描述差異。
    
    透過連接字串進行連線
    
    ```bash
    sqlpackage /Action:Extract /SourceConnectionString:"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=ContosoUniversity;Integrated Security=True" /TargetFile:"ContosoUniversity.dacpac"
    ```
    
    透過 Windows 驗證進行連線
    
    ```bash
    sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac"
    ```
    
    透過 SQL 登入驗證進行連線
    
    ```bash
    sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /SourceUser:"sa" /SourcePassword:"YourPWD" /TargetFile:"ContosoUniversity.dacpac"
    ```
    
    透過 Windows 驗證進行連線，並指定 DAC 相關屬性（應用程式名稱、應用程式描述、應用程式版本）
    
    ```bash
    sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac" /p:DacApplicationName="ContosoUniversity" /p:DacApplicationDescription="TESTING" /p:DacMajorVersion=2 /p:DacMinorVersion=1
    ```
    
    上述 DAC 屬性可以在匯入的時候看到，如下圖示：
    
    ![Deploy Data-tier Application](https://stwillblogassets.blob.core.windows.net/files/images/external/user-images.githubusercontent.com/b1338e368b74d6570caf-119466420-2e458d00-bd77-11eb-9f6c-db89dae3358f.jpg)
    
    透過 Windows 驗證進行連線，擷取時不僅僅擷取結構描述，連同所有**使用者資料表**的**資料**也一併擷取
    
    ```bash
    sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac" /p:ExtractAllTableData=true
    ```
    
    透過 Windows 驗證進行連線，擷取時僅擷取應用程式層級的物件，跳過有參考伺服器層級的物件
    
    ```bash
    sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac" /p:ExtractApplicationScopedObjectsOnly=true /p:ExtractReferencedServerScopedElements=false
    ```
    
    透過 Windows 驗證進行連線，擷取時忽略所有物件的**擴充屬性**(Extended Properties)，最後**驗證擷取結果**
    
    ```bash
    sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac" /p:IgnoreExtendedProperties=true /p:VerifyExtraction=True
    ```
    
    偵錯擷取過程的完整記錄
    
    ```bash
    sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac" /Diagnostics:True
    ```
    
-   發行資料庫 ([`/Action:Publish`](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686))
    
    唯有**資料層應用程式**的擷取檔(`*.dacpac`)才能用來**發行**！
    
    **資料層應用程式**的擷取檔(`*.dacpac`)包含**應用程式名稱**與**應用程式版本**，當你在**發行資料庫**的時候：
    
    1.  **如果是你第一次發行**，預設**並不會**自動會在 SQL Server 中建立起一個**資料層應用程式**，除非你加上 `/p:RegisterDataTierApplication=true` 參數！
    2.  **如果不是第一次發行**，那再次發行資料庫就是**更新資料庫**的的意思，這個動作會更新這個資料庫的**結構描述**(Schema)。而事實上這個**發行**動作會對**來源**與**目標**進行比對，自動產生一組**差異指令碼**(Changed Script)出來，最後僅會套用**差異變更**到目標資料庫上，所以資料庫中現有資料都不會遺失。
    
    發行資料庫
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB"
    ```
    
    發行資料庫，並同時註冊為**資料層應用程式** (`/p:RegisterDataTierApplication=true`)
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:RegisterDataTierApplication=true
    ```
    
    > 想要查詢 SQL Server 有哪些資料層應用程式，可以執行 `SELECT * FROM msdb.dbo.sysdac_instances` 這個 T-SQL 語句！但如果是用 Azure SQL Database 的話，命令必須修改為 `SELECT * FROM master.dbo.sysdac_instances`！
    
    發行資料庫，發行之前會**自動備份資料庫** (`/p:BackupDatabaseBeforeChanges=true`)
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:BackupDatabaseBeforeChanges=true
    ```
    
    > 注意: 預設資料庫會備份到 SQL Server 設定的 BackupDirectory 備份目錄，但 `MSSQLLocalDB` 預設會備份到**資料檔**所在目錄！
    
    發行資料庫，並建立全新資料庫 (`/p:CreateNewDatabase=True`)
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:CreateNewDatabase=True
    ```
    
    > 注意：發行前如果發現有**同名資料庫**，會自動**刪除現有資料庫**並**重建全新資料庫**！
    
    發行資料庫，發行前會將現有資料庫切換到 `SINGLE_USER` 模式，這個過程將會 Rollback 當前資料庫正在進行的所有交易！ (`/p:DeployDatabaseInSingleUserMode=true`)
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:DeployDatabaseInSingleUserMode=true
    ```
    
    發行資料庫，參數 `/p:DropObjectsNotInSource=True` 會刪除**目標資料庫中**不存在於**來源資料庫**的物件，然而可以透過 `/p:DoNotDropObjectTypes=` 保留特定 SQL Server **物件類型**避免被刪除！
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:DropObjectsNotInSource=True /p:DoNotDropObjectTypes=Tables;Views;StoredProcedures
    ```
    
    > 這裡 `/p:DoNotDropObjectTypes=` 可以放入以「分號」間隔的**物件類型**清單，可以設定的物件類型非常多，完整清單如下： `Tables`, `Views`, `StoredProcedures`, `Users`, `Logins`, `Aggregates`, `ApplicationRoles`, `Assemblies`, `AsymmetricKeys`, `BrokerPriorities`, `Certificates`, `ColumnEncryptionKeys`, `ColumnMasterKeys`, `Contracts`, `DatabaseRoles`, `DatabaseTriggers`, `Defaults`, `ExtendedProperties`, `ExternalDataSources`, `ExternalFileFormats`, `ExternalTables`, `Filegroups`, `FileTables`, `FullTextCatalogs`, `FullTextStoplists`, `MessageTypes`, `PartitionFunctions`, `PartitionSchemes`, `Permissions`, `Queues`, `RemoteServiceBindings`, `RoleMembership`, `Rules`, `ScalarValuedFunctions`, `SearchPropertyLists`, `SecurityPolicies`, `Sequences`, `Services`, `Signatures`, `SymmetricKeys`, `Synonyms`, `TableValuedFunctions`, `UserDefinedDataTypes`, `UserDefinedTableTypes`, `ClrUserDefinedTypes`, `XmlSchemaCollections`, `Audits`, `Credentials`, `CryptographicProviders`, `DatabaseAuditSpecifications`, `DatabaseScopedCredentials`, `Endpoints`, `ErrorMessages`, `EventNotifications`, `EventSessions`, `LinkedServerLogins`, `LinkedServers`, `Routes`, `ServerAuditSpecifications`, `ServerRoleMembership`, `ServerRoles`, `ServerTriggers`。
    
    發行資料庫，參數 `/p:ExcludeObjectTypes=` 會特別**排除**不想被更新的**物件類型**！
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:ExcludeObjectTypes=Users;Logins
    ```
    
    發行資料庫，參數 `/p:IncludeTransactionalScripts=True` 意味著要把整份發行的變更包裹在一個 `BEGIN TRANS` 交易中！
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:IncludeTransactionalScripts=True
    ```
    
    > 這裡 `/p:DoNotDropObjectTypes=` 可以放入以「分號」間隔的**物件類型**清單，可以設定的物件類型非常多，完整清單如下： `Aggregates`, `ApplicationRoles`, `Assemblie`s, `AsymmetricKeys`, `BrokerPriorities`, `Certificates`, `ColumnEncryptionKeys`, `ColumnMasterKeys`, `Contracts`, `DatabaseRoles`, `DatabaseTriggers`, `Default`s, `ExtendedProperties`, `ExternalDataSources`, `ExternalFileFormats`, `ExternalTables`, `Filegroups`, `FileTables`, `FullTextCatalogs`, `FullTextStoplist`s, `MessageTypes`, `PartitionFunctions`, `PartitionSchemes`, `Permissions`, `Queues`, `RemoteServiceBindings`, `RoleMembership`, `Rules`, `ScalarValuedFunction`s, `SearchPropertyLists`, `SecurityPolicies`, `Sequences`, `Services`, `Signatures`, `StoredProcedures`, `SymmetricKeys`, `Synonyms`, `Tables`, `TableValuedFunction`s, `UserDefinedDataTypes`, `UserDefinedTableTypes`, `ClrUserDefinedTypes`, `Users`, `Views`, `XmlSchemaCollections`, `Audits`, `Credentials`, `CryptographicProvider`s, `DatabaseAuditSpecifications`, `DatabaseScopedCredentials`, `Endpoints`, `ErrorMessages`, `EventNotifications`, `EventSessions`, `LinkedServerLogin`s, `LinkedServers`, `Logins`, `Routes`, `ServerAuditSpecifications`, `ServerRoleMembership`, `ServerRoles`, `ServerTriggers`。
    
    發行資料庫，指定 SQLCMD 變數，讓發行的同時可以傳入 SQLCMD 變數到發行的 T-SQL 指令碼中 (變數可以在 SSDT 的 Profile 中設定)
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /Variables:Env=Staging
    ```
    
    發行資料庫之前關閉驗證 Schema 有沒有被偷偷改過，直接將新版更新到目標資料庫
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:BlockWhenDriftDetected=false
    ```
    
-   編寫腳本 ([`/Action:Script`](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-script?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686))
    
    基本上這個 `/Action:Script` 動作，所有的參數都跟 `/Action:Publish` 動作幾乎完全相同。
    
    首先，它在執行的時候，不會真的對資料庫做出改變，而是會輸出一份 T-SQL 格式的**變更指令碼** (Changed Script)，所以你必須加上 `/OutputPath:Deploy_v1.6.sql` 才能將 T-SQL 檔案輸出！
    
    ```bash
    sqlpackage /Action:Script /SourceFile:"ContosoUniversity_v1.6.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /OutputPath:Deploy_v1.6.sql
    ```
    
-   部署報告 ([`/Action:DeployReport`](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-deploy-drift-report?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686))
    
    基本上這個 `/Action:DeployReport` 動作，所有的參數都跟 `/Action:Publish` 動作幾乎完全相同。
    
    首先，它在執行的時候，不會真的對資料庫做出改變，而是會輸出一份 XML 格式的檔案，所以你必須加上 `/OutputPath:Filename.xml` 才能將報告輸出！
    
    ```bash
    sqlpackage /Action:DeployReport /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /OutputPath:ContosoUniversity_v1.0_Report.xml
    ```
    
-   漂移報告 ([`/Action:DriftReport`](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-deploy-drift-report?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686#driftreport-action-parameters))
    
    這裡的 **漂移** 意味著 **目前的資料庫** 與 **已註冊的資料層應用程式** 之間的差異，你可以藉此查詢資料庫中的 Schema 有沒有在上次部署之後有沒有被其他人偷偷改過！
    
    基本上，透過 `/Action:Publish` 部署的資料庫，預設並不會 **註冊** 到 SQL Server 之中，你必須在發行時加上 `/p:RegisterDataTierApplication=true` 參數，才能將部署的資料庫**註冊**成為**資料層應用程式**。
    
    ```bash
    sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversity" /TargetServerName:"." /TargetUser:sa /TargetPassword:Ver7CompleXPW /p:RegisterDataTierApplication=true
    ```
    
    當你將現有資料庫註冊為**資料層應用程式**之後，如果你**手動**對**資料庫**進行任何變更，就會導致 **目前的資料庫** 與 **已註冊的資料層應用程式** 之間開始產生差異。此時你若執行下列 `/Action:DriftReport` 命令，就會得到一份**漂移報告**，顯示**已註冊的資料層應用程式**與**目前的資料庫**之間的變化差異（新增/刪除/修改 SQL 物件）。
    
    ```bash
    sqlpackage /Action:DriftReport /TargetDatabaseName:"ContosoUniversity" /TargetServerName:"(localdb)\MSSQLLocalDB" /OutputPath:DriftReport.xml
    ```
    
    > ~~注意: 我目前使用的是 SqlPackage 的 `15.0.5084.2` 當前最新版本。但只要透過 `/Action:Publish` 搭配 `/p:RegisterDataTierApplication=true` 註冊資料層應用程式的話，之後 SqlPackage 就再也讀取不到資料層應用程式的資訊。此問題我有發布到 [Unable to read data-tier application registration after Publish using SqlPackage #65](https://github.com/microsoft/DACExtensions/issues/65) 詢問！~~
    
    > 目前最新版已經來到 `16.0.6296.0`，過去的問題已經都確認修掉了。
    

### 相關連結

-   [資料層應用程式 - SQL Server | Microsoft Docs](https://learn.microsoft.com/zh-tw/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver15&WT.mc_id=DT-MVP-4015686)
-   [Download and install SqlPackage](https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)
-   [SqlPackage.exe - Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques](https://www.sqlshack.com/sqlpackage-exe-automate-sql-server-database-restoration-using-bacpac-with-powershell-or-batch-techniques/)