This Azure tutorial will discuss multiple approaches to export Azure SQL Database.
Table of Contents
Export Azure SQL Database To bak File
Here, we will discuss two approaches to exporting the Azure SQL database to a bak file.
Approach-1
To export Azure SQL Database, follow the below steps.
- Log in to the Azure Portal (https://portal.azure.com/).
- Search for the SQL Database and then click on the search result SQL databases.
3. Now you can see the list of Azure SQL databases that you have created. Click on the Database 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 must provide a valid Azure subscription for the use you want here.
- Storage: Click on the Configure Required Settings option to select the storage account and then the 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, the .bacpac file will be downloaded to the Storage container you selected above.
7. As a next step, you need to download the .bacpac file locally. To do that, please right-click the .bacpac file, click 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, then Browse to your local path, select the .bacpac file you saved above, and click the Next button.
11. On the Database Settings tab, you can provide a new name for your Database or 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 that all the information is correct. If you want to change anything, click the Previous or Finish buttons.
13. It will take a few seconds to progress and show you the Operation Complete screen. In case of any error, it will show you in red. Click on each error to see 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, and Click on the BackUp… option, as highlighted below.
16. On the next window, click the Remove button to remove the existing path. Then click the Add button to add a new path to save the .bak file.
17. Once you click the Add button, select the destination path, provide the filename, and click 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
- Connect to the Azure SQL DB instance.
- 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. The next step is to 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, and select the .bacpac file from the local path you have saved before.
6. Once the database import is complete, Right-click on it, select Tasks, and click on the BackUp… option, as highlighted below.
7. Now, you can see it is providing us with the option to Back up the DB to Disk with the file name as the .bak extension file and the local path.
How to Export Azure SQL Database
Let’s discuss here how to export the Azure SQL database from the Azure portal. To export the Azure SQL database from the Azure portal, you need to save the SQL database into a BACPAC file.
Approach-1 Using The Azure Portal
First, you need to create a storage account in Azure Portal.
It is ready to use if you have successfully created a storage account following the above link.
1. To export the Azure SQL database from the Azure portal, click on the database you want to export and then click the Export button.
2. From the Export database window, Click on the Storage (Configure required settings) link
3. Select the storage account you created from the Storage Accounts window by following the link above.
4. Now, it will ask you to create a container since you have not created one yet. To create a container, click on the + Container button.
5. In the New container window, please provide a valid name for your container and leave the Public access level as is. Now click the Create button to create the container.
6. You can see the container created successfully. Click on the Select button.
7. On the Export database window, provide the below details
- File name: provide a file name, or use the default one.
- Subscription: Your subscription details will load by default. You can cross-check if they are correct or choose the correct one.
- Storage: You need to configure and select the container per the abovementioned 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 the export database and check its progress on the Import/Export History tab.
If you want to check the Bacpac file created, click on the container you just created. You will see a file with the Bacpac extension.
To download the Bacpac file, click on the … (three dots) button or right-click on the file and then select the download option from the popup.
Verify the Bacpac file downloaded successfully on your local machine. Later on, you can import it back to Azure or a SQL Server on-premises installation.
Approach-2: Using SQLPackage utility
Above, we have discussed the steps to export an Azure SQL Database with the Azure Portal. We can also export an Azure SQL Database using the SQLPackage utility.
SqlPackage.exe is a command-line utility that helps you perform multiple tasks, such as Creating a database snapshot (.dacpac) file, Exporting a live database, Importing data to a new database, 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"Approach-3 Using SQL Server Management Studio (SSMS)
The latest version of SQL Server Management Studio provides you the option of a wizard that helps you export a database. Below are the steps you need to follow to export the database.
- The first step is to connect to the SQL Server instance.
- Navigate to the Object Explorer window, expand the node, and select the instance you want to export.
- The third step is to right-click on the database name, click the Tasks option, and then select the Export Data-tier Application option.
- Click on the check box. Do not show this page again; the option not to show the Introduction page to display in the future, then click on the Next button to the next page, and then click on the Cancel button to cancel the operation.
- On the Export Settings Page, you can choose to either Save to a local disk or Save to Azure in terms of a BACPAC file.
- On the Validation page, click the Re-run Validation option to cross-check the validation status, or click the Next button to continue.
- Finally, click the Finish button on the Summary page to complete the Export operation.
Approach-4 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 Export Azure SQL Database to Local Machine
Well, there are multiple ways by which you can export Azure SQL Database or download the Azure SQL Database to the local machine. Those are as below
- Using Azure Portal
- Using SQL Server Import and Export Wizard
- Using SqlPackage utility
- Using SSIS Tools
- Using BCP utility
- Using SqlBackupAndFtp
Approach-1 Using SSIS
SQL Server Integration Services is an excellent platform allowing you to export the Azure SQL Database easily.
If you have a .dtsx file or SSIS package, 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.
Approach-2: 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 back up the Azure SQL database.
bcp Demobackupdb.Demo.Test out c:\sqlfile\tsinfo.dat -c -U raj -S tcp:testbackupserver.database.windows.netDemobackupdb 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.
FAQs
How to cancel Azure SQL database export
If the export process gets stuck in between or for some reason, you want to cancel the Azure SQL database export operation. It would be best if you did the below things
You probably need to delete the storage account/container that is exporting the data. Then, we can set Allow Azure Services to No. This option is available under the Manage Allowed IP Addresses page.
You can try it out this way. You must talk to the Azure support guys if you still have the same issue.
You may like the following Azure tutorials:
Conclusion
In this tutorial, we learned multiple approaches to export Azure SQL Database. I hope you have enjoyed this Article !!!
I am Bijay, a Microsoft MVP (10 times) having more than 17 years of experience in the software industry. During my IT career, I got a chance to share my expertise in SharePoint and Microsoft Azure, like Azure VM, Azure Active Directory, Azure PowerShell, etc. I hope you will learn from these Azure tutorials. Read more
