Export (0) Print
Expand All

SqlPackage.exe

SqlPackage.exe is a command line utility that automates the following database development tasks:

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

  • Export: Exports a live database - including database schema and user data - from SQL Server or Windows Azure SQL Database to a BACPAC package (.bacpac file).

  • Import: Imports the schema and table data from a BACPAC package into a new user database in an instance of SQL Server or Windows Azure SQL Database.

  • Publish: Incrementally updates a database schema to match the schema of a source .dacpac file. If the database does not exist on the server, the publish operation will create it. Otherwise, an existing database will be updated.

  • DeployReport: Creates an XML report of the changes that would be made by a publish action.

  • DriftReport: Creates an XML report of the changes that have been made to a registered database since it was last registered.

  • Script: Creates a Transact-SQL incremental update script that updates the schema of a target to match the schema of a source.

The SqlPackage.exe command line allows you to specify these actions along with action specific parameters and properties.

Command Line Syntax

SqlPackage.exe initiates the actions specified using the parameters, properties, and SQLCMD variables specified on the command line.


          SqlPackage
   {
    parameters
  }{
    properties
  }{
    SQLCMDVariables
  }

To see solutions to common customer problems with Windows Azure SQL Database, SQL Server Data Tools, and SQL Server Management Studio, see Database Lifecycle Management.

General Parameters

General parameters affect the overall operation of SqlPackage.exe.

 

Parameter Short Form Required? Description

/Help

/?

N

Displays a usage statement.

/Action: { Extract | DeployReport | DriftReport | Publish | Script | Export | Import }

/a: { Extract | DeployReport | DriftReport | Publish | Script | Export | Import }

Y

Specifies the action to be performed.

/Quiet: { True | False }

/q: { True | False }

N

Specifies whether detailed feedback is suppressed. Defaults to False .

/OverwriteFiles: { True | False }

/of: { True | False }

N

Specifies if sqlpackage.exe should overwrite existing files. Specifying False causes sqlpackage.exe to abort action if an existing file is encountered. Default value is True .

@ { file-path }

None

N

Specifies the file path to a response file.

Source Parameters

The following table shows the command line parameters that SqlPackage.exe supports for defining sources:

 

Parameter Short Form Default Description

/SourceConnectionString: { string }

/scs: { string }

N/A

Specifies a valid SQL Server/Azure connection string to the source database. If this parameter is specified it shall be used exclusively of all other source parameters.

/SourceDatabaseName: { string }

/sdn: { string }

N/A

Defines the name of the source database.

/SourceUser: { value }

/su: { value }

N/A

For SQL Server authoring scenarios, specifies the SQL Server user that is used to get access to the database.

/SourceEncryptConnection: {True|False}

/sec: { True | False}

False

Specifies if SQL encryption should be used for the source database connection.

/SourceFile: { string }

/sf: { string }

N/A

Specifies a source file to be used as the source of action instead of database. If this parameter is used, no other source parameter shall be valid.

/SourcePassword: { string }

/sp: { string }

N/A

For SQL Server authoring scenarios, defines the password to use to access the source database.

/SourceServerName: {string}

/ssn: { string }

N/A

Specifies the name of the server that hosts the database.

/SourceTimeout: { string }

/st: { string }

15

Specifies the timeout, in seconds, for establishing a connection to the source database.

/SourceTrustServerCertificate: { True | False }

/stsc: { True | False }

False

Specifies whether to use Secure Socket Layer (SSL) to encrypt the source database connection and bypass walking the certificate chain to validate trust.

/SourceUser: { string }

/su: { string }

N/A

For SQL Server authoring scenarios, specifies the SQL Server user that is used to get access to the database.

Target Parameters

The following table shows the command line parameters that SqlPackage.exe supports for defining targets:

 

Parameter Short Form Required? Description

/TargetConnectionString { string }

/tcs: { string }

N/A

Specifies a valid SQL Server or SQL Azure connection string for the target database. If this parameter appears on the command line, the connection string is used exclusively of other target parameters.

/TargetDatabaseName: { string }

/tdn: { string }

N/A

Specifies the name of the target database.

/TargetEncryptConnection: { True | False }

/tec: { True | False }

False

Specifies whether to use SQL encryption for the target database.

/TargetFile: {string}

/tf: {string}

N/A

Specifies a disk file path where the .dacpac file will be written.

/TargetPassword: { string }

/tp: { string }

N/A

For SQL Server authoring scenarios specifies password that is used to get access to the target database.

/TargetServerName: { string }

/ssn: { string }

N/A

Specifies the name of the server that hosts the target database.

/TargetTimeout: { string }

/tt: { string }

15

Specifies the timeout, in seconds, for establishing a connection to the target database.

/TargetTrustServerCertificate: { True | False }

/ttsc: { True | False }

False

Specifies whether to use Secure Socket Layer (SSL) to encrypt the target database connection and bypass walking the certificate chain to validate trust.

/TargetUser: { string }

/tu: { string }

N/A

For SQL Server authoring scenarios specifies the SQL Server user that is used to get access to the target database.

Extract Parameters and Properties

A SqlPackage.exe Extract action creates a database snapshot (in the form of a .dacpac file) from a live database in an on-premise instance of SQL Server or a Windows Azure SQL Database instance. An Extract action can create a .dacpac file that contains just the schema of the source database, or the schema and user table data.

Parameters

Source: All Source Parameters are valid for Extract except /SourceFile: .

Target: Only the following target parameter is available when you specify an Extract action with SqlPackage.exe:

 

Parameter Short Form Required? Description

/TargetFile: { string }

/tf: { string }

Y

Specifies a disk file path where the .dacpac file will be written.

Properties

 

Property Default Description

/p:DacApplicationDescription ={ string }

N/A

Defines the Application description to be stored in the DACPAC metadata.

/p:DacApplicationName ={ string }

N/A

Defined the Application name to be stored in the DACPAC metadata. The default value is the database name.

/p:DacMajorVersion ={ integer }

1

Defines the major version to be stored in the DACPAC metadata.

/p:DacMinorVersion ={ integer }

0

If true, only extract application-scoped objects for the specified source. If false, extract all objects for the specified source.

/p:ExtractAllTableData ={ true|false }

False

If true, creates a .dacpac file that includes the database schema and data from all user tables.

/p:TableData ={ string }

N/A

This property is used to specify individual user tables to include as part of an Extract action. Tables are specified using the two-part table name.

/p:ExtractApplicationScopedObjectsOnly ={ True | False }

True

If true, only extract application-scoped objects for the specified source. If false, extract all objects for the specified source.

/p:ExtractReferencedServerScopedElements ={ True | False }

True

If true, extract login, server audit and credential objects referenced by source database objects.

/p:IgnoreExtendedProperties ={ True | False }

False

Specifies whether extended properties should be ignored.

/p:IgnorePermissions ={ True | False }

True

Specifies whether permissions should be ignored.

/p:IgnoreUserLoginMappings ={ True | False }

False

Specifies whether relationships between users and logins will be ignored.

/p:Storage ={ File | Memory }

File

Specifies the type of backing storage for the schema model used during extraction.

/p:VerifyExtraction ={ True | False }

False

Specifies whether the extracted dacpac should be verified.

NOTE: When performing an Extract action, setting the /p:ExtractAllTableData=true prohibits you from also specifying individual tables using the /p:TableData property. Set /p:ExtractAllTableData=false to specify one or more user tables from which to extract data.

Publish Parameters, Properties, and SQLCMD Variables

A SqlPackage.exe publish operation incrementally updates the schema of a target database to match the structure of a source database. Publishing a deployment package that contains user data for all or a subset of tables will update the table data in addition to the schema. Note that data deployment will overwrite the schema and data in existing tables of the target database. Data deployment will not change existing schema or data in the target database for tables not included in the deployment package.

Parameters

Source: Only /SourceFile: parameter is valid for Publish.

Target: All Target Parameters are valid for Publish except for TargetFile.

The following target parameters are also available when you specify a Publish action with SqlPackage.exe :

 

Parameter Short Form Required? Description

/Profile: { string }

/pr: { 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 outputs.

/Properties:{PropertyName} ={ Value }

/p: {PropertyName}={Value}

N/A

Specifies a name value pair for an action specific property; {PropertyName}={Value}. Refer to the help for a specific action to see that action's property names.

Note: You can publish a deployment package with table data without specifying any additional syntax or constraints.

Properties

 

Property Default Description

/p:AdditionalDeploymentContributorArguments ={ string }

N/A

Specifies additional deployment contributor arguments for the deployment contributors. This should be a semi-colon delimited list of values.

/p:AdditionalDeploymentContributors ={ string }

N/A

Specifies additional deployment contributors which should run when the dacpac is deployed. This should be a semi-colon delimited list of fully qualified build contributor names or IDs.

/p: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.

/p:AllowIncompatiblePlatform ={ True | False }

False

Specifies whether the publish action should go forward despite potentially incompatible SQL Server platforms.

/p:BackupDatabaseBeforeChanges ={ True | False }

False

Backups the database before deploying any changes.

/p:BlockOnPossibleDataLoss ={ True | False }

True

Specifies whether the publish episode is terminated if the publish operation might cause data loss.

/p:BlockWhenDriftDetected ={ True | False }

True

Specifies whether to block updating a database whose schema no longer matches its registration or is unregistered.

/p: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.

/p: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.

/p: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.

/p:DeployDatabaseInSingleUserMode ={ True | False }

False

If true, the database is set to Single User Mode before deploying.

/p: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.

/p:DoNotAlterChangeDataCaptureObjects ={ True | False }

True

If true, Change Data Capture objects are not altered.

/p:DoNotAlterReplicatedObjects ={ True | False }

True

Specifies whether objects that are replicated are identified during verification.

/p: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.

/p: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.

/p: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.

/p: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.

/p:DropObjectsNotInSource ={ True | False }

False

Specifies whether objectsthat do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

/p: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.

/p: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.

/p: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.

/p:IgnoreAnsiNulls ={ True | False }

False

Specifies whether to ignore or update differences in the ANSI NULLS setting when you publish to a database.

/p: IgnoreAuthorizer ={ True | False }

False

Specifies whether to ignore or update differences in the Authorizer when you publish to a database.

/p:IgnoreColumnCollation ={ True | False }

False

Specifies whether to ignore or update differences in column collation when you publish to a database.

/p:IgnoreComments ={ True | False }

False

Specifies whether to ignore or update differences in comments order when you publish to a database.

/p: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.

/p: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.

/p: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.

/p:IgnoreDefaultSchema ={ True | False }

False

Specifies whether to ignore or update differences in the default schema when you publish to a database.

/p:IgnoreDmlTriggerOrder ={ True | False }

False

Specifies whether to ignore or update differences in the order of DML triggers when you publish to a database.

/p: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.

/p:IgnoreExtendedProperties ={ True | False }

False

Specifies whether to ignore or update differences in extended properties when you publish to a database.

/p: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.

/p:IgnoreFilegroupPlacement ={ True | False }

True

Specifies whether to ignore or update differences in the placement of FILEGROUP s when you publish to a database.

/p:IgnoreFileSize ={ True | False }

True

Specifies whether to ignore or update differences in file sizes when you publish to a database.

/p:IgnoreFillFactor ={ True | False }

True

Specifies whether to ignore or update differences in fill factors when you publish to a database.

/p: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.

/p:IgnoreIdentitySeed ={ True | False }

False

Specifies whether to ignore or update differences in the seed for an identity column when you publish to a database.

/p:IgnoreIncrement ={ True | False }

False

Specifies whether to ignore or update differences in the increment for an identity column when you publish to a database.

/p:IgnoreIndexOptions ={ True | False }

False

Specifies whether to ignore or update differences in the index options when you publish to a database.

/p:IgnoreIndexPadding ={ True | False }

True

Specifies whether to ignore or update differences in the index padding when you publish to a database.

/p:IgnoreKeywordCasing ={ True | False }

True

Specifies whether to ignore or update differences in the keyword casing when you publish to a database.

/p:IgnoreLockHintsOnIndexes ={ True | False }

False

Specifies whether to ignore or update differences in the lock hints on indexes when you publish to a database.

/p:IgnoreLoginSids ={ True | False }

True

Specifies whether to ignore or update differences in the security identifier (SID) when you publish to a database.

/p:IgnoreNotForReplication ={ True | False }

False

Specifies whether to ignore or update the not-for-replication setting when you publish to a database.

/p:IgnoreObjectPlacementOnPartitionScheme ={ True | False }

True

Specifies whether to ignore or update an object’s placement on a partition scheme when you publish to a database.

/p:IgnorePartitionSchemes ={ True | False }

False

Specifies whether to ignore or update differences in partition schemes and functions when you publish to a database.

/p:IgnorePermissions ={ True | False }

False

Specifies whether to ignore or update differences in permissions when you publish to a database.

/p:IgnoreQuotedIdentifiers ={ True | False }

False

Specifies whether to ignore or update differences in quoted identifier settings when you publish to a database.

/p:IgnoreRoleMembership ={ True | False }

False

Specifies whether differences in the role membership of logins should be ignored or updated when you publish to a database.

/p:IgnoreRouteLifetime ={ True | False }

True

Specifies whether to ignore or update differences in the role memberships of logins when you publish to a database.

/p:IgnoreSemicolonBetweenStatements ={ True | False }

True

Specifies whether to ignore or update differences in semicolons between Transact-SQL statements when you publish to a database.

/p:IgnoreTableOptions ={ True | False }

False

Specifies whether to ignore or update differences in table options when you publish to a database.

/p:IgnoreUserSettingsObjects ={ True | False }

False

Specifies whether to ignore or update differences in user setting options when you publish to a database.

/p:IgnoreWhitespace ={ True | False }

True

Specifies whether to ignore or update differences in whitespace when you publish to a database.

/p: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.

/p: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.

/p:IncludeCompositeObjects ={ True | False }

False

Specifies whether to include all composite elements as part of a single publish operation.

/p:IncludeTransactionalScripts ={ True | False }

False

Specifies whether to use transactional statements wherever possible when you publish to a database.

/p: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.

/p:PopulateFilesOnFilegroups ={ True | False }

True

Specifies whether a new file is also created when you create a new FileGroup in the target database.

/p:RegisterDataTierApplication ={ True | False }

False

Specifies whether the schema is registered with the database server.

/p:RunDeploymentPlanExecutors ={ True | False }

False

Specifies whether DeploymentPlanExecutor contributors should be run when other operations are executed.

/p:ScriptDatabaseCollation ={ True | False }

False

Specifies whether to ignore or update differences in database collation when you publish to a database.

/p:ScriptDatabaseCompatibility ={ True | False }

True

Specifies whether to ignore or update differences in database compatibility when you publish to a database.

/p:ScriptDatabaseOptions ={ True | False }

True

Specifies whether to set or update target database properties when you publish to a database.

/p:ScriptFileSize ={ True | False }

False

Controls whether size is specified when adding a file to a filegroup.

p: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.

/p: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.

/p:ScriptRefreshModule ={ True | False }

True

Specifies whether to include refresh statements at the end of the publish script.

/p:Storage ={ File | Memory }

Memory

Specifies how elements are stored when building the database model. For performance reasons the default is InMemory. For very large databases, File backed storage is required.

/p: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.

/p: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).

/p:VerifyCollationCompatibility ={ True | False }

True

Specifies whether collation compatibility is verified.

/p: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.

SQLCMD Variables

The following table describes the format of the option that you can use to override the value of a SQL command ( sqlcmd ) variable used during a publish action. The values of variable specified on the command line override other values assigned to the variable (for example, in a publish profile).

 

Parameter Default Description

/Variables:{PropertyName}={Value}

Specifies a name value pair for an action specific variable; {VariableName}={Value}. The DACPAC file contains the list of valid SQLCMD variables. An error will result if a value is not provided for every variable.

Export Parameters and Properties

A SqlPackage.exe Export action exports a live database from SQL Server or Windows Azure SQL Database to a BACPAC package (.bacpac file). By default, data for all tables will be included in the .bacpac file. Optionally, you can specify only a subset of tables for which to export data. Validation for the Export action ensures Windows Azure SQL Database compatibility for the complete targeted database even if a subset of tables is specified for the export.

Parameters

Source: All Source Parameters are supported for Export except /SourceFile :.

Target: The only valid target parameter for Export is /TargetFile :{string}, where {string} is the file path on disk where the .bacpac file will be written.

Restrictions:

  • Exporting table data is only supported for user tables.

  • Tables specified for export must be in a closed set. User tables cannot include inbound or outbound foreign keys that extend outside the specified set of tables.

The following parameters are available when you specify the Export action with SqlPackage.exe :

 

Parameter Short Form Required? Description

/TargetFile :{string}

/tf :{string}

Y

Specifies the file path where the exported .bacpac file will be generated. The file path must be an existing directory, and the string must include the file name, including the .bacpac extension. The path name cannot exceed the Windows file path limitation of 256 characters.

Properties

The following table specifies the command line properties that SqlPackage.exe provides for exporting a BACPAC file.

 

Property Default Description

/p:TableData ={string}

N/A

This property is used to specify individual user tables to include as part of an Export action. Tables are specified using the two-part table name.

/p: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.

Import Parameters and Properties

A SqlPackage.exe Import action imports the schema and table data from a BACPAC package - .bacpac file – into a new or empty database in SQL Server or Windows Azure SQL Database. At the time of the import operation to an existing database, the target database cannot contain any user-defined schema objects.

Parameters

Source: Only the /SourceFile :{string} parameter is valid for Import, where {string} is the file path on disk where the source .bacpac file is located.

Target: All Target Parameters are valid for Import except /TargetFile .

The following other target parameters are available when you specify an Importaction with SqlPackage.exe :

 

Parameter Short Form Required? Description

/SourceFile :{string}

/sf :{string}

Y

Specifies the file path where the source file is located. The string must include the target file name, including the .bacpac extension. The path name cannot exceed the Windows file path limitation of 256 characters.

Properties

The following table specifies the command line properties that SqlPackage.exe provides for exporting a BACPAC package:

 

Property Default Description

/p: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.

DeployReport Parameters and Properties

A SqlPackage.exe report action creates an XML report of the changes that would be made by a publish action.

Parameters

Source: Only /SourceFile : parameter is valid for DeployReport.

Target: All Target Parameters are valid for DeployReport.

The following other target parameters are available when you specify a DeployReport action with SqlPackage.exe :

 

Parameter Short Form Required? Description

/OutputPath :{string}

/op :{string}

Y

Specifies the file path where the XML output files are generated.

Properties

The following table specifies the command line properties that SqlPackage.exe provides for changing the effect of a DeployReport action.

 

Property Default Description

/p:AdditionalDeploymentContributorArguments ={ String }

N/A

Specifies additional deployment contributor arguments for the deployment contributors. This should be a semi-colon delimited list of values.

/p:AdditionalDeploymentContributors ={ String }

N/A

Specifies additional deployment contributors which should run when the dacpac is deployed. This should be a semi-colon delimited list of fully qualified build contributor names or IDs.

/p:AllowDropBlockingAssemblies ={ True | False }

False

This property is used by SqlClr deployment to cause any blocking assemblies to be dropped as part of the deployment plan. By default, any blocking/referencing assemblies will block an assembly update if the referencing assembly needs to be dropped.

/p:AllowIncompatiblePlatform ={ True | False }

False

Specifies whether the report action should go forward despite potentially incompatible SQL Server platforms.

/p:BackupDatabaseBeforeChanges ={ True | False }

False

Backs up the database before deploying any changes.

/p:BlockOnPossibleDataLoss ={ True | False }

True

Specifies that the publish episode should be terminated if there is a possibility of data loss resulting from the publish. operation.

/p:BlockWhenDriftDetected ={ True | False }

True

Specifies whether to block updating a database whose schema no longer matches its registration or is unregistered.

/p:CommentOutSetVarDeclarations ={ True | False }

False

Specifies whether the declaration of SETVAR variables should be commented out in the generated publish script. You might choose to do this if you plan to specify the values on the command line when you publish by using a tool such as SQLCMD.EXE.

/p: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.

/p: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.

/p:DeployDatabaseInSingleUserMode ={ True | False }

False

If true, the database is set to Single User Mode before deploying.

/p: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.

/p:DoNotAlterChangeDataCaptureObjects ={ True | False }

False

If true, Change Data Capture objects are not altered.

/p:DoNotAlterReplicatedObjects ={ True | False }

True

Specifies whether objects that are replicated are identified during verification.

/p:DropConstraintsNotInSource ={ True | False }

True

Specifies whether constraints that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

/p:DropDmlTriggersNotInSource ={ True | False }

True

Specifies whether DML triggers that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

/p:DropExtendedPropertiesNotInSource ={ True | False }

True

Specifies whether extended properties that do not exist in the databasesnapshot (.dacpac) file will be dropped from the target database when youpublish to a database.

/p:DropIndexesNotInSource ={ True | False }

True

Specifies whether indexes that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

/p:DropObjectsNotInSource ={ True | False }

False

Specifies whether objectsthat do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

/p:DropPermissionsNotInSource ={ True | False }

False

Specifies whether permissions that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish updates to a database.

/p:DropRoleMembersNotInSource ={ True | False }

False

Specifies whether role members that are not defined in the database snapshot (.dacpac) file will be dropped from the target database when you publish updates to a database.

/p:GenerateSmartDefaults ={ True | False }

False

Automatically provides a default value when updating a table that contains data with a column that does not allow null values.

/p:IgnoreAnsiNulls ={ True | False }

False

Specifies whether to ignore or update differences in the ANSI NULLS setting when you publish to a database.

/p: IgnoreAuthorizer ={ True | False }

False

Specifies whether to ignore or update differences in the Authorizer when you publish to a database.

/p:IgnoreColumnCollation ={ True | False }

False

Specifies whether to ignore or update differences in column collation when you publish to a database.

/p:IgnoreComments ={ True | False }

False

Specifies whether to ignore or update differences in comments order when you publish to a database.

/p: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.

/p: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.

/p: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.

/p:IgnoreDefaultSchema ={ True | False }

False

Specifies whether to ignore or update differences in the default schema when you publish to a database.

/p:IgnoreDmlTriggerOrder ={ True | False }

False

Specifies whether to ignore or update differences in the order of DML triggers when you publish to a database.

/p: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.

/p:IgnoreExtendedProperties ={ True | False }

False

Specifies whether to ignore or update differences in extended properties when you publish to a database.

/p: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.

/p:IgnoreFilegroupPlacement ={ True | False }

True

Specifies whether to ignore or update differences in the placement of FILEGROUP s when you publish to a database.

/p:IgnoreFileSize ={ True | False }

True

Specifies whether to ignore or update differences in file sizes when you publish to a database.

/p:IgnoreFillFactor ={ True | False }

True

Specifies whether to ignore or update differences in fill factors when you publish to a database.

/p: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.

/p:IgnoreIdentitySeed ={ True | False }

False

Specifies whether to ignore or update differences in the seed for an identity column when you publish to a database.

/p:IgnoreIncrement ={ True | False }

False

Specifies whether to ignore or update differences in the increment for an identity column when you publish to a database.

/p:IgnoreIndexOptions ={ True | False }

False

Specifies whether to ignore or update differences in the index options when you publish to a database.

/p:IgnoreIndexPadding ={ True | False }

True

Specifies whether to ignore or update differences in the index padding when you publish to a database.

/p:IgnoreKeywordCasing ={ True | False }

True

Specifies whether to ignore or update differences in the keyword casing when you publish to a database.

/p:IgnoreLoginSids ={ True | False }

True

Specifies whether to ignore or update differences in the security identifier (SID) when you publish to a database.

/p:IgnoreNotForReplication ={ True | False }

False

Specifies whether to ignore or update the not-for-replication setting when you publish to a database.

/p:IgnoreObjectPlacementOnPartitionScheme ={ True | False }

True

Specifies whether to ignore or update an object’s placement on a partition scheme when you publish to a database.

/p:IgnorePartitionSchemes ={ True | False }

False

Specifies whether to ignore or update differences in partition schemes and functions when you publish to a database.

/p:IgnorePermissions ={ True | False }

False

Specifies whether to ignore or update differences in permissions when you publish to a database.

/p:IgnoreQuotedIdentifiers ={ True | False }

False

Specifies whether to ignore or update differences in quoted identifier settings when you publish to a database.

/p:IgnoreRoleMembership ={ True | False }

False

Specifies whether differences in the role membership of logins should be ignored or updated when you publish to a database.

/p:IgnoreRouteLifetime ={ True | False }

True

Specifies whether to ignore or update differences in the role memberships of logins when you publish to a database.

/p:IgnoreSemicolonBetweenStatements ={ True | False }

True

Specifies whether to ignore or update differences in semicolons between Transact-SQL statements when you publish to a database.

/p:IgnoreTableOptions ={ True | False }

False

Specifies whether to ignore or update differences in table options when you publish to a database.

/p:IgnoreUserSettingsObjects ={ True | False }

False

Specifies whether to ignore or update differences in user setting options when you publish to a database.

/p:IgnoreWhitespace ={ True | False }

True

Specifies whether to ignore or update differences in whitespace when you publish to a database.

/p: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.

/p: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.

/p:IncludeCompositeObjects ={ True | False }

False

Specifies whether to include all composite elements as part of a single publish operation.

/p:IncludeTransactionalScripts ={ True | False }

False

Specifies whether to use transactional statements wherever possible when you publish to a database.

/p: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.

/p:PopulateFilesOnFilegroups ={ True | False }

True

Specifies whether a new file is also created when you create a new FileGroup in the target database.

/p:RegisterDataTierApplication ={ True | False }

False

Specifies whether the schema is registered with the database server.

/p:RunDeploymentPlanExecutors ={ True | False }

False

Specifies whether DeploymentPlanExecutor contributors should be run when other operations are executed.

/p:ScriptDatabaseCollation ={ True | False }

False

Specifies whether to ignore or update differences in database collation when you publish to a database.

/p:ScriptDatabaseCompatibility ={ True | False }

True

Specifies whether to ignore or update differences in database compatibility when you publish to a database.

/p:ScriptDatabaseOptions ={ True | False }

True

Specifies whether to set or update target database properties when you publish to a database.

/p:ScriptFileSize ={ True | False }

False

Controls whether size is specified when adding a file to a filegroup.

/p: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.

/p: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.

/p:ScriptRefreshModule ={ True | False }

True

Specifies whether to include refresh statements at the end of the publish script.

/p:Storage ={ File | Memory }

Memory

Specifies how elements are stored when building the database model. For performance reasons the default is InMemory. For very large databases, File backed storage is required.

/p: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.

/p: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).

/p:VerifyCollationCompatibility ={ True | False }

True

Specifies whether collation compatibility is verified.

/p: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.

DriftReport Parameters

A SqlPackage.exe report action creates an XML report of the changes that have been made to the registered database since it was last registered.

Parameters

Source: N/A

Target: All Target Parameters are valid for DriftReport except for TargetFile.

The following target parameter is available when you specify a DriftReport action with SqlPackage.exe :

 

Parameter Short Form Required? Description

/OutputPath: { string }

/op: { string }

Y

Specifies the file path where the output files are generated.

Script Parameters and Properties

A SqlPackage.exe script action creates a Transact-SQL incremental update script that updates the schema of a target database to match the schema of a source database.

Parameters

Source: Only /SourceFile: parameter is valid for Script.

Target: All Target Parameters are valid for Script.

The following other target parameter is available when you specify a Script action with SqlPackage.exe :

 

Parameter Short Form Required? Description

/OutputPath: { string }

/op: { string }

Y

Specifies the file path where the output files are generated.

Properties

The following table specifies the command line properties that SqlPackage.exe provides for changing the effect of a Script action

 

Property Default Description

/p:AdditionalDeploymentContributorArguments ={ String }

N/A

Specifies additional deployment contributor arguments for the deployment contributors. This should be a semi-colon delimited list of values.

/p:AdditionalDeploymentContributors ={ String }

N/A

Specifies additional deployment contributors which should run when the dacpac is deployed. This should be a semi-colon delimited list of fully qualified build contributor names or IDs.

/p:AllowDropBlockingAssemblies ={ True | False }

False

This property is used by SqlClr deployment to cause any blocking assemblies to be dropped as part of the deployment plan. By default, any blocking/referencing assemblies will block an assembly update if the referencing assembly needs to be dropped.

/p:AllowIncompatiblePlatform ={ True | False }

False

Specifies whether the report action should go forward despite potentially incompatible SQL Server platforms.

/p:BackupDatabaseBeforeChanges ={ True | False }

False

Backs up the database before deploying any changes.

/p:BlockOnPossibleDataLoss ={ True | False }

True

Specifies that the publish episode should be terminated if there is a possibility of data loss resulting from the publish operation.

/p:BlockWhenDriftDetected ={ True | False }

True

Specifies whether to block updating a database whose schema no longer matches its registration or is unregistered.

/p:CommentOutSetVarDeclarations ={ True | False }

False

Specifies whether the declaration of SETVAR variables should be commented out in the generated publish script. You might choose to do this if you plan to specify the values on the command line when you publish by using a tool such as SQLCMD.EXE.

/p: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 optionis set, the target database's (or server's) collation should be used.

/p: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.

/p:DeployDatabaseInSingleUserMode ={ True | False }

False

If true, the database is set to Single User Mode before deploying.

/p: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.

/p:DoNotAlterChangeDataCaptureObjects ={ True | False }

True

If true, Change Data Capture objects are not altered.

/p:DoNotAlterReplicatedObjects ={ True | False }

True

Specifies whether objects that are replicated are identified during verification.

/p:DropConstraintsNotInSource ={ True | False }

True

Specifies whether constraints that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

/p:DropDmlTriggersNotInSource ={ True | False }

True

Specifies whether DML triggers that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

/p:DropExtendedPropertiesNotInSource ={ True | False }

True

Specifies whether extended properties that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

/p:DropIndexesNotInSource ={ True | False }

True

Specifies whether indexes that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database.

/p: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.

/p:DropPermissionsNotInSource ={ True | False }

False

Specifies whether permissions that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish updates to a database.

/p:DropRoleMembersNotInSource ={ True | False }

False

Specifies whether role members that are not defined in the database snapshot (.dacpac) file will be dropped from the target database when you publish updates to a database.

/p:GenerateSmartDefaults ={ True | False }

False

Automatically provides a default value when updating a table that contains data with a column that does not allow null values.

/p:IgnoreAnsiNulls ={ True | False }

False

Specifies whether to ignore or update differences in the ANSI NULLS setting when you publish to a database.

/p: IgnoreAuthorizer ={ True | False }

False

Specifies whether to ignore or update differences in the Authorizer when you publish to a database.

/p:IgnoreColumnCollation ={ True | False }

False

Specifies whether to ignore or update differences in column collation when you publish to a database.

/p:IgnoreComments ={ True | False }

False

Specifies whether to ignore or update differences in comments order when you publish to a database.

/p: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.

/p: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.

/p: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.

/p:IgnoreDefaultSchema ={ True | False }

False

Specifies whether to ignore or update differences in the default schema when you publish to a database.

/p:IgnoreDmlTriggerOrder ={ True | False }

False

Specifies whether to ignore or update differences in the order of DML triggers when you publish to a database.

/p: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.

/p:IgnoreExtendedProperties ={ True | False }

False

Specifies whether to ignore or update differences in extended properties when you publish to a database.

/p: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.

/p:IgnoreFilegroupPlacement ={ True | False }

True

Specifies whether to ignore or update differences in the placement of FILEGROUP s when you publish to a database.

/p:IgnoreFileSize ={ True | False }

True

Specifies whether to ignore or update differences in file sizes when you publish to a database.

/p:IgnoreFillFactor ={ True | False }

True

Specifies whether to ignore or update differences in fill factors when you publish to a database.

/p: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.

/p:IgnoreIdentitySeed ={ True | False }

False

Specifies whether to ignore or update differences in the seed for an identity column when you publish to a database.

/p:IgnoreIncrement ={ True | False }

False

Specifies whether to ignore or update differences in the increment for an identity column when you publish to a database.

/p:IgnoreIndexOptions ={ True | False }

False

Specifies whether to ignore or update differences in the index options when you publish to a database.

/p:IgnoreIndexPadding ={ True | False }

True

Specifies whether to ignore or update differences in the index padding when you publish to a database.

/p:IgnoreKeywordCasing ={ True | False }

True

Specifies whether to ignore or update differences in the keyword casing when you publish to a database.

/p:IgnoreLoginSids ={ True | False }

True

Specifies whether to ignore or update differences in the security identifier (SID) when you publish to a database.

/p:IgnoreLockHintsOnIndexes ={ True | False }

False

Specifies whether differences in the lock hints on indexes should be ignored or updated when you publish to a database.

/p:IgnoreNotForReplication ={ True | False }

False

Specifies whether to ignore or update the not-for-replication setting when you publish to a database.

/p:IgnoreObjectPlacementOnPartitionScheme ={ True | False }

True

Specifies whether to ignore or update an object’s placement on a partition scheme when you publish to a database.

/p:IgnorePartitionSchemes ={ True | False }

False

Specifies whether to ignore or update differences in partition schemes and functions when you publish to a database.

/p:IgnorePermissions ={ True | False }

False

Specifies whether to ignore or update differences in permissions when you publish to a database.

/p:IgnoreQuotedIdentifiers ={ True | False }

False

Specifies whether to ignore or update differences in quoted identifier settings when you publish to a database.

/p:IgnoreRoleMembership ={ True | False }

False

Specifies whether differences in the role membership of logins should be ignored or updated when you publish to a database.

/p:IgnoreRouteLifetime ={ True | False }

True

Specifies whether to ignore or update differences in the role memberships of logins when you publish to a database.

/p:IgnoreSemicolonBetweenStatements ={ True | False }

True

Specifies whether to ignore or update differences in semicolons between Transact-SQL statements when you publish to a database.

/p:IgnoreTableOptions ={ True | False }

False

Specifies whether to ignore or update differences in table options when you publish to a database.

/p:IgnoreUserSettingsObjects ={ True | False }

False

Specifies whether to ignore or update differences in user setting options when you publish to a database.

/p:IgnoreWhitespace ={ True | False }

True

Specifies whether to ignore or update differences in whitespace when you publish to a database.

/p: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.

/p: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.

/p:IncludeCompositeObjects ={ True | False }

False

Specifies whether to include all composite elements as part of a single publish operation.

/p:IncludeTransactionalScripts ={ True | False }

False

Specifies whether to use transactional statements wherever possible when you publish to a database.

/p: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.

/p:PopulateFilesOnFilegroups ={ True | False }

True

Specifies whether a new file is also created when you create a new FileGroup in the target database.

/p:RegisterDataTierApplication ={ True | False }

False

Specifies whether the schema is registered with the database server.

/p:RunDeploymentPlanExecutors ={ True | False }

False

Specifies whether DeploymentPlanExecutor contributors should be run when other operations are executed.

/p:ScriptDatabaseCollation ={ True | False }

False

Specifies whether to ignore or update differences in database collation when you publish to a database.

/p:ScriptDatabaseCompatibility ={ True | False }

True

Specifies whether to ignore or update differences in database compatibility when you publish to a database.

/p:ScriptDatabaseOptions ={ True | False }

True

Specifies whether to set or update target database properties when you publish to a database.

/p:ScriptFileSize ={ True | False }

False

Controls whether size is specified when adding a file to a filegroup.

/p: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.

/p: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.

/p:ScriptRefreshModule ={ True | False }

True

Specifies whether to include refresh statements at the end of the publish script.

/p:Storage ={ File | Memory }

Memory

Specifies how elements are stored when building the database model. For performance reasons the default is InMemory. For very large databases, File backed storage is required.

/p: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.

/p:UnmodifiableObjectWarnings ={ True | False }

True

Specifies whether warnings should be generated when differences are found in objects that cannot be modified, for example, if the file size or file paths were different for a file.

/p:VerifyCollationCompatibility ={ True | False }

True

Specifies whether collation compatibility is verified.

/p:VerifyDeployment ={ True | False }

True

Specifies whether checks should be performed before publishing that will stop the publish action if issues are present that might block successful publishing. For example, your publish action might stop if you have foreign keys on the target database that do not exist in the database project, and that will cause errors when you publish.

See Also



Show:
© 2014 Microsoft