Edit

Share via

SqlPackage for SQL database in Microsoft Fabric

  • Article

Applies to: ✅ SQL database in Microsoft Fabric

In this tutorial, you learn how to work with SqlPackage in your SQL database in Fabric.

SqlPackage is a CLI providing database portability and database deployments.

  • The portability (import/export) of a database managed in Azure or in Fabric ensures that your data is portable to other platforms, including SQL Server or Azure SQL Managed Instance, should you want to migrate later on.
  • The same portability also enables certain migration scenarios through self-contained database copies (.bacpac) with import/export operations.

SqlPackage can also enable easy database deployments of incremental changes to database objects (new columns in tables, alterations to existing stored procedures, etc.).

  • SqlPackage can extract a .dacpac file containing the definitions of objects in a database, and publish a .dacpac file to apply that object state to a new or existing database.
  • The publish operation also integrates with SQL projects, which enables offline and more dynamic development cycles for SQL databases.

Prerequisites

Setup

SqlPackage is available for Windows, macOS, and Linux as a dotnet tool. You can install it using the following command:

dotnet tool install --global Microsoft.SqlPackage

As a global dotnet tool, SqlPackage is available in your terminal as sqlpackage from any folder.

Import a database with SqlPackage

A .bacpac is a portable copy of a database, useful for some migration and testing scenarios. You can import that .bacpac into an empty SQL database.

Note

A .bacpac is not a backup or a replacement for backup/restore capabilities. For more information about backups in Fabric SQL database, see Automatic backups in SQL database in Microsoft Fabric and Restore from a backup in SQL database in Microsoft Fabric.

  1. If using a .bacpac from your Azure SQL Database environment, you might need to alter the source database to meet the Fabric SQL database T-SQL surface area.

  2. Create your new SQL database in Fabric as usual through the Fabric interface.

  3. Copy the connection string from settings.

    Screenshot from the Fabric portal showing the Connection strings page of the SQL database.

  4. Use the import command from terminal in the sqlpackage folder. Provide your owner <servername> and <database_name>.

    sqlpackage /action:import /sourcefile:"C:\DatabaseName.bacpac" /targetconnectionstring:"Data Source=tcp:<server_name>.database.windows.net,1433;Initial Catalog=<database_name>;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;ConnectRetryCount=6;ConnectRetryInterval=10;Authentication=Active Directory Interactive"
    • Replace your connection string from the SQL database settings dialog.
    • Replace the sourcefile value with the .bacpac name (DatabaseName) and location on your local machine.

For more information on import, see SqlPackage import.

Export a database with SqlPackage

Exporting a .bacpac is the reverse operation, where your targetfile is a .bacpac and your sourceconnectionstring can be found in the SQL database settings dialog, as in the previous example. Provide your owner <servername> and <database_name>. For example:

sqlpackage.exe /action:export /targetfile:"C:\DatabaseName.bacpac" /sourceconnectionstring:"Data Source=tcp:<server_name>.database.windows.net,1433;Initial    Catalog=<database_name>;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;ConnectRetryCount=6;ConnectRetryInterval=10;Authentication=Active Directory Interactive"

For more information on export, see SqlPackage export.

Extract and publish

Diagram of how SQL database projects can move schema changes.

A .dacpac is a database schema model file, containing definitions for the tables, stored procedures, and other objects in the source database.

SqlPackage is capable of deploying a .dacpac to a new (empty) database or incrementally updating an existing database to match the desired .dacpac state.

  • Extract creates a .dacpac or sql files from an existing database.
  • Publish deploys a .dacpac to a database.

The SqlPackage publish syntax is similar to the import/export commands.

Related content