How to backup Azure SQL database

How to backup Azure SQL database

This Azure tutorial will discuss How to backup Azure SQL database.

How to backup Azure SQL database

When we are thinking of the way to take the backup of the Azure SQL database, many questions come into our mind like how to take backup of SQL Azure database, How can I do It using the Azure Portal? Is there a way to achieve this using the portal? etc

Let’s discuss here how to take a backup of the Azure SQL database from the portal. Follow the below steps for Azure SQL backup.

1. Log in to the Azure portal (https://portal.azure.com/)

2. Click on the Azure SQL database you created.

How to backup Azure SQL database

3. Click the Manage Backups link on your Azure SQL database window.

azure sql server backup

4. Select your database from the list and click on the Configure retention link from the Configure policies section.

backup azure sql database to blob storage

5. Fill in the options below based on your business requirements.

Point in Time Restore Configuration: Select the number of days based on your business needs from 7,14,21,28,35 days.

Long-term Retention Configurations: You can check the Weekly LTR Backups option and select the appropriate choice. How long would you like weekly backups to be kept?

You can choose the appropriate options for Monthly LTR Backups and Yearly LTR Backups based on your business needs.

Then click on the Apply button to save the changes you made.

manual backup azure sql database

6. Now click on the yes button on the below popup for confirmation.

azure sql database backup

Now you can see the successful message “Retention policies updated successfully for selected databases.”

how to take backup of azure sql database from portal

Now you can see the changes are reflected in your Azure SQL database

azure sql database backup to storage account

Backup Azure SQL Database To Blob Storage

We can back up our Azure SQL Database to the Azure Blob Storage using the steps below. Before that, we need to know what the prerequisites are needed to work with this requirement.

Prerequisites

  • You must have an Azure Subscription or Azure Free Account. If you don’t have an Azure account, Create an Azure free account now.
  • You need to have an Azure Storage Account. If you don’t have an Azure Storage Account, create one Azure storage account now.
  • You need to install the SQL Server Management Studio.

Create a storage account

The first step is to create a storage account. Follow the below steps to create a storage account.

  • Log in to the Azure Portal(https://portal.azure.com/).
  • Once you log in to the Azure Portal, search for the Storage Account and click on the search result storage accounts as highlighted below.
Azure SQL Managed Instance Backup
  • Click the +Add button to create the accounts on the Storage accounts page.
backup azure database

You must provide the details below on the Create Storage Account page.

  • Subscription: Choose the correct subscription that you want to use to create the storage account.
  • Resource Group: Select a resource group. If you don’t have an existing resource group, you can click on the Create new link to create a new resource group.
  • Storage account name: You must provide a unique Azure Storage Account name.
  • Location: Select the location or Region for your storage account.
  • Performance: Select the performance as standard or Premium based on your business requirement.
  • Account Kind: You can select the Account Kind option based on your needs.
  • Replication: You can select this option based on your business requirements.

Finally, click on the Review + Create button.

how to take backup in azure sql database
  • Once you click on the Review + Create button, the system will validate and show you the validation passed message, and then you can see the Create button is enabled; click on the Create button to create the Azure Storage account.
  • It will take a few seconds, and then you can see that your deployment is completed. You must click the Go to Resource button to navigate to your created storage account.
Backup Azure SQL Database To Blob Storage

As a next step, we need to create the

Create Azure Blob container

You need to follow the below steps to create the Azure Blob container

  • Log in to the Azure Portal(https://portal.azure.com/).
  • Navigate to the Azure storage account that you have created above.
  • Click on the Containers link from the left navigation and then click the + Container button.
backup azure database
  • On the new container page, provide the name for the container, and then click on the Create button to create the container. It will take a few seconds.
backup sql azure database

We need to copy the container URL we will use in the below steps. To copy the URL of the container, Navigate to the Azure container and then on the container page, click on the Properties option from the left navigation. Click on the copy button next to the URL to copy the container URL as shown below.

How to Backup Azure SQL Database To Blob Storage

Now, we need to make the changes at the SQL server end. Now, we need to create a SQL Server Credential.

Create A SQL Server Credential

To create an SQL Server credential, we need an access key to the storage account we created above. To copy the access key, log in to the Azure Portal–> Navigate to the Storage account we have created above–> Click on the access keys link from the left navigation, copy any of the keys, and keep it in a notepad.

Login to your SQL server and then run the below SQL script to create an SQL server credential.

Using the Access Key

USE master
GO
CREATE CREDENTIAL mySQLBackups 
--your storage account name:
WITH IDENTITY='demostoragesql5',
-- Your storage account key from portal
SECRET = 'PhGbyBpKemOkwLfTldjguJR6SdutqEtaVsm5BBrUtVQV63bBGYaq1T9H3TeozILQl47+Wi1zVpPgX2qt34bN5w=='
GO

Make sure to replace the access key of your storage account and the name of your storage account.

After running the above script, you can see the SQL Server Credential has been created successfully, as highlighted below.

azure sql database backup

If you don’t have an existing database, you can create a new Database using the below SQL query

Create Database TSINFODB

You can see the database has been created successfully

azure sql database manual backup

Back up database

Approach-1: Using the SQL Script

The next step is to use the below SQL script to back up the Azure SQL database.

BACKUP DATABASE TSINFODB
TO URL = N'https://testdemo34.blob.core.windows.net/mynewdb/TSINFODB_backup_2021_01_11_184937.bak'
WITH credential = 'mySQLBackups';
GO

Make sure to replace the URL and credential value with yours.

Once you run the above SQL script, Navigate to the Azure Portal and open the container, and you will find the Database there.

Approach-2: Using the In Build SQL Server Back UP Wizard

Or, another approach is taking the Azure SQL Database backup to the Blob storage. Follow the below steps.

  • Connect to the SQL Server –> Expand the Databases node –> Right-click on the Database –> Select the Tasks option –> Click on the Back Up option as highlighted below.
azure sql manual backup

On the Back-Up Database wizard, Provide the below details

  • Back up to: Select the URL option here.
  • Filename: Provide a file name, or you can keep the default name.
  • SQL Credential: Select the SQL credential that you have created above. You can also create a new SQL credential by clicking on the Create button, as highlighted below.
  • Azure Storage Container: Provide the Azure Storage container you created above.

Finally, you need to click on the OK button.

how to backup azure sql database to azure blob storage

Using the Shared Access Signature (SAS)

The first step is to create a shared access signature (SAS), Follow the below steps to generate the Shared Access Signature (SAS).

  • Navigate to the Azure storage account that you are using here.
  • Click the Shared Access signature link from the left navigation on the Azure Storage account page.
  • Select the options below as highlighted.
  1. Allowed Services: Select the Blob option.
  2. Allowed resource types: Select the Container option.
  3. Start and expiry date/time: Select an expiry date and time for the Shared Access Signature (SAS).
  4. Allowed IP addresses and Allowed Protocols: Select these options based on your need.

Finally, click on the Generate SAS and connection string button.

azure sql backup to blob storage

You can see below the SAS token that got generated, copy that and copy to a notepad that we will use below.

azure sql backup to storage account

Create an SQL Credential

Now, we need to create the SQL Credential using the below script.

USE master
GO
 
CREATE CREDENTIAL [https://<storage account name>.blob.core.windows.net/<container name>]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS token>'

In my case, it is like below

USE master
GO
 
CREATE CREDENTIAL [https://demostoragesql5.blob.core.windows.net/mynewdb]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2019-12-12&ss=b&srt=c&sp=rwdlacx&se=2021-01-13T14:53:51Z&st=2021-01-12T06:53:51Z&spr=https&sig=3hnMPXIytoxR4%2FIvEiOc1n8cibMVUHsHAcneucfDe3s%3D'

Note: One important point to note down here is, You need to provide the SAS token value starting from sv= and don’t include the ?.

Backup the Azure SQL DB

It would be best if you used the script below to take the Backup of the Azure SQL DB.

BACKUP DATABASE [TSINFODB]
TO URL = 'https://demostoragesql5.blob.core.windows.net/mynewdb/TSINFODB_backup_2021_01_11_184937.bak'

You can also use the below complete script at a time.

IF NOT EXISTS (SELECT * FROM sys.credentials   
               WHERE name = 'https://demostoragesql5.blob.core.windows.net/mynewdb')  
CREATE CREDENTIAL [https://demostoragesql5.blob.core.windows.net/mynewdb] 
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = 'sv=2019-12-12&ss=b&srt=c&sp=rwdlacx&se=2021-01-13T14:53:51Z&st=2021-01-12T06:53:51Z&spr=https&sig=3hnMPXIytoxR4%2FIvEiOc1n8cibMVUHsHAcneucfDe3s%3D';


BACKUP DATABASE TSINFODB   
TO URL = 'https://demostoragesql5.blob.core.windows.net/mynewdb/TSINFO123.bak' 
GO

How to backup Azure SQL Database to Local Machine

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

  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

Using Azure Portal

We have already discussed the steps to take a backup of the Azure SQL database using the Azure portal. Check out the above section for more information.

Using SQL Server Import and Export Wizard

Now, we can easily take the backup of the Azure SQL database using the built-in SSMS SQL Server Import and Export Wizard by following the below steps.

  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 database you want to export.
  3. The third step is to right-click the database name, click the Tasks option, and then select the Import Data / Export Data option.
azure sql database manual backup

4. On the Import/Export wizard, choose the Data Source as “.Net Framework Data Provider for SQLServer” and provide the connection string that you will get from the Azure Portal ( In the Azure Portal, navigate to the SQL Databases –> select the database for which one you need to get the connection string –> Select the Connection Strings link from the left navigation –> Click on the ADO.NET tab and copy the connection string ). Finally, click on the Next button.

Backup Azure SQL database

5. On the next screen, If everything is fine, it will ask to select the Destination parameter as SQL Server, Access, Excel, Flat file, etc.

6. If the connection is established properly, it will ask you to select all the data you want to export. The final step is to run the export to get the data.

Using BCP utility

Using the Azure portal, you can also use the SqlPackage utility to take a backup of the Azure SQL database. You must follow the steps below to back up the Azure SQL database. Before that, make sure to install the BCP Utility.

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

tsinfobackupdb is the name of the database. The TSDemo 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.

Using SqlPackage utility

We can also export an Azure SQL Database using the 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.

Let’s discuss an example: you can export a database using SqlPackage.exe with Active Directory Universal Authentication using the script below

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

Using SSIS Tools

SSIS is an excellent platform that allows you to back up 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 your .dtsx file, you can run it either with the DTEXEC.EXE command-line utility or with the help of the DTEXECUI application.

Suppose your .dtsx file name is Demo.dtsx file. Then you can run it like the below

DTEXEC.EXE /F "Demo.dtsx"

Using SqlBackupAndFtp

You can also use a very powerful tool, i.e., SqlBackupAndFtp, which can take the backup of the Azure SQL database. You can use the steps below to take the Azure SQL database backup.

  1. You can download and install the SqlBackupAndFtp.
  2. Now, you need to connect, and it will ask you to provide an Azure SQL Server name, a username, and a password.
  3. Click on the Select Databases link, then check the database and click the Save & Close button.
  4. On the “Store backups” section, select destination. You need to select the local folder, a network folder, NAS, etc.
  5. The last step would be to click on the Run the Job button. You can Run immediately with the “Run Now” option or schedule the backup. 

Issues and Challenges

You might encounter a few issues and challenges while taking the SQL DB backup to the Blob storage like below.

Generate sas and connection string greyed out

While generating the Shared Access Signature (SAS) for the storage account, you might notice that the Generate sas and connection string button is not enabled or it’s greyed out.

Solution

The solution for this issue is, that you need to make sure to select any of the options as per your requirement for the Allowed resource types option.

Generate sas and connection string greyed out

Backup/Restore to URL device error: Error while decoding the storage key.

This error you might get while running the SQL script to take the backup of the SQL database to the Azure Blob storage.

Solution

The script should be like below

IF NOT EXISTS (SELECT * FROM sys.credentials   
               WHERE name = 'https://demostoragesql5.blob.core.windows.net/mynewdb')  
CREATE CREDENTIAL [https://demostoragesql5.blob.core.windows.net/mynewdb] 
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = 'sv=2019-12-12&ss=b&srt=c&sp=rwdlacx&se=2021-01-13T14:53:51Z&st=2021-01-12T06:53:51Z&spr=https&sig=3hnMPXIytoxR4%2FIvEiOc1n8cibMVUHsHAcneucfDe3s%3D';


BACKUP DATABASE TSINFODB   
TO URL = 'https://demostoragesql5.blob.core.windows.net/mynewdb/TSINFO123.bak' 
GO

Note: You need to provide the SAS token value starting from sv= and don’t include the ?.

A Backup device of type URL was specified without a Credential, Backup/Restore operation cannot proceed.

Sometimes you might get the error A Backup device of type URL was specified without a Credential, Backup/Restore operation cannot proceed. At the same time, running an SQL script takes the backup of the SQL DB to the Blob storage.

Solution

Along with the Database base SQL script, you need to add the below line

WITH credential = 'https://demostoragesql5.blob.core.windows.net/mynewdb';

Here https://demostoragesql5.blob.core.windows.net/mynewdb is the name of the SQL credential.

Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request. (Microsoft.SqlServer.Smo)

While trying to take the backup of the SQL DB, you might get the error Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request.. (Microsoft.SqlServer.Smo).

Backup to URL received an exception from the remote endpoint. Exception Message. The remote server returned an error (400) Bad Request.. (Microsoft.SqlServer.Smo)

The complete error message is as below

System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file “https://demostoragesql5.blob.core.windows.net/mynewdb/TSINFODB_backup_2021_01_12_151612.bak:” Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request.. (Microsoft.SqlServer.Smo)


For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.41011.9+(SqlManagementObjects-master-APPLOCAL)&LinkId=20476

Cause

When saving a blob file to a Non-Blob Storage or not using the Blob Storage Endpoint. SQL Server backups can only be saved to a Blob Storage. If you choose the Storage account as General Purpose Storage, you will have multiple endpoints for different usage.

Solution

To fix this error, You need to make sure the following stuff

While creating the Azure Storage account, If You select the Account kind as General Purpose. You will get multiple Endpoints for different purposes.

how to backup azure sql database

Make sure to choose the correct endpoint, i.e., if you are trying to store the SQL DB to the blob storage, then you should choose the Endpoint, that is meant for the Blob storage, not for other purposes like File, Queue, Table, etc.

Fix: SQL Backup to URL Error – The remote server returned an error: (400) Bad Request

FAQs

You have the standard service tier. How many days are your backups retained?

Note that the backup retention is based on the service tier you have. The answer to this question is 14 days for the standard tier, whereas, in the case of the Basic tier, it is 7 days, and for the Premium tier, it is 35 days.

You can check out the quick steps to restore Azure SQL Database.

You may like the following Azure tutorials:

Conclusion

In this tutorial, we learned how to backup Azure SQL database. I hope you have enjoyed this article !!!