Skip to content

Latest commit

 

History

History
75 lines (46 loc) · 4.26 KB

day88.sql.bacpac.md

File metadata and controls

75 lines (46 loc) · 4.26 KB
Raw

Day 88 - SQL BACPAC Files and Azure CLI

In yesterday's installment, we stepped through the details of Azure SQL from ARM. In that article, we imported a database into the new instance from a BACPAC file. What is a BACPAC file? We're going to dig a little further into the BACPAC, a very useful feature that's been around a very long time.

In this article:

What is a BACPAC file?
Operations, Tools, and Methods
Permissions
Export your SQL database to a BACPAC file
Import a new SQL database from a BACPAC file

What is a BACPAC file?

A BACPAC is a file with a .bacpac extension that contains a database schema and data. The primary use cases for a BACPAC include:

  • Moving a database between servers
  • Migrating a local database to the cloud
  • Archiving an existing database to an open format

In short, the BACPAC is super-handy in cloud migration scenarios, whether moving to SQL on Azure VMs (IaaS) or Azure SQL (PaaS).

NOTE: BACPACs are not intended to be used for backup and restore operations. As mentioned earlier in this series, Azure Database automatically creates backups for every user database.

Operations, Tools, and Methods

When working with a BACPAC file, you'll likely be performing one of two operations: IMPORT or an EXPORT. Both these capabilities are supported by the database management tools: SQL Server Management Studio, the Azure Portal, DACFx API, as well as ARM and the Azure CLI.

We'll be focused on import and export of BACPAC of your Azure SQL database with the Azure portal.

Permissions

Before you attempt export or import, make sure you have the right permissions. You must be a member of the dbmanager role or assigned CREATE DATABASE permissions to create a database, including creating a database by deploying a DAC package. You must be a member of the dbmanager role, or have been assigned DROP DATABASE permissions to drop a database.

Export your SQL database to a BACPAC file

You import a BACPAC into an existing database with the Azure CLI using the az sql db export command.

First, get a SAS key for use in the export operation.

az storage blob generate-sas \
--account-name myAccountName \
-c myContainer -n myBacpac.bacpac \
--permissions w --expiry 2020-31-01T00:00:00Z

Then, Export to BACPAC using an SAS key.

az sql db export -s myserver -n contoso -g mygroup -p password -u login \
    --storage-key "?sr=b&sp=rw&se=2020-01-31T00%3A00%3A00Z&sig=mysignature&sv=2019-01-01" \
    --storage-key-type SharedAccessKey \
    --storage-uri https://contosoAcctName.blob.core.windows.net/bacpacContainer/contoso.bacpac

To guarantee a transaction-consistent BACPAC file, you may first want to create a copy of your database and then export from the copy.

Import a new SQL database from a BACPAC file

The BACPAC import process in Azure is supported natively in Azure with ARM, but also with Azure CLI.The BACPAC import process in the Azure context is essentially two steps

  • The BACPAC is exported into an Azure storage blob container
  • The BACPAC is then downloaded and imported on the target server

You import a BACPAC into an existing database with the Azure CLI using the az sql db import command.

For a detailed step-by-step example, simply visit "Import the Database into the Azure SQL Server" in Day 87!

If you do not have an Azure SQL server or database, or the storage account to upload the BACPAC to, start at the beginning of yesterday's installment of this series: Day 87 - Deploying Azure SQL Server using ARM.

Conclusion

This is a quick look at the BACPAC. If you've never tried it, revisit Day 87 and try the Azure CLI and ARM samples to get some hands-on experience.