Data Platform Tips 8 - Export a BACPAC file of a database

Export using Azure Portal

a) Logon to the Azure Portal

b) Navigate to the Azure SQL Database that needs to be exported and also make sure that you have a Azure Storage service provisioned which will hold the bacpac files.

c) Select the "Export" button.

d) Provide the details. Export bacpac file name, storage container and credentials and click ok.

e) You may receive an error with the following message.

Database export error

Failed to export the database: export-database-db.
ErrorCode: 400
ErrorMessage: There was an error that occurred during this operation : 'Error encountered during the service operation. ; Exception Microsoft.SqlServer.Management.Dac.Services.ServiceException:Unable to authenticate request; Inner exception System.Data.SqlClient.SqlException:Cannot open server 'export-database-db-server' requested by the login. Client with IP address '191.239.64.14' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect.; '

f) Set the Firewall rules to allow the IP address of the machine that tries to initiate the export as shown below.

g) Once done repeat step b) and initiate the export again.

h) Export request will be submitted and you also check the progress as shown below.

i) Once the request is complete, the bacpac file will be successfully created in the storage container.

Export using SQLPackage Utility

SQLPackage is a command-line utility that is used to export, import, extract, publish and run T-SQL scripts against databases.

Download the SQLPackage Utility

Check syntax here

Note: SqlPackage.exe will be located under "C:\Program Files\Microsoft SQL Server\150\DAC\bin" post the installation.

For using Windows Authentication, run the following command to get the bacpac file.

SqlPackage.exe /a:Export /tf:export-database-db.bacpac /scs:"Data Source=export-database-db-server.database.windows.net;Initial Catalog=export-database-db;" /ua:True /tid:"xxxxxx.onmicrosoft.com"

For using SQL Authentication, run the following command to get the bacpac file.

SqlPackage.exe /a:Export /tf:export-database-db.bacpac /scs:"Data Source=export-database-db-server.database.windows.net;Initial Catalog=export-database-db;user id=xxxxx;password=xxxxxx"  /ua:False