| title | SqlPackage.exe |
|---|---|
| description | Learn how to automate database development tasks with SqlPackage.exe. View examples and available parameters, properties, and SQLCMD variables. |
| ms.prod | sql |
| ms.prod_service | sql-tools |
| ms.technology | tools-other |
| ms.topic | conceptual |
| ms.assetid | 198198e2-7cf4-4a21-bda4-51b36cb4284b |
| author | dzsquared |
| ms.author | drskwier |
| ms.reviewer | maghan |
| ms.date | 1/25/2022 |
SqlPackage.exe is a command-line utility that automates the following database development tasks:
-
Version: Returns the build number of the SqlPackage application. Added in version 18.6.
-
Extract: Creates a data-tier application (.dacpac) file containing the schema or schema and user data from a connected 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 creates it. Otherwise, an existing database is updated.
-
Export: Exports a connected SQL database - including database schema and user data - to a BACPAC file (.bacpac).
-
Import: Imports the schema and table data from a BACPAC file into a new user database.
-
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 tool allows you to specify these actions along with action-specific parameters and properties.
Download the latest version. For details about the latest release, see the release notes.
SqlPackage.exe initiates the actions specified using the parameters, properties, and SQLCMD variables specified on the command line.
SqlPackage {parameters}{properties}{SQLCMD Variables}
Generate a comparison between databases by using .dacpac files with a SQL script output
Start by creating a .dacpac file of your latest database changes:
sqlpackage.exe /TargetFile:"C:\sqlpackageoutput\output_current_version.dacpac" /Action:Extract /SourceServerName:"." /SourceDatabaseName:"Contoso.Database"
Create a .dacpac file of your database target (that has no changes):
sqlpackage.exe /TargetFile:"C:\sqlpackageoutput\output_target.dacpac" /Action:Extract /SourceServerName:"." /SourceDatabaseName:"Contoso.Database"
Create a SQL script that generates the differences of two .dacpac files:
sqlpackage.exe /Action:Script /SourceFile:"C:\sqlpackageoutput\output_current_version.dacpac" /TargetFile:"C:\sqlpackageoutput\output_target.dacpac" /TargetDatabaseName:"Contoso.Database" /OutputPath:"C:\sqlpackageoutput\output.sql"
Displays the sqlpackage version as a build number. Can be used in interactive prompts as well as in automated pipelines.
sqlpackage.exe /Version
You can display sqlpackage usage information by using /? or /help:True.
sqlpackage.exe /?
For parameter and property information specific to a particular action, use the help parameter in addition to that action's parameter.
sqlpackage.exe /Action:Publish /?
Commands that return the following exit codes:
- 0 = success
- non-zero = failure
Some parameters are shared between the SqlPackage actions. Below is a table summarizing the parameters, for more information click into the specific action pages.
| Parameter | Short Form | Extract | Publish | Export | Import | DeployReport | DriftReport | Script |
|---|---|---|---|---|---|---|---|---|
| /AccessToken: | /at | x | x | x | x | x | x | x |
| /ClientId: | /cid | x | ||||||
| /DeployScriptPath: | /dsp | x | x | |||||
| /DeployReportPath: | /drp | x | x | |||||
| /Diagnostics: | /d | x | x | x | x | x | x | x |
| /DiagnosticsFile: | /df | x | x | x | x | x | x | x |
| /MaxParallelism: | /mp | x | x | x | x | x | x | x |
| /OutputPath: | /op | x | x | x | ||||
| /OverwriteFiles: | /of | x | x | x | x | x | x | |
| /Profile: | /pr | x | x | x | ||||
| /Properties: | /p | x | x | x | x | x | x | |
| /Quiet: | /q | x | x | x | x | x | x | x |
| /Secret: | /secr | x | ||||||
| /SourceConnectionString: | /scs | x | x | x | x | x | ||
| /SourceDatabaseName: | /sdn | x | x | x | x | x | ||
| /SourceEncryptConnection: | /sec | x | x | x | x | x | ||
| /SourceFile: | /sf | x | x | x | x | |||
| /SourcePassword: | /sp | x | x | x | x | x | ||
| /SourceServerName: | /ssn | x | x | x | x | x | ||
| /SourceTimeout: | /st | x | x | x | x | x | ||
| /SourceTrustServerCertificate: | /stsc | x | x | x | x | x | ||
| /SourceUser: | /su | x | x | x | x | x | ||
| /TargetConnectionString: | /tcs | x | x | x | x | |||
| /TargetDatabaseName: | /tdn | x | x | x | x | x | ||
| /TargetEncryptConnection: | /tec | x | x | x | x | x | ||
| /TargetFile: | /tf | x | x | x | x | |||
| /TargetPassword: | /tp | x | x | x | x | x | ||
| /TargetServerName: | /tsn | x | x | x | x | x | ||
| /TargetTimeout: | /tt | x | x | x | x | x | ||
| /TargetTrustServerCertificate: | /ttsc | x | x | x | x | x | ||
| /TargetUser: | /tu | x | x | x | x | x | ||
| /TenantId: | /tid | x | x | x | x | x | x | x |
| /UniversalAuthentication: | /ua | x | x | x | x | x | x | x |
| /Variables: | /v | x | x |
Some properties are shared between the SqlPackage actions. Below is a table summarizing the properties, for more information click into the specific action pages.
| Property | Extract | Publish | Export | Import | DeployReport | Script |
|---|---|---|---|---|---|---|
| AdditionalDeploymentContributorArguments=(STRING) | x | x | x | |||
| AdditionalDeploymentContributors=(STRING) | x | x | x | |||
| AdditionalDeploymentContributorPaths=(STRING) | x | x | x | |||
| AllowDropBlockingAssemblies=(BOOLEAN) | x | x | x | |||
| AllowIncompatiblePlatform=(BOOLEAN) | x | x | x | |||
| AllowUnsafeRowLevelSecurityDataMovement=(BOOLEAN) | x | x | x | |||
| AzureSharedAccessSignatureToken=(STRING) | x | |||||
| AzureStorageBlobEndpoint=(STRING) | x | x | ||||
| AzureStorageContainer=(STRING) | x | x | ||||
| AzureStorageKey=(STRING) | x | x | ||||
| AzureStorageRootPath=(STRING) | x | x | ||||
| BackupDatabaseBeforeChanges=(BOOLEAN) | x | x | x | |||
| BlockOnPossibleDataLoss=(BOOLEAN 'True') | x | x | x | |||
| BlockWhenDriftDetected=(BOOLEAN 'True') | x | x | x | |||
| CommandTimeout=(INT32 '60') | x | x | x | x | x | x |
| CommentOutSetVarDeclarations=(BOOLEAN) | x | x | x | |||
| CompareUsingTargetCollation=(BOOLEAN) | x | x | x | |||
| CompressionOption=(ENUM 'Normal') | x | x | ||||
| CreateNewDatabase=(BOOLEAN) | x | x | x | |||
| DacApplicationDescription=(STRING) | x | |||||
| DacApplicationName=(STRING) | x | |||||
| DacMajorVersion=(INT32 '1') | x | |||||
| DacMinorVersion=(INT32 '0') | x | |||||
| DatabaseEdition=(ENUM 'Default') | x | x | x | x | ||
| DatabaseLockTimeout=(INT32 '60') | x | x | x | x | x | |
| DatabaseMaximumSize=(INT32) | x | x | x | x | ||
| DatabaseServiceObjective=(STRING) | x | x | x | x | ||
| DeployDatabaseInSingleUserMode=(BOOLEAN) | x | x | x | |||
| DisableAndReenableDdlTriggers=(BOOLEAN 'True') | x | x | x | |||
| DisableIndexesForDataPhase=(BOOLEAN 'True') | x | |||||
| DoNotAlterChangeDataCaptureObjects=(BOOLEAN 'True') | x | x | x | |||
| DoNotAlterReplicatedObjects=(BOOLEAN 'True') | x | x | x | |||
| DoNotDropObjectType=(STRING) | x | x | x | |||
| DoNotDropObjectTypes=(STRING) | x | x | x | |||
| DropConstraintsNotInSource=(BOOLEAN 'True') | x | x | x | |||
| DropDmlTriggersNotInSource=(BOOLEAN 'True') | x | x | x | |||
| DropExtendedPropertiesNotInSource=(BOOLEAN 'True') | x | x | x | |||
| DropIndexesNotInSource=(BOOLEAN 'True') | x | x | x | |||
| DropObjectsNotInSource=(BOOLEAN) | x | x | x | |||
| DropPermissionsNotInSource=(BOOLEAN) | x | x | x | |||
| DropRoleMembersNotInSource=(BOOLEAN) | x | x | x | |||
| DropStatisticsNotInSource=(BOOLEAN 'True') | x | x | x | |||
| EnclaveAttestationProtocol=(STRING) | x | |||||
| EnclaveAttestationUrl=(STRING) | x | |||||
| ExcludeObjectType=(STRING) | x | x | x | |||
| ExcludeObjectTypes=(STRING) | x | x | x | |||
| ExtractAllTableData=(BOOLEAN) | x | |||||
| ExtractApplicationScopedObjectsOnly=(BOOLEAN 'True') | x | |||||
| ExtractReferencedServerScopedElements=(BOOLEAN 'True') | x | |||||
| ExtractUsageProperties=(BOOLEAN) | x | |||||
| GenerateSmartDefaults=(BOOLEAN) | x | x | x | |||
| IgnoreAnsiNulls=(BOOLEAN 'True') | x | x | x | |||
| IgnoreAuthorizer=(BOOLEAN) | x | x | x | |||
| IgnoreColumnCollation=(BOOLEAN) | x | x | ||||
| IgnoreColumnOrder=(BOOLEAN) | x | x | x | |||
| IgnoreComments=(BOOLEAN) | x | x | x | |||
| IgnoreCryptographicProviderFilePath=(BOOLEAN 'True') | x | x | x | |||
| IgnoreDdlTriggerOrder=(BOOLEAN) | x | x | x | |||
| IgnoreDdlTriggerState=(BOOLEAN) | x | x | x | |||
| IgnoreDefaultSchema=(BOOLEAN) | x | x | x | |||
| IgnoreDmlTriggerOrder=(BOOLEAN) | x | x | x | |||
| IgnoreDmlTriggerState=(BOOLEAN) | x | x | x | |||
| IgnoreExtendedProperties=(BOOLEAN) | x | x | x | x | ||
| IgnoreFileAndLogFilePath=(BOOLEAN 'True') | x | x | x | |||
| IgnoreFilegroupPlacement=(BOOLEAN 'True') | x | x | x | |||
| IgnoreFileSize=(BOOLEAN 'True') | x | x | x | |||
| IgnoreFillFactor=(BOOLEAN 'True') | x | x | x | |||
| IgnoreFullTextCatalogFilePath=(BOOLEAN 'True') | x | x | x | |||
| IgnoreIdentitySeed=(BOOLEAN) | x | x | x | |||
| IgnoreIncrement=(BOOLEAN) | x | x | x | |||
| IgnoreIndexOptions=(BOOLEAN) | x | x | x | |||
| IgnoreIndexPadding=(BOOLEAN 'True') | x | x | x | |||
| IgnoreKeywordCasing=(BOOLEAN 'True') | x | x | x | |||
| IgnoreLockHintsOnIndexes=(BOOLEAN) | x | x | x | |||
| IgnoreLoginSids=(BOOLEAN 'True') | x | x | x | |||
| IgnoreNotForReplication=(BOOLEAN) | x | x | x | |||
| IgnoreObjectPlacementOnPartitionScheme=(BOOLEAN 'True') | x | x | x | |||
| IgnorePartitionSchemes=(BOOLEAN) | x | x | x | |||
| IgnorePermissions=(BOOLEAN 'True') | x | x | x | x | ||
| IgnoreQuotedIdentifiers=(BOOLEAN 'True') | x | x | x | |||
| IgnoreRoleMembership=(BOOLEAN) | x | x | x | |||
| IgnoreRouteLifetime=(BOOLEAN 'True') | x | x | x | |||
| IgnoreSemicolonBetweenStatements=(BOOLEAN 'True') | x | x | x | |||
| IgnoreTableOptions=(BOOLEAN) | x | x | x | |||
| IgnoreTablePartitionOptions=(BOOLEAN) | x | x | x | |||
| IgnoreUserLoginMappings=(BOOLEAN) | x | |||||
| IgnoreUserSettingsObjects=(BOOLEAN) | x | x | x | |||
| IgnoreWhitespace=(BOOLEAN 'True') | x | x | x | |||
| IgnoreWithNocheckOnCheckConstraints=(BOOLEAN) | x | x | ||||
| IgnoreWithNocheckOnForeignKeys=(BOOLEAN) | x | x | ||||
| ImportContributorArguments=(STRING) | x | |||||
| ImportContributors=(STRING) | x | |||||
| ImportContributorPaths=(STRING) | x | |||||
| IncludeCompositeObjects=(BOOLEAN) | x | x | x | |||
| IncludeTransactionalScripts=(BOOLEAN) | x | x | x | |||
| LongRunningCommandTimeout=(INT32) | x | x | x | x | x | x |
| NoAlterStatementsToChangeClrTypes=(BOOLEAN) | x | x | x | |||
| PopulateFilesOnFileGroups=(BOOLEAN 'True') | x | x | x | |||
| RebuildIndexesOfflineForDataPhase=(BOOLEAN 'False') | x | |||||
| RegisterDataTierApplication=(BOOLEAN) | x | x | x | |||
| RunDeploymentPlanExecutors=(BOOLEAN) | x | x | x | |||
| ScriptDatabaseCollation=(BOOLEAN) | x | x | x | |||
| ScriptDatabaseCompatibility=(BOOLEAN) | x | x | x | |||
| ScriptDatabaseOptions=(BOOLEAN 'True') | x | x | x | |||
| ScriptDeployStateChecks=(BOOLEAN) | x | x | x | |||
| ScriptFileSize=(BOOLEAN) | x | x | x | |||
| ScriptNewConstraintValidation=(BOOLEAN 'True') | x | x | x | |||
| ScriptRefreshModule=(BOOLEAN 'True') | x | x | x | |||
| Storage=({File|Memory}) | x | x | x | x | x | x |
| TableData=(STRING) | x | x | ||||
| TargetEngineVersion=(ENUM 'Latest') | x | |||||
| TempDirectoryForTableData=(STRING) | x | x | ||||
| TreatVerificationErrorsAsWarnings=(BOOLEAN) | x | x | x | |||
| UnmodifiableObjectWarnings=(BOOLEAN 'True') | x | x | x | |||
| VerifyCollationCompatibility=(BOOLEAN 'True') | x | x | x | |||
| VerifyDeployment=(BOOLEAN 'True') | x | x | x | |||
| VerifyExtraction=(BOOLEAN) | x | |||||
| VerifyFullTextDocumentTypesSupported=(BOOLEAN) | x |
- Learn more about SqlPackage Extract
- Learn more about SqlPackage Publish
- Learn more about SqlPackage Export
- Learn more about SqlPackage Import