The Will Will Web

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

使用 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,過去的問題已經都確認修掉了。

相關連結

留言評論