Quick answer: For a small SQL Server database, you can create a BACPAC with the SSMS Export Data-tier Application wizard. For large databases, Microsoft recommends using the SqlPackage command-line utility for better control, scale, and performance. For very large exports, use local storage and provide enough temporary disk space instead of relying on the Azure portal export workflow.

What is a BACPAC file?

A BACPAC file is a portable package containing a database’s schema and table data. It can be used to move a database between SQL Server, Azure SQL Database, and other supported SQL environments.

A BACPAC is not a replacement for database backup and restore. For recovery scenarios, use the native backup and restore capabilities provided by your database platform. BACPAC is primarily useful for database portability and migration.

Is your SQL Server database ready for a BACPAC?

Before starting a large export, validate that the database can be represented by the data-tier tooling. A practical first step is to perform a DACPAC extraction and resolve schema or validation issues before attempting the data export.

See How to Create a DACPAC from SQL Server Database Using SSMS and SqlPackage for a detailed DACPAC workflow.

Choose the right BACPAC export method

MethodBest suited for
SSMS Export Data-tier ApplicationSmall or moderate databases and interactive exports
SqlPackageLarge databases, automation, repeatable exports, and performance tuning
Azure portal exportAzure SQL Database exports where the service limits and storage requirements are acceptable

1. Using SQL Server Management Studio

SSMS provides the Export Data-tier Application wizard and is convenient for an interactive export.

  1. In Object Explorer, right-click the database and select Tasks > Export Data-tier Application.
  2. Choose a local destination or supported Azure Storage destination and provide a suitable BACPAC filename.
  3. Review the settings and select Finish to start the export.

When should you prefer SSMS?

  • The database is relatively small or moderate in size.
  • You are performing a one-time interactive export.
  • The machine running the operation has sufficient temporary and destination disk space.

2. Using SqlPackage for large databases

SqlPackage is the preferred approach for large or automated BACPAC operations. Microsoft recommends SqlPackage for scale and performance in production environments. It runs on Windows, macOS, and Linux and supports both local and automated workflows.

Install SqlPackage as a .NET global tool

The current cross-platform installation method is the .NET global tool. The current Microsoft release supports .NET 8 and later, with the .NET 10 build recommended for modern environments.

dotnet tool install -g microsoft.sqlpackage

To update an existing installation:

dotnet tool update -g microsoft.sqlpackage

Verify the installation

sqlpackage /Version

Available on Microsoft Store

SSRS Reports Migration Wizard

A simple Windows tool for migrating SSRS reports, data sources, and related configurations between report servers.

Export the BACPAC with SqlPackage

For a SQL Server database running on the local machine, you can use Windows integrated authentication with a connection string. Adjust the server, database, and output paths for your environment.

$BacpacPath = "D:\backups\Sales.bacpac"
$DiagnosticsPath = "D:\backups\Sales-export.log"

sqlpackage /Action:Export /SourceConnectionString:"Server=localhost;Database=Sales;Integrated Security=True;TrustServerCertificate=True;" /TargetFile:$BacpacPath /DiagnosticsFile:$DiagnosticsPath

The export creates the .bacpac file and writes diagnostic information to the log file. SqlPackage also supports other authentication methods and source connection parameters; choose the method appropriate for your environment.

Optimize SqlPackage for a large database

  • Provide enough temporary disk space. SqlPackage can create temporary data during export. If the system drive is constrained, use /p:TempDirectoryForTableData to place table-data temporary files on a larger local disk.
  • Run the export close to the database. For Azure SQL Database, Microsoft recommends running SqlPackage from a VM in the same region when a VM is used, reducing network latency.
  • Use adequate compute and storage. Large exports are read-intensive. Scaling the database and using fast SSD storage for the client machine can improve performance.
  • Check large tables for clustered indexes. Large tables without clustered indexes can make BACPAC export significantly slower and may contribute to failures.
  • Reduce competing workload. Export performance is affected by concurrent database activity. A transactionally consistent copy can be useful when the production workload cannot be paused.
sqlpackage /Action:Export /SourceConnectionString:"Server=localhost;Database=Sales;Integrated Security=True;TrustServerCertificate=True;" /TargetFile:"D:\backups\Sales.bacpac" /p:TempDirectoryForTableData="D:\SqlPackageTemp" /DiagnosticsFile:"D:\backups\Sales-export.log"
create bacpac file from sql database

Important limits for large BACPAC files

  • When exporting an Azure SQL Database BACPAC to Azure Blob Storage, the current maximum BACPAC size is 200 GB. For larger BACPAC files, export to local storage with SqlPackage.
  • Microsoft currently recommends SqlPackage for importing or exporting databases larger than 150 GB because portal and PowerShell workflows can run into temporary-disk limitations.
  • For Azure SQL Database, import/export machines have limited local disk space and temporary files can require substantially more space than the database itself.
  • SqlPackage export performs best for databases under about 200 GB; larger databases may require additional tuning or a different migration approach.

Keep the BACPAC export transactionally consistent

For a transactionally consistent export, Microsoft recommends either ensuring that no write activity occurs during the export or exporting from a transactionally consistent copy of the database. This is particularly important when the source database is actively changing.

Import the BACPAC into Azure SQL

Once the BACPAC is created, it can be imported into Azure SQL Database. For large BACPAC files, use SqlPackage rather than depending on the Azure portal import workflow.

sqlpackage /Action:Import /SourceFile:"D:\backups\Sales.bacpac" /TargetConnectionString:"Server=myserver.database.windows.net;Initial Catalog=Sales;User ID=<user>;Password=<password>;"

For production migrations, use a secure authentication method and avoid putting real passwords directly in scripts or source control.

Pro tips:
1. Keep the BACPAC on storage with enough free space for the package and temporary files; the temporary footprint can be much larger than the final BACPAC.
2. For large databases, prefer SqlPackage over an interactive portal workflow and run it from a machine with fast local SSD storage.
3. Do not treat a BACPAC as your primary backup. Use native database backups for recovery requirements.
4. Protect BACPAC files because they contain database data and are compressed but not encrypted by SqlPackage.
5. For comparing Azure SQL database schemas, see Compare Azure SQL Database Schema Using SqlPackage.

See more

Visual Studio Marketplace

SSIS Catalog Migration Wizard

Extend Visual Studio with an easy way to migrate SSIS Catalog projects.

Get it from Visual Studio Marketplace
Kunal Rathi

With over 15 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.