How to Export Azure SQL Database

In this azure tutorial, we will discuss How to Export Azure SQL Database. Apart from this, we will also discuss on below topics

• How To Export An Azure SQL Database With The Azure Portal
• Azure storage account
• How to create a storage account in Azure
• Create a storage account in Azure using PowerShell
• How to Backup Azure SQL Database using SQLPackage utility
• How to Backup Azure SQL Database using SQL Server Management Studio (SSMS)
• How to check Blob Files on Azure storage account
• How to Copy the bacpac file
• Using SQLPackage utility
• Using SQL Server Management Studio (SSMS)
• How to Delete a storage account using PowerShell
• Export Azure SQL Database using PowerShell
• Cancel Azure SQL database export
• How to backup Azure SQL Database to Local Machine
• How do I copy SQL Azure database to my local development server?
• How to Backup Azure SQL Database Using SSIS
• How to Backup Azure SQL Database Using BCP Utility
• Export Azure SQL Database To bak File
• How To Restore Azure SQL Database (.bacpac) file to SQL Server (.bak) file
• Replicate Azure SQL Database To Local
• Backup Azure SQL Database
• Backup Azure SQL Database To Blob Storage

How to Export Azure SQL Database

Let’s discuss here, how to export the Azure SQL database from the Azure portal. In order to export the Azure SQL database from the Azure portal, you need to save the SQL database into a BACPAC file.

• How to create an Azure SQL database
• Azure virtual machine back up

How To Export An Azure SQL Database With The Azure Portal

For this, you need to create a storage account first. Before that, We will see what is Azure storage account and the steps to create a storage account and container in the Azure portal.

Azure storage account

An Azure storage account is something that contains all the Azure storage data objects. The Azure storage data objects are like blobs, files, queues, tables, and disks, etc.

Once you store the data inside the Azure storage account that becomes more secure, scalable, durable  and highly available.

Azure storage account helps you providing a unique namespace for your Azure storage data and another important thing is that with the help of the unique namespace your Azure storage data is accessible from anywhere in the world over HTTP or HTTPS.

There are different types of storage accounts provided by Microsoft like General-purpose v2 accounts, General-purpose v1 accounts, BlockBlobStorage accounts, FileStorage accounts, BlobStorage accounts, etc.

How to create a storage account in Azure

Step-1: Login to the Azure portal (https://portal.azure.com/)

Step-2: Search for the Storage accounts (classic) and select it.

Step-3: Click on the + Add button from the Storage accounts (classic) window.

Step-4: On the Create storage account window, Provide the below details in the Basics tab.

• Subscription: Select your subscription details.
• Resource group: Select your existing resource group or you can create a new one by clicking on the Create new link.
• Storage account name: Provide a valid name for your storage account.
• Location: Select the location you belong to.
• Performance: Select the Standard option.
• Account kind: You can use the default option or you can choose the appropriate option.
• Replication: You can use the default option or you can choose the appropriate option.
• Access tier(default): You can select the default Hot option or Cool option.

Click on the Next : Networking > button to go the Networking tab.

On the networking tab keep the default option as it is. Click on the Next : Tags > button to go to the Tags tab

On the Tags tab no need to change anything click on the Next : Review + create > button.

Click on the Create button to create the storage account.

Now you can see it is deployed successfully

Now to export the Azure SQL database from the Azure portal, click on your database that you want to export and then click on the Export button.

From the Export database window, Click on the Storage (Configure required settings) link

From the Storage accounts window, select the storage account you created above.

Now it will ask to create a container since you do not have a container created as of now. To create a container click on the + Container button.

On the New container window, provide a valid name for your container and keep the Public access level as it is. Now click on the Create button to create the container.

You can see the container created successfully. Click on the Select button.

On the Export database window provide the below details

• File name: provide a file name or else you can use the default one.
• Subscription: Your subscription details will load by default. Cross-check if it is correct or else you can choose the correct one.
• Storage: You need to configure and select the container as per the above-mentioned steps.
• Server admin login: Provide the username for the admin login.
• Password: Provide the password for the admin login.
• Click on the Ok button

Now you can see the Request submitted to export database. You can check the Progress on the Import/Export History tab.

If you want to check the bacpac file created, click on the container just created, you can able to see a file with bacpac extension.

How to Copy the bacpac file

To download the bacpac file, you can click on the … (three dots) button or else you can right-click on the file and then select the download option from the popup.

Verify the bacpac file downloaded successfully in your local machine. Later on, you can import it back to Azure or to a SQL Server on-premises installation

How to Backup Azure SQL Database using SQLPackage utility

Well, Above, we have discussed the steps to export an Azure SQL Database with the Azure Portal. We can also able to export an Azure SQL Database using SQLPackage utility.

SqlPackage.exe is a command-line utility that helps you to perform multiple tasks like Creating a database snapshot (.dacpac) file, Exporting a live database, Importing the data to a new DB, Creating different XML reports and Transact-SQL scripts, etc.

If you will consider an example, you can export a database using SqlPackage.exe with Active Directory Universal Authentication using the below script

SqlPackage.exe /a:Export /tf:testExport.bacpac /scs:"Data Source=apptestserver.database.windows.net;Initial Catalog=DemoDB;" /ua:True /tid:"xyz.onmicrosoft.com"

How to Backup Azure SQL Database using SQL Server Management Studio (SSMS)

The latest version of SQL Server Management Studio provides you the option in terms of a wizard that helps you to export a database. Below are the steps you need to follow to export the database.

1. The first step is to connect to the SQL Server instance.
2. Navigate to the Object Explorer window, expand the node and select the instance that you want to export.
3. The third step is, right-click on the database name, click on the Tasks option, and then select the Export Data-tier Application option.
4. Click on the check box Do not show this page again the option to not show the Introduction page to display in the future and then click on the Next button to the next page and then click on the Cancel button to cancel the operation.
5. On the Export Settings Page, you can choose the option to either Save to local disk or Save to Azure in terms of a BACPAC file.
6. Now, on the Validation page, click on the Re-run Validation option to cross-check the validation status or you can click on the Next button to continue.
7. Finally, on the Summary page, click on the Finish button to complete the Export operation.

How to check Blob Files on Azure storage account

You can check the blob files on Azure storage account using the below ways

• Azure Portal: Once you will log in to the Azure Portal, You need to search for the Storage Explorer (preview), and then under the container, you can browse your files.
• Azure Storage Explorer tool: You can also browse all your files using Azure Storage Explorer easily. Check out some more information on Azure Storage Explorer now.

How to create a storage account in Azure using PowerShell

We saw above How to create a storage account in Azure using the Azure portal. Now let’s discuss how can we create a storage account in Azure using PowerShell cmdlet.

You can use the below PowerShell cmdlet to create a storage account using PowerShell.

$resourceGroup = 'newresgroup'
$location = 'East US'

New-AzStorageAccount -ResourceGroupName $resourceGroup `
  -Name mynewstroageaccount890 `
  -Location $location `
  -SkuName Standard_RAGRS `
  -Kind StorageV2

Name should be a unique name with all lower case letter.

The SkuName parameter can be anything from below based on your business needs

Locally redundant storage as Standard_LRS, Zone-redundant storage (ZRS) as the Standard_ZRS, Geo-redundant storage (GRS) as the Standard_GRS, Read-access geo-redundant storage (GRS) as the Standard_RAGRS, etc

Now you can login to the Azure portal and verify if the storage account got created

How to Delete a storage account using PowerShell

You can delete a storage account using PowerShell which deletes the entire account with all data in the account.

You can use the below cmdlet to delete the Storage account.

Remove-AzStorageAccount -Name mynewstroageaccount890 -ResourceGroupName newresgroup

Once you will run the above cmdlet, you will get the below Confirm popup, click on the Yes button to confirm for deletion.

Now you can see the storage account got deleted and it is no longer available in the Azure portal.

How to Export Azure SQL Database using PowerShell

We can use the New-AzSqlDatabaseExport command to submit the Export request to the Azure SQL database service.

We can use the below cmdlet. Set all the variable values as per your database configuration.

$mysubscriptionId = "XXXXX"
$myserverAdmin = 'XXXX'
$myserverPassword = 'XXXX'
$mysecurePassword = ConvertTo-SecureString $myserverPassword -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential($myserverAdmin, $mysecurePassword)
$MyResourceGroupName = 'Your resource group name'
$MyServerName = 'Your server name'
$MyDatabaseName = 'Your database name'
$MyStorageKeytype = 'Your storage key type'
$MyStorageKey = 'YourStorageAccountKey'
$bacpacFilename = $DatabaseName + (Get-Date).ToString("yyyyMMddHHmm") + ".bacpac"
$BaseStorageUri = "https://XXXXX.blob.core.windows.net/XXXXX"
$BacpacUri = $BaseStorageUri + $bacpacFilename

$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName $MyResourceGroupName -ServerName $MyServerName `
  -DatabaseName $MyDatabaseName -StorageKeytype $MyStorageKeytype -StorageKey $MyStorageKey -StorageUri $BacpacUri `
  -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

How to cancel Azure SQL database export

In case the export process is getting stuck in between or due to some reason you want to cancel the Azure SQL database export operation, you need to do the below things

Probably what you need to do is delete the storage account/container where it is exporting the data. Then after

We can set Allow Azure Services to No. We can get this option under Manage Allowed IP Addresses page.

You can try out in this way if still you are facing the same issue then you need to talk to the Azure support guys.

How to backup Azure SQL Database to Local Machine

Well, there are multiple ways by which you can able to backup Azure SQL Database or download the Azure SQL Database to the local machine. Those are as below

How do I copy SQL Azure database to my local development server?

1. Using Azure Portal
2. Using SQL Server Import and Export Wizard
3. Using SqlPackage utility
4. Using SSIS Tools
5. Using BCP utility
6. Using SqlBackupAndFtp

How to Backup Azure SQL Database Using SSIS

SQL Server Integration Services is an excellent platform that provides you the option to backup the Azure SQL Database easily.

If you have a .dtsx file or SSIS package then you can export the Azure SQL Database using the command-line tool. Now with the available .dtsx file, you can run it either with the DTEXEC.EXE command-line utility or with the help of the DTEXECUI application.

Say, your .dtsx file name is Demo.dtsx file, then you can run it like below

DTEXEC.EXE /F "Demo.dtsx"

You can also use the DTEXECUI application for the same purpose if you don’t want the command line utility.

How to Backup Azure SQL Database Using BCP Utility

The first step is to install the BCP Utility. Once you have installed the BCP Utility, you can use the below command to backup the Azure SQL database.

bcp Demobackupdb.Demo.Test out c:\sqlfile\tsinfo.dat -c -U raj -S tcp:testbackupserver.database.windows.net

Demobackupdb is the name of the database. The Demo is the schema name and Test is the table to export. The name of the file is tsinfo.dat. -c is for converting the char data type. -U is to mention the username. -S Azure server name.

Export Azure SQL Database To bak File

Well, the below information will help you to export Azure SQL Database to the bak file. We will discuss here, 2 approaches to export Azure SQL database to bak file.

How To Restore Azure SQL Database (.bacpac) file to SQL Server (.bak) file

Now, let’s discuss here multiple ways to restore SQL Database (.bacpac) file to SQL Server (.bak) file.

Approach-1

1. Log in to the Azure Portal (https://portal.azure.com/).
2. Search for the SQL Database and then click on the search result SQL databases.

3. Now you can able to see the list of Azure SQL databases that you have created, click on the particular Database that you want to export as a .bak file.

4. On the SQL Database page, click on the Export button as shown below.

5. On the Export Database window, provide the below details

• File name: Provide the file name.
• Subscription: You need to provide a valid Azure subscription that you want to use here.
• Storage: Click on the Configure Required Settings option to select the storage account and then the respective container.
• Server admin login: Provide the Server admin login username here.
• Password: Provide a valid Password.

Finally, click on the Ok button.

6. Once, the export is completed, you will find the .bacpac file downloaded on the Storage container that you have selected above.

7. Now, as a next step, you need to download the .bacpac file locally. For that right-click on the .bacpac file and then click on the Download option and save it in a local path.

8. The next step is to create the .BAK file from the .bacpac file using the SQL Server Management Studio (SSMS). Connect to the SSMS and Right-click on the Database node –> Click on the Import Data-tier Application as shown below.

9. Click on the Next button on the next page.

10. On the Import Settings tab, select the Import from local disk option and then Browse to your local path and select the .bacpac file that you have saved above, and then click on the Next button.

11. On the Database Settings tab, you can provide a new name for your Database or you can keep the default name as it is. Leave the other options as it is. Click on the Next button.

12. Now on the Summary tab, you can cross check if all the information are correct. If you want to change anything, you can click on the Previous button else click on the Finish button.

13. Now, it will take few seconds to progress and will show you the Operation Complete screen. In case of any error, it will show you in red color, click on each error to know more details.

14. You find the newly created database under the Database node in the SQL Server Management Studio.

15. Right-click on the new Database –> select Tasks –> Click on the BackUp… option as highlighted below.

16. On the next window, click on the Remove button to remove the Existing path. Then click on the Add button to add a new path to save the .bak file.

17. Once, you will click on the Add button, select the destination path and provide the filename and click on the Ok button. It will store the file as .BAK extension.

18. You are done !!! Now, in the destination path, you will find the .bak file.

Approach-2

1. Connect to the Azure SQL DB instance.
2. Right-click on the Database –> Select the Tasks option –> Click on the Export Data-tier Application option.

3. Now, click on the Next button and then choose the option to save to local disk on the Export settings tab. That will store a .bacpac file there.

4. As a next step is, connect to your Local SQL server Instance.

5. Right-click on the Database –> Click on the Import Data-tier Application option. Click on the Next button, on the Import Settings tab, select the .bacpac file from the local path that you have saved before.

6. Once the database import will be done completely, Right-click on it –> select Tasks –> Click on the BackUp… option as highlighted below.

7. Now, you can able to see it is providing us the option to Back up the DB to Disk and with the file name as the .bak extension file with the local path.

Replicate Azure SQL Database To Local

Follow the below steps to replicate Azure SQL database to local.

1. Connect to the Azure SQL DB.
2. Now, you need to Right-click on the Database –> then select the Tasks option –> Click on the Export Data-tier Application option.

3. Now, click on the Next button and then choose the option to save to local disk on the Export settings tab. That will store a .bacpac file there.

4. Now. on the summary page, you need to verify the details if everything is correct, click on the Previous button if you want to modify the options, else click on the Finish button.

5. Now as the next step Connect to the Local SQL Server Instance.

6. Right-click on the Database –> Click on the Import Data-tier Application option. Click on the Next button, on the Import Settings tab, select the .bacpac file from the local path that you have saved before.

7. Click on the Import from local disk and select the .bacpac file that you have saved above, Click on the Next button and finally, click on the Finish button.

8. You can able to see the same Database has been created successfully without any issue.

Backup Azure SQL Database

There are multiple ways to Backup Azure SQL Database, You can check out a complete article on How to backup Azure SQL database now.

Backup Azure SQL Database To Blob Storage

You can also backup your Azure SQL Database to the Blob Storage using few quick steps. Check out a complete article on Backup Azure SQL Database To Blob Storage now.

You may like following Azure tutorials:

• Azure SQL Database service tiers
• How to backup Azure blob storage using PowerShell
• Step-by-Step Guide: How to Encrypt Azure Linux VM PowerShell
• Azure Cognitive Services Text Analytics Python SDK
• How to add bulk guest users in Azure AD B2B from Azure Portal and PowerShell

Conclusion

This tutorial, we learned the below things:

• How to Export Azure SQL Database
• Azure storage account
• How to create a storage account in Azure
• Create a storage account in Azure using PowerShell
• How to Backup Azure SQL Database using SQLPackage utility
• How to Backup Azure SQL Database using SQL Server Management Studio (SSMS)
• How to check Blob Files on Azure storage account
• How to Delete a storage account using PowerShell
• Export Azure SQL Database using PowerShell
• How to cancel Azure SQL database export
• How to backup Azure SQL Database to Local Machine
• How do I copy SQL Azure database to my local development server?
• How to Backup Azure SQL Database Using SSIS
• How to Backup Azure SQL Database Using BCP Utility
• Export Azure SQL Database To bak File
• How To Restore Azure SQL Database (.bacpac) file to SQL Server (.bak) file
• Replicate Azure SQL Database To Local
• Backup Azure SQL Database
• Backup Azure SQL Database To Blob Storage

Hope you have enjoyed this Article !!!