Using MSDeploy with dbSqlPackage Provider

  • Article

 

DbSqlPackage is an MSDeploy provider that allows you interact with SQL Server/SQL Azure databases. DbSqlPackage supports the following actions:

  • Extract: Creates a database snapshot (.dacpac) file from a live SQL Server or SQL Azure databases.

  • 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 at https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.aspx or SqlPackage.exe (DACFx command-line tool).

Important

The dbSqlPackage provider feature will be removed from the next major release of Visual Studio. For information on how to do database publishing with Web Deploy, see dbDacFx Provider for Incremental Database publishing.

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 a live SQL Server/SQL Azure database, dbSqlPackage will extract a database snapshot in the form of a .dacpac file from a live SQL Server/SQL Azure 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>