Move a whole database with SqlPackage: dacpac and bacpac
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
.
Azure SQL Database's own automated backups and point-in-time or geo-restore are
. Loading rows into a table that already exists is
.
Vector indexes themselves are
.
Scope
- Includes moving the database itself from the local Azure SQL Database container up to Azure SQL
Database.
- Pointing the application's connection string at the cloud database, and first-time setup of the
local container, are separate workflows.
Read the property list for the action before you write the command
Properties are per action.
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:
bash
sqlpackage /version:True
sqlpackage /Action:Export /?
An unknown property is refused before any connection is attempted:
text
$ 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:
is the .NET
Framework build's default, and Learn records
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,
as
,
as
,
as
, and the two forms are
interchangeable.
The four actions, as four commands
| Action | Direction | Default contents | File |
|---|
| Extract | database to file | schema only | writes |
| Publish | file to database | schema only | reads |
| Export | database to file | schema and every base table's rows | writes |
| Import | file to database | schema and every base table's rows | reads |
Extract:
bash
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:
bash
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:
bash
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:
bash
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
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.
On a logical server both need that permission to create the target: the server admin, or a member
of the
role in
.
What Import will not do is load into a target that already holds a user object:
text
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
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 produces a
carrying no data entry
of any kind, and an Export of the same database produces a
carrying
Data/dbo.Widget/TableData-000-00000.BCP
, one entry per base table holding rows.
Then the same Extract with one property added:
bash
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
, is still the output of Extract, and now carries
Data/dbo.Widget/TableData-000-00000.BCP
as well.
is the narrower form, accepted
by both Extract and Export, naming one table per occurrence:
bash
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:
bash
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. On Azure SQL Database
BACKUP DATABASE app TO DISK='/tmp/x.bak'
returns
,
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
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
, and leftover Service Broker or query notification
permissions, most often a
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:
bash
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.
bash
sqlpackage /Action:DeployReport /SourceFile:app.dacpac /DeployReportPath:deploy-report.xml \
/TargetServerName:your-server.database.windows.net /TargetDatabaseName:<database> \
/TargetUser:<user> /TargetPassword:"$SQLPACKAGE_PASSWORD"
bash
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:
sql
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. A vector index needs at least 100 rows carrying non-null vectors; with fewer, its
creation is refused with
. Drop the vector indexes
before exporting and recreate them after the data is loaded.
is refused while a
vector index exists (
), 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.
bash
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:
sql
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
- File listings, exit codes and the commands behind them: open it when a claim above disagrees with what you are seeing, or to rerun a check.
- The SQL71627 pre-flight scan
- The property reference per action, and the only authority on whether a property exists:
Extract,
Export,
Import,
Publish,
Script and
DeployReport.