SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillssqlpackage-import-export

sqlpackage-import-export

Data & analytics

View repository

Updated 10-16 00:31

About this skill

Move a whole database with SqlPackage: dacpac and bacpac

Verified 2026-09-02 against SqlPackage 170.4.83.3, and the engine behaviour below measured 2026-08-29 against a container reporting EngineEdition 5, 12.0.2000.8.

This owns the four-action decision for moving an entire existing database as a portable file, and the command line for each action. Building a dacpac from a source-controlled SQL project, shipping it through CI, its refactorlog and its pre and post deployment scripts are sql-database-projects. Azure SQL Database's own automated backups and point-in-time or geo-restore are restore-and-recover. Loading rows into a table that already exists is bulk-load-and-bulk-copy. Vector indexes themselves are vector-search-azure-sql.

Read the property list for the action before you write the command

Properties are per action. ExtractAllTableData is an Extract property and is not accepted by Export. There is no catalog of properties that spans the actions, so ask the build in front of you:

sqlpackage /version:True
sqlpackage /Action:Export /?

An unknown property is refused before any connection is attempted:

$ sqlpackage /Action:Export /p:ExportAllTableData=true /TargetFile:app.bacpac /SourceServerName:your-server.database.windows.net /SourceDatabaseName:app
*** 'ExportAllTableData' is not a valid argument for the 'Export' action.

A property can also be real, documented and still unavailable: /p:Storage=File is the .NET Framework build's default, and Learn records Memory as the only option on the cross-platform build.

Every parameter in this skill is written in long form. The same help output gives each one a short form, /Action as /a, /SourceFile as /sf, /TargetFile as /tf, and the two forms are interchangeable.

The four actions, as four commands

Action Direction Default contents File
Extract database to file schema only writes .dacpac
Publish file to database schema only reads .dacpac
Export database to file schema and every base table's rows writes .bacpac
Import file to database schema and every base table's rows reads .bacpac

Extract:

sqlpackage /Action:Extract /TargetFile:app.dacpac /DiagnosticsFile:extract.log \
  /SourceServerName:your-server.database.windows.net /SourceDatabaseName:<database> \
  /SourceUser:<user> /SourcePassword:"$SQLPACKAGE_PASSWORD" \
  /p:ExtractAllTableData=false /p:VerifyExtraction=true

Export:

sqlpackage /Action:Export /TargetFile:app.bacpac /DiagnosticsFile:export.log \
  /SourceServerName:your-server.database.windows.net /SourceDatabaseName:<database> \
  /SourceUser:<user> /SourcePassword:"$SQLPACKAGE_PASSWORD" \
  /p:VerifyExtraction=true /p:LongRunningCommandTimeout=0

Import:

sqlpackage /Action:Import /SourceFile:app.bacpac /DiagnosticsFile:import.log \
  /TargetServerName:your-server.database.windows.net /TargetDatabaseName:<new-database> \
  /TargetUser:<user> /TargetPassword:"$SQLPACKAGE_PASSWORD" \
  /p:DatabaseEdition=Standard /p:DatabaseServiceObjective=S1

Publish:

sqlpackage /Action:Publish /SourceFile:app.dacpac \
  /TargetServerName:your-server.database.windows.net /TargetDatabaseName:<database> \
  /TargetUser:<user> /TargetPassword:"$SQLPACKAGE_PASSWORD" \
  /p:BlockOnPossibleDataLoss=true /p:DropObjectsNotInSource=false

/p:DatabaseEdition and /p:DatabaseServiceObjective are accepted by Import and Publish and are how the database they create is sized. Extract and Export do not accept them.

Both Import and Publish can create the target; only Publish can rerun

Microsoft Learn states it for each. Publish: if the database doesn't exist on the server, the publish operation creates it, otherwise an existing database is updated. Import: a new database can be created by the import action when the authenticated user has create database permissions.

What Import will not do is load into a target that already holds a user object:

Error SQL71659: Data cannot be imported into target because it contains one or more user objects.

It refuses before any statement runs against the target, and retrying the same Import against the same name refuses again, every time, until that database is dropped and recreated or a new name is chosen. Publish diffs instead: rerun it against a target it already published to and it reports Update complete with nothing to apply.

The property decides what is in the file, not the extension

An Extract of a database with one table and one view produced a .dacpac carrying no data entry of any kind, and an Export of the same database produced a .bacpac carrying Data/dbo.Widget/TableData-000-00000.BCP, one entry per base table holding rows.

Then the same Extract with one property added:

sqlpackage /Action:Extract /TargetFile:app-with-data.dacpac \
  /SourceServerName:your-server.database.windows.net /SourceDatabaseName:<database> \
  /SourceUser:<user> /SourcePassword:"$SQLPACKAGE_PASSWORD" /p:ExtractAllTableData=true

That file is still named .dacpac, is still the output of Extract, and now carries Data/dbo.Widget/TableData-000-00000.BCP as well. /p:TableData is the narrower form, accepted by both Extract and Export, naming one table per occurrence:

sqlpackage /Action:Extract /TargetFile:app-two-tables.dacpac \
  /SourceServerName:your-server.database.windows.net /SourceDatabaseName:<database> \
  /SourceUser:<user> /SourcePassword:"$SQLPACKAGE_PASSWORD" \
  /p:TableData=dbo.Widget /p:TableData=dbo.WidgetAudit

Open the file and look rather than reading the name:

unzip -l app-with-data.dacpac

An export is not a backup, and is not consistent by default

Microsoft Learn states plainly that bacpac files aren't intended to be used for backup and restore operations. BACKUP DATABASE app TO DISK='/tmp/x.bak' against this engine returns Msg 40510, and there is no RESTORE either.

Learn again, on Export: for an export to be transactionally consistent, either no write activity is occurring during the export, or the export is taken from a transactionally consistent copy of the database. Nothing in the output of a successful export says which of those was true. Export from a database copy, or from a database nothing is writing to, or state in the answer that the file may be internally inconsistent.

Before an Export or Extract from a database with operating history

Export is limited to the Azure SQL Database surface area, so a source carrying an element outside it fails with SQL71627 after the whole schema model has already been built, discarding the entire attempt rather than the one object. The usual carriers are Windows-authenticated users and logins, such as an inherited NT AUTHORITY\SYSTEM, and leftover Service Broker or query notification permissions, most often a RECEIVE grant on QueryNotificationErrorsQueue.

Open references/preflight-and-sql71627.md before running an export against a database you did not create yourself: it holds the queries that find both classes and the remediation for each.

Extract accepts two properties that drop the offending model elements, and Export accepts neither:

sqlpackage /Action:Extract /TargetFile:app.dacpac \
  /SourceServerName:your-server.database.windows.net /SourceDatabaseName:<database> \
  /SourceUser:<user> /SourcePassword:"$SQLPACKAGE_PASSWORD" \
  /p:IgnoreUserLoginMappings=true /p:IgnorePermissions=true

So when a database will not export, extracting the schema and moving the rows separately is the route that still runs.

Read the plan before a Publish that could drop something

DeployReport writes XML and Script writes T-SQL. Neither one changes the target database.

sqlpackage /Action:DeployReport /SourceFile:app.dacpac /DeployReportPath:deploy-report.xml \
  /TargetServerName:your-server.database.windows.net /TargetDatabaseName:<database> \
  /TargetUser:<user> /TargetPassword:"$SQLPACKAGE_PASSWORD"

sqlpackage /Action:Script /SourceFile:app.dacpac /DeployScriptPath:publish.sql \
  /TargetServerName:your-server.database.windows.net /TargetDatabaseName:<database> \
  /TargetUser:<user> /TargetPassword:"$SQLPACKAGE_PASSWORD"

/p:BlockOnPossibleDataLoss defaults to True and is what stops a publish that would drop data. Leave it on. /p:CreateNewDatabase=true does not mean "create it if it is missing", which Publish already does: Learn describes it as whether the target database should be updated or whether it should be dropped and re-created. Setting it against a populated target destroys that target.

A vector index breaks the round trip, and the export looks fine

Check before planning anything around a bacpac:

SELECT OBJECT_NAME(object_id) AS table_name, name
FROM sys.indexes WHERE type_desc = 'VECTOR';

An import creates schema objects before it loads data, so a vector index is created against an empty table. Measured 2026-08-31 against 12.0.2000.8: a vector index needs at least 100 rows carrying non-null vectors, 99 is refused with Msg 42266 and 100 succeeds. Drop the vector indexes before exporting and recreate them after the data is loaded. TRUNCATE TABLE is refused while a vector index exists (Msg 42232), so reloading in place hits the same wall one step earlier.

Check it worked

Exit code 0 says the action SqlPackage attempted finished. It does not say the rows arrived.

unzip -l app.bacpac | grep TableData

Expect one Data/<schema>.<table>/TableData-000-00000.BCP entry per base table that held rows, and none for a view. Then compare the target against the source:

SELECT SUM(p.rows) AS row_count, COUNT(DISTINCT t.object_id) AS table_count
FROM sys.tables AS t
JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id IN (0, 1);

Run it on both databases. Equal numbers, or a stated reason they differ, is the check. A schema only dacpac publishes to zero rows and that is correct, not a failure.

References