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

今天有同事來問我，他用 SQL Server 資料庫專案管理 SQL Server 資料庫的結構描述 (Schema)，在開發環境與測試環境的更新與比對都沒有問題，但偏偏正式機上的 SQL Server 資料庫因為防火牆的關係，通常無法直接從本機直接連接，這導致他沒有辦法比對正式資料庫與開發資料庫之間的差異，如果有人異動了正式資料庫，日後的資料庫更新就很容易出問題。這篇文章，我就來介紹這套 SqlPackage 命令列工具。

SqlPackage

安裝 SqlPackage

SqlPackage 是一套跨平台的命令列工具，支援 Windows、Linux 與 macOS 等作業系統。你可以到 Download and install sqlpackage 頁面下載最新版本。

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

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

Windows 平台還有額外提供一個 MSI 安裝檔可以快速安裝，其他平台都僅提供 ZIP 下載，解壓縮之後就能執行。另外，在 Chocolatey 套件管理器也有 SqlPackage 套件可以安裝。

choco install sqlpackage -y

常見 SqlPackage 使用情境

查詢版本資訊
```
sqlpackage /Version
```
查詢使用說明
```
sqlpackage /Help
```
這裡最重要的參數是 /Action:{Extract|DeployReport|DriftReport|Publish|Script|Export|Import} 參數，這個參數會決定你可以用哪些命令列參數。

雖然此 /Help 命令會顯示很多可用的選項參數，但是不是每個都能用，也很難看得出能不能用，建議搭配官方文件 SqlPackage.exe 所提供的參數範例與說明，上面還有一份可用參數與動作的對照表。
匯出資料庫 (Export Database) (/Action:Export)

匯出資料庫與擷取資料層應用程式在連接來源資料庫的部分，參數用法是完全相同的，所以這部分就不再提供範例，僅提供差異部分。

注意：匯出資料庫並沒有包含 DAC 相關屬性，所以匯出的內容並不是一個 資料層應用程式(Data-tier Application)！

匯出完整資料庫（包含所有結構描述與所有表格中的資料）
```
sqlpackage /Action:Export /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.bacpac"
```
注意：匯出資料層應用程式的目標檔案應該是 *.bacpac 副檔名！

匯出資料庫所有結構描述與部分表格中的資料（你可以透過多組 /p:TableData= 參數指定多個表格名稱）
```
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)

唯有 DAC 的備份檔 (*.bacpac) 才能用來匯入資料庫！

DAC 的備份檔 (*.bacpac) 並未包含應用程式名稱與應用程式版本，所以就是很單純的匯入資料庫而已，並不會自動註冊資料層應用程式。

匯入資料庫
```
sqlpackage /Action:Import /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB"
```

擷取資料層應用程式 (Extract Data-tier Application) (/Action:Extract)

擷取資料層應用程式會自動下載/擷取資料層應用程式(*.dacpac)，你可以在本機進行結構描述比對(Schema compare)，用以確認本地資料庫專案與遠端資料庫之間的結構描述差異。

透過連接字串進行連線

sqlpackage /Action:Extract /SourceConnectionString:"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=ContosoUniversity;Integrated Security=True" /TargetFile:"ContosoUniversity.dacpac"

透過 Windows 驗證進行連線

sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac"

透過 SQL 登入驗證進行連線

sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /SourceUser:"sa" /SourcePassword:"YourPWD" /TargetFile:"ContosoUniversity.dacpac"

透過 Windows 驗證進行連線，並指定 DAC 相關屬性（應用程式名稱、應用程式描述、應用程式版本）

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

透過 Windows 驗證進行連線，擷取時不僅僅擷取結構描述，連同所有使用者資料表的資料也一併擷取

sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac" /p:ExtractAllTableData=true

透過 Windows 驗證進行連線，擷取時僅擷取應用程式層級的物件，跳過有參考伺服器層級的物件

sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac" /p:ExtractApplicationScopedObjectsOnly=true /p:ExtractReferencedServerScopedElements=false

透過 Windows 驗證進行連線，擷取時忽略所有物件的擴充屬性(Extended Properties)，最後驗證擷取結果

sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac" /p:IgnoreExtendedProperties=true /p:VerifyExtraction=True

偵錯擷取過程的完整記錄

sqlpackage /Action:Extract /SourceDatabaseName:"ContosoUniversity" /SourceServerName:"(localdb)\MSSQLLocalDB" /TargetFile:"ContosoUniversity.dacpac" /Diagnostics:True

發行資料庫 (/Action:Publish)

唯有資料層應用程式的擷取檔(*.dacpac)才能用來發行！

資料層應用程式的擷取檔(*.dacpac)包含應用程式名稱與應用程式版本，當你在發行資料庫的時候：
1. 如果是你第一次發行，預設並不會自動會在 SQL Server 中建立起一個資料層應用程式，除非你加上 /p:RegisterDataTierApplication=true 參數！
2. 如果不是第一次發行，那再次發行資料庫就是更新資料庫的的意思，這個動作會更新這個資料庫的結構描述(Schema)。而事實上這個發行動作會對來源與目標進行比對，自動產生一組差異指令碼(Changed Script)出來，最後僅會套用差異變更到目標資料庫上，所以資料庫中現有資料都不會遺失。
發行資料庫
```
sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB"
```
發行資料庫，並同時註冊為資料層應用程式 (/p:RegisterDataTierApplication=true)
```
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)
```
sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:BackupDatabaseBeforeChanges=true
```
注意: 預設資料庫會備份到 SQL Server 設定的 BackupDirectory 備份目錄，但 MSSQLLocalDB 預設會備份到資料檔所在目錄！

發行資料庫，並建立全新資料庫 (/p:CreateNewDatabase=True)
```
sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:CreateNewDatabase=True
```
注意：發行前如果發現有同名資料庫，會自動刪除現有資料庫並重建全新資料庫！

發行資料庫，發行前會將現有資料庫切換到 SINGLE_USER 模式，這個過程將會 Rollback 當前資料庫正在進行的所有交易！ (/p:DeployDatabaseInSingleUserMode=true)
```
sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:DeployDatabaseInSingleUserMode=true
```
發行資料庫，參數 /p:DropObjectsNotInSource=True 會刪除目標資料庫中不存在於來源資料庫的物件，然而可以透過 /p:DoNotDropObjectTypes= 保留特定 SQL Server 物件類型避免被刪除！
```
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= 會特別排除不想被更新的物件類型！
```
sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:ExcludeObjectTypes=Users;Logins
```
發行資料庫，參數 /p:IncludeTransactionalScripts=True 意味著要把整份發行的變更包裹在一個 BEGIN TRANS 交易中！
```
sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:IncludeTransactionalScripts=True
```
這裡 /p:DoNotDropObjectTypes= 可以放入以「分號」間隔的物件類型清單，可以設定的物件類型非常多，完整清單如下： 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, StoredProcedures, SymmetricKeys, Synonyms, Tables, TableValuedFunctions, UserDefinedDataTypes, UserDefinedTableTypes, ClrUserDefinedTypes, Users, Views, XmlSchemaCollections, Audits, Credentials, CryptographicProviders, DatabaseAuditSpecifications, DatabaseScopedCredentials, Endpoints, ErrorMessages, EventNotifications, EventSessions, LinkedServerLogins, LinkedServers, Logins, Routes, ServerAuditSpecifications, ServerRoleMembership, ServerRoles, ServerTriggers。

發行資料庫，指定 SQLCMD 變數，讓發行的同時可以傳入 SQLCMD 變數到發行的 T-SQL 指令碼中 (變數可以在 SSDT 的 Profile 中設定)
```
sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /Variables:Env=Staging
```
發行資料庫之前關閉驗證 Schema 有沒有被偷偷改過，直接將新版更新到目標資料庫
```
sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /p:BlockWhenDriftDetected=false
```
編寫腳本 (/Action:Script)

基本上這個 /Action:Script 動作，所有的參數都跟 /Action:Publish 動作幾乎完全相同。

首先，它在執行的時候，不會真的對資料庫做出改變，而是會輸出一份 T-SQL 格式的變更指令碼 (Changed Script)，所以你必須加上 /OutputPath:Deploy_v1.6.sql 才能將 T-SQL 檔案輸出！
```
sqlpackage /Action:Script /SourceFile:"ContosoUniversity_v1.6.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /OutputPath:Deploy_v1.6.sql
```
部署報告 (/Action:DeployReport)

基本上這個 /Action:DeployReport 動作，所有的參數都跟 /Action:Publish 動作幾乎完全相同。

首先，它在執行的時候，不會真的對資料庫做出改變，而是會輸出一份 XML 格式的檔案，所以你必須加上 /OutputPath:Filename.xml 才能將報告輸出！
```
sqlpackage /Action:DeployReport /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversityNew" /TargetServerName:"(localdb)\MSSQLLocalDB" /OutputPath:ContosoUniversity_v1.0_Report.xml
```
漂移報告 (/Action:DriftReport)

這裡的漂移意味著 目前的資料庫 與 已註冊的資料層應用程式 之間的差異，你可以藉此查詢資料庫中的 Schema 有沒有在上次部署之後有沒有被其他人偷偷改過！

基本上，透過 /Action:Publish 部署的資料庫，預設並不會註冊到 SQL Server 之中，你必須在發行時加上 /p:RegisterDataTierApplication=true 參數，才能將部署的資料庫註冊成為資料層應用程式。
```
sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversity" /TargetServerName:"." /TargetUser:sa /TargetPassword:Ver7CompleXPW /p:RegisterDataTierApplication=true
```
當你將現有資料庫註冊為資料層應用程式之後，如果你手動對資料庫進行任何變更，就會導致 目前的資料庫 與 已註冊的資料層應用程式 之間開始產生差異。此時你若執行下列 /Action:DriftReport 命令，就會得到一份漂移報告，顯示已註冊的資料層應用程式與目前的資料庫之間的變化差異（新增/刪除/修改 SQL 物件）。
```
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 詢問！

目前最新版已經來到 16.0.6296.0，過去的問題已經都確認修掉了。

The Will Will Web

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