Use MSDeploy with dbSqlPackage provider (deprecated)
Important
The dbSqlPackage provider feature is deprecated, and is not compatible with current releases of DacFx. Consider using SqlPackage (DacFx command-line tool) for your SQL Server and Azure SQL deployment scenarios.
DbSqlPackage is an MSDeploy provider that allows you interact with SQL Server / Azure SQL Database. DbSqlPackage supports the following actions:
Extract: Creates a database snapshot (.dacpac) file from a live SQL Server or Azure SQL Database.
Publish: Incrementally updates a database schema to match the schema of a source .dacpac file.
DeployReport: Creates an XML report of the changes that would be made by a publish action.
Script: Creates a Transact-SQL script equivalent to the script executed by the Publish Action.
For more information regarding DACFx, please see the DACFx managed API documentation or SqlPackage (DacFx command-line tool).
Command Line Syntax
MSDeploy with the dbSqlPackage provider uses a command line of the following form:
MSDeploy -verb: MSDeploy-verb -source:dbSqlPackage="Input"[,dbSqlPackage-source-parameters] -dest:dpSqlPackage="Input"[,dbSqlPackage-target-parameters]
MS-Deploy Verbs
You specify MS-Deploy verbs using the -verb switch on the MS-Deploy command line. The dbSqlPackage provider supports the following MSDeploy verbs:
| Verb | Description |
|---|---|
| dump | Provides information including name, version number, and description, about a source database contained in a .dacpac file. Specify the source database using the following format on the command line: msdeploy -verb:dump -source:dbSqlPackage=".dacpac-file-path" |
| sync | Specifies dbSqlPackage actions using the following format on the command line: msdeploy -verb:sync -source:dbSqlPackage="input" [,DbSqlPackage-source-parameters] -dest:dbSqlPackage="input" [,DbSqlPackage-destination-parameters] See the sections below for the valid source and destination parameters for the sync verb. |
dbSqlPackage Source
The dbSqlPackage provider takes an input that is either a valid SQL Server/SQL Azure connection string or a path to a .dacpac file on disk. The syntax for specifying the input source for the provider is the following:
| Input | Default | Description |
|---|---|---|
| -source:dbSqlPackage={input} | N/A | input is a valid SQL Server or SQL Azure connection string or a path to a .dacpac file on disk. NOTE: The only connection string properties supported when using a connection string as an input source are InitialCatalog, DataSource, UserID, Password, IntegratedSecurity, Encrypt, TrustServerCertificate, and ConnectionTimeout. |
If your input source is a connection string to SQL Server / Azure SQL Database, dbSqlPackage will extract a database snapshot in the form of a .dacpac file from SQL Server / Azure SQL Database.
The Source parameters are:
| Parameter | Default | Description |
|---|---|---|
| Profile:{ string} | N/A | Specifies the file path to a DAC Publish Profile. The profile defines a collection of properties and variables to use when generating the resulting dacpac. The publish profile is passed to the destination and used as the default options when performing one a Publish, Script or DeployReport action. |
| DacApplicationName={ string } | Database name | Defines the Application name to be stored in the DACPAC metadata. The default string is the database name. |
| DacMajorVersion ={integer} | 1 | Defines the major version to be stored in the DACPAC metadata. |
| DacMinorVersion={integer} | 0 | Defines the minor version to be stored in the DACPAC metadata. |
| DacApplicationDescription={ string } | N/A | Defines the Application description to be stored in the DACPAC metadata. |
| ExtractApplicationScopedObjectsOnly={True | False} | True | If True, extracts only application-scoped objects from the source. If False, extracts both application-scoped objects and non-application-scoped objects. |
| ExtractReferencedServerScopedElements={True | False} | True | If True, extract login, server audit and credential objects referenced by source database objects. |
| ExtractIgnorePermissions={True | False} | False | If True, ignores extracting permissions for all extracted objects; if False, does not. |
| ExtractStorage={File|Memory} | File | Specifies the type of backing storage for the schema model used during extraction. |
| ExtractIgnoreExtendedProperties={True|False} | False | Specifies whether extended properties should be ignored. |
| VerifyExtraction = {True|False} | False | Specifies whether the extracted dacpac should be verified. |
DbSqlPackage Destination
The dbSqlPackage provider accepts either a valid SQL Server/SQL Azure connection string or a path to a .dacpac file on disk as the destination input. The syntax for specifying the destination for the provider is the following:
| Input | Default | Description |
|---|---|---|
| -dest:dbSqlPackage={input} | N/A | input is a valid SQL Server or SQL Azure connection string or a full or partial path to a .dacpac file on disk. If input is a file path, no other parameters may be specified. |
The following Destination parameters are available for all dbSqlPackage operations:
| Property | Default | Description |
|---|---|---|
| Action={Publish|DeployReport|Script} | N/A | Optional parameter specifying the action to perform at the Destination. |
| AllowDropBlockingAssemblies ={True | False} | False | Specifies whether SqlClr publishing drops blocking assemblies as part of the deployment plan. By default, any blocking or referencing assemblies block an assembly update if the referencing assembly must be dropped. |
| AllowIncompatiblePlatform={True | False} | False | Specifies whether the publish action should go forward despite potentially incompatible SQL Server platforms. |
| BackupDatabaseBeforeChanges={True | False} | False | Backups the database before deploying any changes. |
| BlockOnPossibleDataLoss={ True | False} | True | Specifies whether the publish episode is terminated if the publish operation might cause data loss. |
| BlockWhenDriftDetected={ True | False} | True | Specifies whether to block updating a database whose schema no longer matches its registration or is unregistered. |
| CommentOutSetVarDeclarations= {True | False} | False | Specifies whether SETVAR variable declarations are commented out in the generated publish script. You might choose to do this if you plan to use a tool such as SQLCMD.exe to specify the values on the command line when you publish. |
| CompareUsingTargetCollation={ True | False} | False | This setting dictates how the database's collation is handled during deployment; by default the target database's collation will be updated if it does not match the collation specified by the source. When this option is set, the target database's (or server's) collation should be used. |
| CreateNewDatabase={ True | False} | False | Specifies whether the target database should be updated or whether it should be dropped and re-created when you publish to a database. |
| DeployDatabaseInSingleUserMode={ True | False} | False | If True, the database is set to Single User Mode before deploying. |
| DisableAndReenableDdlTriggers={True | False} | True | Specifies whether Data Definition Language (DDL) triggers are disabled at the beginning of the publish process and re-enabled at the end of the publish action. |
| DoNotAlterChangeDataCaptureObjects={ True | False} | True | If True, Change Data Capture objects are not altered. |
| DoNotAlterReplicatedObjects=( True | False} | True | Specifies whether objects that are replicated are identified during verification. |
| DropConstraintsNotInSource= {True | False} | True | Specifies whether the publish action drops constraints that do not exist in the database snapshot (.dacpac) from the target database when you publish to a database. |
| DropDmlTriggersNotInSource= {True | False} | True | Specifies whether the publish action drops Data Manipulation Language (DML) triggers that do not exist in the database snapshot (.dacpac) from the target database when you publish to a database. |
| DropExtendedPropertiesNotInSource= {True | False} | True | Specifies whether the publish action drops extended properties that do not exist in the database snapshot (.dacpac) from the target database when you publish to a database. |
| DropIndexesNotInSource= {True | False} | True | Specifies whether the publish action drops indexes that do not exist in the database snapshot (.dacpac) from the target database when you publish to a database. |
| DropObjectsNotInSource= {True | False} | False | Specifies whether objects that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. |
| DropPermissionsNotInSource= {True | False} | False | Specifies whether the publish action permissions that do not exist in the database snapshot (.dacpac) from the target database when you publish to a database. |
| DropRoleMembersNotInSource= {True | False} | False | Specifies whether the publish action drops role members that do not exist in the database snapshot (.dacpac) from the target database when you publish to a database. |
| GenerateSmartDefaults={True | False} | False | Specifies whether SqlPackage.exe provides a default value automatically when it updates a table that contains data with a column that does not allow null values. |
| IgnoreAnsiNulls= {True | False} | False | Specifies whether to ignore or update differences in the ANSI NULLS setting when you publish to a database. |
| IgnoreAuthorizer= {True | False} | False | Specifies whether to ignore or update differences in the Authorizer when you publish to a database. |
| IgnoreColumnCollation= {True | False} | False | Specifies whether to ignore or update differences in column collation when you publish to a database. |
| IgnoreComments= {True | False} | False | Specifies whether to ignore or update differences in comments order when you publish to a database. |
| IgnoreCryptographicProviderFile= {True | False} | True | Specifies whether to ignore or update differences in the file path for a cryptographic provider when you publish to a database. |
| IgnoreDdlTriggerOrder= {True | False} | False | Specifies whether to ignore or update differences in the order of Data Definition Language (DDL) triggers when you publish to a database. |
| IgnoreDdlTriggerState={True | False} | False | Specifies whether to ignore or update differences in the enabled or disabled state of DDL triggers when you publish to a database. |
| IgnoreDefaultSchema={True | False} | False | Specifies whether to ignore or update differences in the default schema when you publish to a database. |
| IgnoreDmlTriggerOrder= {True | False} | False | Specifies whether to ignore or update differences in the order of DML triggers when you publish to a database. |
| IgnoreDmlTriggerState= {True | False} | False | Specifies whether to ignore or update differences in the enabled or disabled state of DML triggers when you publish to a database. |
| IgnoreExtendedProperties= {True | False} | False | Specifies whether to ignore or update differences in extended properties when you publish to a database. |
| IgnoreFileAndLogFilePath={True | False} | True | Specifies whether to ignore or update differences in the paths for files and log files when you publish to a database. |
| IgnoreFilegroupPlacement= {True | False} | True | Specifies whether to ignore or update differences in the placement of FILEGROUPs when you publish to a database. |
| IgnoreFileSize= {True | False} | True | Specifies whether to ignore or update differences in file sizes when you publish to a database. |
| IgnoreFillFactor= {True | False} | True | Specifies whether to ignore or update differences in fill factors when you publish to a database. |
| Property | Default | Description |
|---|---|---|
| IgnoreFullTextCatalogFilePath= {True | False} | True | Specifies whether to ignore or update differences in the path to full-text index files when you publish to a database. |
| IgnoreIdentitySeed= {True | False} | False | Specifies whether to ignore or update differences in the seed for an identity column when you publish to a database. |
| IgnoreIncrement= {True | False} | False | Specifies whether to ignore or update differences in the increment for an identity column when you publish to a database. |
| IgnoreIndexOptions ={True | False} | False | Specifies whether to ignore or update differences in the index options when you publish to a database. |
| IgnoreIndexPadding= {True | False} | True | Specifies whether to ignore or update differences in the index padding when you publish to a database. |
| IgnoreKeywordCasing= {True | False} | True | Specifies whether to ignore or update differences in the keyword casing when you publish to a database. |
| IgnoreLockHintsOnIndexes= {True | False} | False | Specifies whether to ignore or update differences in the lock hints on indexes when you publish to a database. |
| IgnoreLoginSids= {True | False} | True | Specifies whether to ignore or update differences in the security identifier (SID) when you publish to a database. |
| IgnoreNotForReplication= {True | False} | False | Specifies whether to ignore or update the not-for-replication setting when you publish to a database. |
| IgnoreObjectPlacementOnPartitionScheme= {True | False} | True | Specifies whether to ignore or update an object's placement on a partition scheme when you publish to a database. |
| IgnorePartitionSchemes= {True | False} | False | Specifies whether to ignore or update differences in partition schemes and functions when you publish to a database. |
| IgnorePermissions= {True | False} | False | Specifies whether to ignore or update differences in permissions when you publish to a database. |
| IgnoreQuotedIdentifiers= {True | False} | False | Specifies whether to ignore or update differences in quoted identifier settings when you publish to a database. |
| IgnoreRoleMembership={True | False} | False | Specifies whether differences in the role membership of logins should be ignored or updated when you publish to a database. |
| IgnoreRouteLifetime= {True | False} | True | Specifies whether to ignore or update differences in the role memberships of logins when you publish to a database. |
| IgnoreSemicolonBetweenState= {True | False} | True | Specifies whether to ignore or update differences in semicolons between Transact-SQL statements when you publish to a database. |
| IgnoreTableOptions= {True | False} | False | Specifies whether to ignore or update differences in table options when you publish to a database. |
| IgnoreUserSettingsObjects= {True | False} | False | Specifies whether to ignore or update differences in user setting options when you publish to a database. |
| IgnoreWhitespace= {True | False} | True | Specifies whether to ignore or update differences in whitespace when you publish to a database. |
| IgnoreWithNocheckOnCheckConstraints={True | False} | False | Specifies whether to ignore or update differences in the value of the WITH NOCHECK clause for check constraints when you publish to a database. |
| IgnoreWithNocheckOnForeignKeys={True | False} | False | Specifies whether to ignore or update differences in the value of the WITH NOCHECK clause for foreign keys when you publish to a database. |
| IncludeCompositeObjects= {True | False} | False | Specifies whether to include all composite elements as part of a single publish operation. |
| IncludeTransactionalScripts={True | False} | False | Specifies whether to use transactional statements wherever possible when you publish to a database. |
| NoAlterStatementsToChangeClrTypes={True | False} | False | Specifies that publish should always drop and re-create an assembly if there is a difference instead of issuing an ALTER ASSEMBLY statement. |
| PopulateFilesOnFilegroups= {True | False} | True | Specifies whether a new file is also created when you create a new FileGroup in the target database. |
| RegisterDataTierApplication={True | False} | False | Specifies whether the schema is registered with the database server. |
| ScriptDatabaseCollation {True | False} | False | Specifies whether to ignore or update differences in database collation when you publish to a database. |
| ScriptDatabaseCompatibility= {True | False} | True | Specifies whether to ignore or update differences in database compatibility when you publish to a database. |
| ScriptDatabaseOptions= {True | False} | True | Specifies whether to set or update target database properties when you publish to a database. |
| ScriptFileSize={True | False} | False | Controls whether size is specified when adding a file to a filegroup. |
| ScriptNewConstraintValidation= {True | False} | True | Specifies whether to verify all constraints as one set at the end of publishing, avoiding data errors caused by a check or foreign key constraint in the middle of the publish action. If this option is False, constraints are published without checking the corresponding data. |
| ScriptDeployStateChecks={True | False} | False | Specifies whether to generate statements in the publish script to verify that the database and server names match the names specified in the database project. |
| ScriptRefreshModule= {True | False} | True | Specifies whether to include refresh statements at the end of the publish script. |
| Storage={File|Memory} | Memory | Specifies how elements are stored when building the database model. For performance reasons the default is Memory. For very large databases, File backed storage is required. |
| TreatVerificationErrorsAsWarnings= {True | False} | False | Specifies whether to treat errors that occur during publish verification as warnings. The check is performed against the generated deployment plan before the plan is executed against the target database. Plan verification detects problems, such as the loss of target-only objects (for example, indexes), that must be dropped to make a change. Verification also detects situations where dependencies (such as tables or views) exist because of a reference to a composite project, but do not exist in the target database. You might choose to treat verification errors as warnings to get a complete list of issues instead of allowing the publish action to stop when the first error occurs. |
| UnmodifiableObjectWarnings= {True | False} | True | Specifies whether to generate warnings when differences are found in objects that cannot be modified (for example, if the file size or file paths are different for a file). |
| VerifyCollationCompatibility={True | False} | True | Specifies whether collation compatibility is verified. |
| VerifyDeployment={True | False} | True | Specifies whether to perform checks before publishing that stop the publish action if issues are present that might block successful publishing. For example, your publish action might stop if you get errors during publishing because foreign keys on the target database do not exist in the database project. |
Note
Any destination parameters specified will override those specified in the source Publish Profile.
Note
SQLCMD variables and values must be specified in the Publish Profile source parameter, as they cannot be specified as a destination parameter.
The following Destination parameters are available for DeployReport and Script operations only:
| Parameter | Default | Description |
|---|---|---|
| OutputPath={ string } | N/A | Optional parameter that tells dbSqlPackage to create the DeployReport XML output file or Script SQL output file in the disk location specified by string. This action overwrites any scripts that currently reside in the location given by string. |
Note
If OutputPath parameter is not provided for a DeployReport or Script action, the output is returned as a message.
Examples
The following is example syntax for an Extract operation using dbSqlPackage:
MSDeploy.exe -verb:sync -source:dbSqlPackage="<source connection string>",<source parameter> -dest:dbSqlPackage="<target dacpac file path>"
The following is example syntax for a Publish operation using dbSqlPackage:
MSDeploy.exe -verb:sync -source:dbSqlPackage="<source dacpac file path>" -dest:dbSqlPackage="<target SQL Server/SQL Azure connection string>",Action=Publish,<destination parameters>
The following is example syntax for a DeployReport operation using dbSqlPackage:
MSDeploy.exe -verb:sync -source:dbSqlPackage="<source dacpac file path>" -dest:dbSqlPackage="<target SQL Server/SQL Azure connection string>",Action=DeployReport,OutputPath="<path to output XML file>",<destination parameters>
The following is example syntax for a Script operation using dbSqlPackage:
MSDeploy.exe -verb:sync -source:dbSqlPackage="<source dacpac file path>" -dest:dbSqlPackage="<target SQL Server/SQL Azure connection string>",Action=Script,OutputPath="<path to output sql script>",<destination parameters>
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for