Importing a BACPAC to SQL Server

We previously looked at Create a backup for Azure SQL Server and in today’s post we are going to address how to look at that data by restoring or importing it to a local SQL Server.

To start, open SQL Server Management Studio (SSMS) and connect to a local instance of SQL Server. Right-click on the instance name and select Import Data-tier Application.

AzureImportSQL1

Simply click Next to go back the welcome screen of the import wizard.

image

Click browse and locate the BACPAC file on your local computer. Click Next.

AzureImportSQL2

Alternately, change the radio button to Import from Windows Azure and click Connect. You will be prompted to enter your storage account name and access key and then locate the BACPAC in your storage account. This will be downloaded as part of the import process to a temporary directory that can also be specified in the wizard.

On the database settings page of the wizard the database name, data file storage path and log file storage paths can be modified. The default locations for the data and log files will be pulled from the model database. Click Next.

AzureImportSQL3

Click Finish on the Summary page to being the import.

AzureImportSQL4

Each step and the status of the operation will be displayed. Assuming all green check marks click Close on the wizard. If there are any errors click the link in the Result column to see the details behind the failure. There should also be a new database in the SQL Server object explorer carrying the same name specified on the Database Settings page of the import wizard.

AzureImportSQL5

This satisfies the full set of requirements given by the customer:

  • Full backup of the data, archived monthly for 10 years – this can be stored in Azure blob storage and/or downloaded and stored locally
  • Ability to restore the archive at any time – a BACPAC can be imported to Azure SQL Database or to a local SQL Server
  • Maintain data access should the customer decide to no longer leverage Azure SQL Database – BACPAC files can be imported to a local SQL Server instance

Create a backup for Azure SQL Database

Azure SQL Database is a managed database platform as a service (Paas) offering available from Microsoft in the Azure cloud. One of the advantages to Azure SQL Database is all the file management, server maintenance and backups are taken care of automatically. Point in time recovery is built directly into the service. The amount of time a user can go back and perform a point in time restore depends on the service tier selected.

Creating the BACPAC

To start, open a web browser and access the Azure portal (https://portal.azure.com). After signing in navigate to the SQL Databases section and select the database you want to archive.

AzureSql1

On the overview page click the Export button near the top of the page.

AzureSql2

On the resulting page name the BACPAC. Select the subscription and storage location where the BACPAC file will be saved. Enter the credentials that will be used to access the Azure SQL Server. Click OK at the bottom of the pane and the export process will begin in the background.

AzureSql3

If you don’t already have a storage, you can configure one. Go to All resources, Add a new resource and search for Storage account - blob, file, table, queue. Be careful: the name of the storage is lower case.

AzureSql4

Then in your new storage you have to create a container. You can create a container also in the Export process.

Under Activity log you can see the status of the export.

AzureSql5

When the process is finished, in your storage you have to file under Blobs, ready to download it.

This file is a zip file and contains the structure and data of your database. Now you can delete the storage on Azure (it costs money).