Muokkaa

Jaa

SqlPackage Import parameters and properties

  • Artikkeli

The SqlPackage Import action imports the schema and table data from a BACPAC file (.bacpac) into a new or empty database in SQL Server or Azure SQL Database. At the time of the import operation to an existing database the target database cannot contain any user-defined schema objects. Alternatively, a new database can be created by the import action when the authenticated user has create database permissions. The Import action is part of the database portability functionality of SqlPackage.

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.

Note

SqlPackage import performs best for databases under 200GB. For larger databases, you may want to optimize the operation using properties available in this article and tips in Troubleshooting with SqlPackage or alternatively achieve database portability through data in parquet files.

Command-line syntax

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

SqlPackage /Action:Import {parameters} {properties}

Required parameters

The Import action requires a SourceFile parameter to specify the name and location of the .bacpac file containing the database objects and data.

The Export action requires a target connection where a new database will be created by SqlPackage or where a blank database is present. This is specified either through a combination of:

  • TargetServerName and TargetDatabaseName parameters, or
  • TargetConnectionString parameter.

Examples

# example import to Azure SQL Database using SQL authentication and a connection string
SqlPackage /Action:Import /SourceFile:"C:\AdventureWorksLT.bacpac" \
    /TargetConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=AdventureWorksLT;Persist Security Info=False;User ID=sqladmin;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

# example import using short form parameter names
SqlPackage /a:Import /tsn:"{yourserver}.database.windows.net,1433" /tdn:"AdventureWorksLT" /tu:"sqladmin" \
    /tp:"{your_password}" /sf:"C:\AdventureWorksLT.bacpac"

# example import using Microsoft Entra managed identity
SqlPackage /Action:Import /SourceFile:"C:\AdventureWorksLT.bacpac" \
    /TargetConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=AdventureWorksLT;Authentication=Active Directory Managed Identity;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

# example import connecting using Microsoft Entra username and password
SqlPackage /Action:Import /SourceFile:"C:\AdventureWorksLT.bacpac" \
    /TargetConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=AdventureWorksLT;Authentication=Active Directory Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;User ID={yourusername};Password={yourpassword}"

# example import connecting using Microsoft Entra universal authentication
SqlPackage /Action:Import /SourceFile:"C:\AdventureWorksLT.bacpac" /UniversalAuthentication:True \
    /TargetConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=AdventureWorksLT;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Parameters for the Import action

Parameter Short Form Value Description
/AccessToken: /at: {string} Specifies the token-based authentication access token to use when connect to the target database.
/Action: /a: Import Specifies the action to be performed.
/AzureCloudConfig: /acc: {string} Specifies the custom endpoints for connecting to Microsoft Entra ID in the format: AzureActiveDirectoryAuthority={value};DatabaseServicePrincipalName={value}" .
/Diagnostics: /d: {True|False} Specifies whether diagnostic logging is output to the console. Defaults to False.
/DiagnosticsFile: /df: {string} Specifies a file to store diagnostic logs.
/MaxParallelism: /mp: {int} Specifies the degree of parallelism for concurrent operations running against a database. The default value is 8.
/ModelFilePath: /mfp: {string} Specifies the file path to override the model.xml in the source file. Use of this setting may result in deployment failure and/or unintended data loss. This setting is intended only for use when troubleshooting issues with publish, import, or script generation.
/Properties: /p: {PropertyName}={Value} Specifies a name value pair for an action-specific property; {PropertyName}={Value}.
/Quiet: /q: {True|False} Specifies whether detailed feedback is suppressed. Defaults to False.
/SourceFile: /sf: {string} Specifies a source file to be used as the source of action from local storage. If this parameter is used, no other source parameter shall be valid.
/TargetConnectionString: /tcs: {string} Specifies a valid SQL Server/Azure connection string to the target database. If this parameter is specified, it shall be used exclusively of all other target parameters.
/TargetDatabaseName: /tdn: {string} Specifies an override for the name of the database that is the target of SqlPackage Action.
/TargetEncryptConnection: /tec: {Optional|Mandatory|Strict|True|False} Specifies if SQL encryption should be used for the target database connection. Default value is True.
/TargetHostNameInCertificate: /thnic: {string} Specifies value that is used to validate the target SQL Server TLS/SSL certificate when the communication layer is encrypted by using TLS.
/TargetPassword: /tp: {string} For SQL Server Auth scenarios, defines the password to use to access the target database.
/TargetServerName: /tsn: {string} Defines the name of the server hosting the target database.
/TargetTimeout: /tt: {int} Specifies the timeout for establishing a connection to the target database in seconds. For Microsoft Entra ID, it is recommended that this value be greater than or equal to 30 seconds.
/TargetTrustServerCertificate: /ttsc: {True|False} Specifies whether to use TLS to encrypt the target database connection and bypass walking the certificate chain to validate trust. Default value is False.
/TargetUser: /tu: {string} For SQL Server Auth scenarios, defines the SQL Server user to use to access the target database.
/TenantId: /tid: {string} Represents the Microsoft Entra tenant ID or domain name. This option is required to support guest or imported Microsoft Entra users as well as Microsoft accounts such as outlook.com, hotmail.com, or live.com. If this parameter is omitted, the default tenant ID for Microsoft Entra ID will be used, assuming that the authenticated user is a native user for this AD. However, in this case any guest or imported users and/or Microsoft accounts hosted in this Microsoft Entra ID are not supported and the operation will fail.
For more information, see Universal authentication with SQL Database and Azure Synapse Analytics (SSMS support for MFA).
/ThreadMaxStackSize: /tmss: {int} Specifies the maximum size in megabytes for the thread running the SqlPackage action. This option should only be used when encountering stack overflow exceptions that occur when parsing very large Transact-SQL statements.
/UniversalAuthentication: /ua: {True|False} Specifies if universal authentication should be used. When set to True, the interactive authentication protocol is activated supporting MFA. This option can also be used for Microsoft Entra authentication without MFA, using an interactive protocol requiring the user to enter their username and password or integrated authentication (Windows credentials). When /UniversalAuthentication is set to True, no Microsoft Entra authentication can be specified in SourceConnectionString (/scs). When /UniversalAuthentication is set to False, Microsoft Entra authentication must be specified in SourceConnectionString (/scs).
For more information, see Universal authentication with SQL Database and Azure Synapse Analytics (SSMS support for MFA).

Properties specific to the Import action

Property Value Description
/p: CommandTimeout=(INT32 '60') Specifies the command timeout in seconds when executing queries against SQL Server.
/p: DatabaseEdition=({ Basic | Standard | Premium | DataWarehouse | GeneralPurpose | BusinessCritical | Hyperscale | Default } 'Default') Defines the edition of an Azure SQL Database. See Azure SQL Database service tiers.
/p: DatabaseLockTimeout=(INT32 '60') Specifies the database lock timeout in seconds when executing queries against SQLServer. Use -1 to wait indefinitely.
/p: DatabaseMaximumSize=(INT32 '0') Defines the maximum size in GB of an Azure SQL Database.
/p: DatabaseServiceObjective=(STRING) Defines the performance level of an Azure SQL Database such as "P0" or "S1".
/p: DisableIndexesForDataPhase=(BOOLEAN 'True') When true (default), disables indexes before importing data. When false, indexes are not rebuilt.
/p: DisableParallelismForEnablingIndexes=(BOOLEAN 'False') Not using parallelism when rebuilding indexes while importing data into SQL Server.
/p: HashObjectNamesInLogs=(BOOLEAN 'False') Specifies whether to replace all object names in logs with a random hash value.
/p: ImportContributorArguments=(STRING) Specifies deployment contributor arguments for the deployment contributors. This property should be a semi-colon delimited list of values.
/p: ImportContributorPaths=(STRING) Specifies paths to load additional import contributors. This property should be a semi-colon delimited list of values.
/p: ImportContributors=(STRING) Specifies the deployment contributors, which should run when the bacpac is imported. This property should be a semi-colon delimited list of fully qualified build contributor names or IDs.
/p: LongRunningCommandTimeout=(INT32 '0') Specifies the long running command timeout in seconds when executing queries against SQL Server. Use 0 to wait indefinitely.
/p: PreserveIdentityLastValues=(BOOLEAN 'False') Specifies whether last values for identity columns should be preserved during deployment.
/p: RebuildIndexesOfflineForDataPhase=(BOOLEAN 'False') When true, rebuilds indexes offline after importing data into SQL Server.
/p: Storage=({File|Memory}) Specifies how elements are stored when building the database model. For performance reasons the default is InMemory. For large databases, File backed storage is required.

Next Steps