SqlPackage
SqlPackage is a command-line utility that automates the following database development tasks by exposing some of the public Data-Tier Application Framework (DacFx) APIs:
Version: Returns the build number of the SqlPackage application.
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 doesn't 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 representing the changes that a publish action would take.
DriftReport: Creates an XML report representing the changes applied 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 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.
Note
While Microsoft Entra ID is the new name for Azure Active Directory (Azure AD), to prevent disrupting existing environments, Azure AD still remains in some hardcoded elements such as UI fields, connection providers, error codes, and cmdlets. In this article, the two names are interchangeable.
Command-Line Syntax
SqlPackage initiates the actions specified using the parameters, properties, and SQLCMD variables specified on the command line.
SqlPackage {parameters} {properties} {SQLCMD variables}
Exit codes
SqlPackage commands return the following exit codes:
- 0 = success
- nonzero = failure
Usage example
Further examples are available on the individual action pages.
Creating a .dacpac file of the current database schema:
SqlPackage /TargetFile:"C:\sqlpackageoutput\output_current_version.dacpac" /Action:Extract /SourceServerName:"." /SourceDatabaseName:"Contoso.Database"
Parameters
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 |
Properties
SqlPackage actions support a large number of properties to modify the default behavior of an action. For more information click into the specific action pages.
Utility commands
Version
Displays the sqlpackage version as a build number. Can be used in interactive prompts and in automated pipelines.
SqlPackage /Version
Help
You can display SqlPackage usage information by using /?
or /help:True
.
SqlPackage /?
For parameter and property information specific to a particular action, use the help parameter in addition to that action's parameter.
SqlPackage /Action:Publish /?
Authentication
SqlPackage authenticates using methods available in SqlClient. Configuring the authentication type can be accomplished via the connection string parameters for each SqlPackage action (/SourceConnectionString
and /TargetConnectionString
) or through individual parameters for connection properties. The following authentication methods are supported in a connection string:
- SQL Server authentication
- Active Directory (Windows) authentication
- Microsoft Entra authentication
- Username/password
- Integrated authentication
- Universal authentication
- Managed identity
- Service principal
Managed identity
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
In automated environments, Microsoft Entra managed identity is the recommended authentication method. This method doesn't require passing credentials to SqlPackage at runtime. When the managed identity is configured for the environment where the SqlPackage action is run, the SqlPackage action can use that identity to authenticate to Azure SQL. For more information on configuring a managed identity for your environment, see the Managed identity documentation.
An example connection string using system-assigned managed identity is:
Server=sampleserver.database.windows.net; Authentication=Active Directory Managed Identity; Database=sampledatabase;
Environment variables
Connection pooling
Connection pooling can be enabled for all connections made by SqlPackage by setting the CONNECTION_POOLING_ENABLED
environment variable to True
. This setting is recommended for operations with Microsoft Entra username and password connections to avoid throttling by the Microsoft Authentication Library (MSAL).
Temporary files
During SqlPackage operations, the table data is written to temporary files before compression or after decompression. For large databases these temporary files can take up a significant amount of disk space but their location can be specified. The export and extract operations include an optional property to specify /p:TempDirectoryForTableData
to override the SqlPackage's default value.
The .NET API GetTempPath is used to determine the default value within SqlPackage.
For Windows, the following environment variables are checked in the following order and the first path that exists is used:
- The path specified by the
TMP
environment variable. - The path specified by the
TEMP
environment variable. - The path specified by the
USERPROFILE
environment variable. - The Windows directory.
For Linux and macOS, if the path isn't specified in the TMPDIR
environment variable, the default path /tmp/
is used.
SqlPackage and database users
Contained database users are included in SqlPackage operations. However, the password portion of the definition is set to a randomly generated string by SqlPackage, the existing value isn't transferred. It's recommended that the new user's password is reset to a secure value following the import of a .bacpac
or the deployment of a .dacpac
. In an automated environment the password values can be retrieved from a secure keystore, such as Azure Key Vault, in a step following SqlPackage.
Usage data collection
SqlPackage contains Internet-enabled features that can collect and send anonymous feature usage and diagnostic data to Microsoft.
SqlPackage may collect standard computer, use, and performance information that may be transmitted to Microsoft and analyzed to improve the quality, security, and reliability of SqlPackage.
SqlPackage doesn't collect user specific or personal information. To help approximate a single user for diagnostic purposes, SqlPackage generates a random GUID for each computer it runs on and use that value for all events it sends.
For details, see the Microsoft Privacy Statement, and SQL Server Privacy supplement.
Disable telemetry reporting
To disable telemetry collection and reporting, update the environment variable DACFX_TELEMETRY_OPTOUT
to true
or 1
.
Support
The DacFx library and the SqlPackage CLI tool have adopted the Microsoft Modern Lifecycle Policy. All security updates, fixes, and new features are released only in the latest point version of the major version. Maintaining your DacFx or SqlPackage installations to the current version helps ensure that you receive all applicable bug fixes in a timely manner.
Get help with SqlPackage, submit feature requests, and report issues in the DacFx GitHub repository.
Supported SQL offerings
SqlPackage and DacFx support all supported SQL versions at time of the SqlPackage/DacFx release. For example, a SqlPackage release on January 14 2022 supports all supported versions of SQL in January 14 2022. For more on SQL support policies, see the SQL support policy.
Next steps
- Learn more about SqlPackage Extract
- Learn more about SqlPackage Publish
- Learn more about SqlPackage Export
- Learn more about SqlPackage Import
- Learn more about troubleshooting issues with SqlPackage
- Share feedback on SqlPackage in the DacFx GitHub repository
Feedback
https://aka.ms/ContentUserFeedback.
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:Submit and view feedback for