Search code examples
sql-serverazureazure-sql-databaseazure-sql-server

Azure SQL exports fail to backup in blob container

I have a database in Azure SQL database, single database. It is Northwind sample that I have created in Azure SQL database, by using scripts. I am trying to export this database into blob storage in a storage account Gen 2. I have created a storage account in the same resource group where my Azure SQL database, single database resides. Via the portal I export the database. In firewall settings of my database, I have already checked "Allow Azure services and resources to access this server" as shown below:

enter image description here

I have also added my IP address to have access.

When I click on export I can see my storage account, and the container to save my backups (export database files) as shown below:

enter image description here

However, when I click OK and my export request is submitted, after a few minutes, I can see in "Import/Export History" that my request status remians on "Running, Progress=1%" and later the status changes to "Failed".

enter image description here

When I check my blob container in the storage account I can see the files are there all with 4B size as show below:

enter image description here

What is the cause of this and how can I resolve it? Basically I want to export the database into a blob storage by following this link but it is failing to export. Thank you in advance.

Solution

  • You can use SQL Server Management Studio (SSMS) to export the database bacpac file in blob storage account.

    Follow the below steps:

    1. Open the SSMS in your local machine and login to your Azure SQL Server. Fill the required details and click on Connect as shown below.

    enter image description here

    1. Right click on database you want to backup. Follow Task -> Export Data-tier Application.

    enter image description here

    1. Select Save to Microsoft Azure option. Click on Connect to connect with your Azure account and provide storage account details. Click on Next.

    enter image description here

    1. On the next page, check the summary of your settings and simply click on Finish button.

    Once finished, you can check the bacpac file in your storage account. See the image below for your reference.

    enter image description here

    Additional step may required: If the authentication was cached, you need to run DBCC FLUSHAUTHCACHE; command in Management Studio and it will exported perfectly.