How To Backup Azure SQL Database

This Azure tutorial will discuss various approaches to backup Azure SQL database.

How to backup Azure SQL database

Follow the below steps for Azure SQL backup using the Azure portal.

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

2. Click on the Azure SQL database you created.

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

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.

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

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 the prerequisites 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.

Click the +Add button to create the accounts on the Storage accounts page.

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, click on the Create new link to create one.
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. 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.

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.

On the new container page, provide the container’s name and then click the Create button to create it. It will take a few seconds.

We need to copy the container URL using the steps below. To do so, Navigate to the Azure container, and then on the container page, click on the Properties option from the left navigation. Click 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

We need an access key to the storage account we created above to create an SQL Server credential. To copy the access key, log in to the Azure Portal, navigate to the Storage account we created above, click on the access keys link from the left navigation, copy any of the keys, and keep them in a notepad.

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.

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

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. The database will be 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.

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.

Allowed Services: Select the Blob option.
Allowed resource types: Select the Container option.
Start and expiry date/time: Select an expiry date and time for the Shared Access Signature (SAS).
Allowed IP addresses and Allowed Protocols: Select these options based on your need.

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

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

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

Backup Azure SQL database to local

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

Using Azure Portal
Using SQL Server Import and Export Wizard
Using SqlPackage utility
Using SSIS Tools
Using BCP utility
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 backup the Azure SQL database using the built-in SSMS SQL Server Import and Export Wizard by following the below steps.

The first step is to connect to the SQL Server instance.
Navigate to the Object Explorer window, expand the node, and select the database you want to export.
The third step is to right-click the database name, click the Tasks option, and then select the Import Data / Export Data option.

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.

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, you will be asked to select all the data you want to export. The final step is to run the export to get the data.