擷取資料層應用程式 (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 屬性可以在匯入的時候看到,如下圖示:
透過 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
)包含應用程式名稱與應用程式版本,當你在發行資料庫的時候:
- 如果是你第一次發行,預設並不會自動會在 SQL Server 中建立起一個資料層應用程式,除非你加上
/p:RegisterDataTierApplication=true
參數!
- 如果不是第一次發行,那再次發行資料庫就是更新資料庫的的意思,這個動作會更新這個資料庫的結構描述(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
, 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 中設定)
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