How to backup Azure SQL database

In this azure tutorial, we will discuss How to backup Azure SQL database. Apart from this, we will also discuss on few other topics

• Backup Azure SQL database
• How to backup Azure SQL Database to Local Machine
• How to take backup of azure SQL database
• How to take backup of SQL Azure database
• How to manually backup azure SQL database to storage account
• Backup Azure SQL Database To Blob Storage
• Azure SQL Managed Instance Backup
• How to restore database backup(.bak) in SQL Azure
• Azure SQL Backup Pricing
• Generate sas and connection string greyed out
• Backup/Restore to URL device error: Error while decoding the storage key.
• A Backup device of type URL was specified without a Credential, Backup/Restore operation cannot proceed.
• Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request.. (Microsoft.SqlServer.Smo)
• Azure SQL Database Backup And Restore To Different Server
• You have the standard service tier. how many days are your backups retained?

How to backup Azure SQL database

When we are thinking of the way to take the backup of the Azure SQL database, there are many questions coming into our mind like How do I back up my Azure SQL Database?, 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

Backup Azure SQL database

• Azure SQL Database service tiers
• How to create an Azure SQL database

Let’s discuss here, how to take backup of azure sql database from portal. Follow the below steps to back up the Azure SQL database.

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

Step-2: Click on your Azure SQL database you created.

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

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

Step-5: Fill the below options based on your business requirement.

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 for How long would you like weekly backups to be kept?

Same way 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.

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

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

Now you can see the changes are reflecting for your Azure SQL database

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

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 above, the steps to take backup of azure sql database using Azure portal. Check out the above section for more information.

Using SQL Server Import and Export Wizard

Now, easily we can take the backup of the Azure SQL database using 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 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 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 then it will ask to select the Destination parameter as SQL Server, Access, Excel, Flat file, etc.

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

Using BCP utility

You can also use the SqlPackage utility to take backup of azure sql database using Azure portal. You need to follow the below steps 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 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.

Let’s discuss 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:"test.onmicrosoft.com"

Using SSIS Tools

SSIS 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 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 below

DTEXEC.EXE /F "Demo.dtsx"

Using SqlBackupAndFtp

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

1. You can download and install the SqlBackupAndFtp.
2. Now you need to connect and it will ask to provide an Azure SQL Server name, a user name, and a password.
3. Click on the Select databases link and then check the database and click on 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, click on the Run the job button. You can Run immediately with the “Run Now” option or you can also schedule the backup if you want. 

Backup Azure SQL Database To Blob Storage

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

Azure SQL Managed Instance Backup

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 an 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 logged in to the Azure Portal, search for the Storage Account and click on the search result storage accounts as highlighted below.

• On the Storage accounts page, click on the +Add button to create the storage accounts.

On the Create storage account page, you need to provide the below details.

• Subscription: Choose the correct subscription that you want to use to create the storage account.
• Resource Group: Select a resource group and 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 need to provide a unique name for your Azure Storage Account.
• 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 need.
• Replication: This option you can select based on your business requirement.

Finally, click on the Review + Create button.

• Once, you will click on the Review + create button, the system will validate and will show you the validation passed message, and then you can able to 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 able to see that your deployment is completed and you need to click on the Go to resource button to navigate to the storage account that you have created.

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 on the + Container button.

• 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

We need to copy the container URL that we are going to use in our 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.

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

Create A SQL Server Credential

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

Login to your SQL server and then run the below SQL script to create a 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 able to 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 able to see the database has been created successfully

Back up database

Approach-1: Using the SQL Script

Now, the next step is to use the below SQL script to backup 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 will 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, There is one more approach to take the Azure SQL DB 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 as it is.
• SQL Credential: Select the SQL credential that you have created above. If you want, 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 that you have created above.

Finally, you need to click on the Ok button.

Using the Shared Access Signature (SAS)

The first step is we need 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.
• On the Azure Storage account page, click on the Shared access signature link from the left navigation.
• Select the below options 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 connectionstring button.

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

Create a 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

You need to use the below script 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'

Or, 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

Issues and Challenges

You might get 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, you need to make sure to select any of the options as per your requirement for the Allowed resource types option.

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. while running a SQL script to take 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).

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 You are trying to save 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 are choosing the Storage account kind as General Purpose Storage, you will have multiple endpoints for different usage.

Solution

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

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

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 which is mean for the Blob storage, not for other purposes like File, Queue, Table, etc.

How to restore database backup(.bak) in SQL Azure

You can use the below SQL script to restore the SQL database

RESTORE DATABASE Database_Name FROM URL = 'https://demostoragesql5.blob.core.windows.net/mynewdb/TSINFO123.bak'
WITH CREDENTIAL = 'Name of your SQL credential',
MOVE 'Database' to 'D:\DATABASES\TSINFORestored.mdf',
MOVE 'Database_log' to 'M:\LOGS\TSINFORestored.ldf',
BLOCKSIZE = 512

Azure SQL Database Backup And Restore To Different Server

Well, here we will discuss Azure SQL Database Backup And Restore To Different Server. Follow the below information.

1. Connect to the SQL Server Management Studio and locate the database.
2. Right-Click on the database name –> select the Tasks option –> Click on the Generate Scripts option.

3. From the left side, Click on the objects to export tab, for the select the database objects to script option, you can select the script the entire database and all database objects or select specific database objects based on your need and then click on the Next button.

4. Now, select the option how to save the script like save the script to file, clipboard, new query window, etc on the Set scripting options window.

5. Now, if you want, you can set the advanced scripting options also.

6. Click on the Advanced button and set the Script for the database engine type property to SQL Database if you don’t want the stand-alone SQL Server instance. Then you need to click on the Next button.

7. Then, set the Types of data to script option to Schema only, Data only, Schema, and data based on your requirement.

8. Finally, click on the Finish button. then it will generate the script on the local path provided by you.

Restore To Different Server

Now in order to restore it to a different server, Follow the below steps.

1. Connect to the Destination SQL Server Management Studio.
2. Create a new database with exactly the same database name as the source.
3. Select the newly created database and run the above-generated script to restore the database.

Azure SQL Backup Pricing

In Azure SQL Database, the databases are backed up automatically for Basic, standard, and premium versions. These backups are retained for 7 days, 14 days, and 35 days respectively.

The price of the backup depends on your Azure SQL purchase models i.e DTU or vCore. The cost of the Backup storage depends on the Azure Storage redundancy (LRS, ZRS, RA-GRS).

For more details on the pricing details, you can check out the Microsoft official site.

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 may like following Azure tutorials:

• Azure AD group membership PowerShell
• How to create SQL managed instance in azure
• What is Microsoft Azure for Students: FAQS, How to Sign-Up and More
• What Is the Difference Between Azure and Office 365?
• How To Call A Stored Procedure From Azure Functions

Conclusion

This tutorial, we learned the below things:

• How to backup Azure SQL database
• Backup Azure SQL database
• How to take backup of azure SQL database
• How to take backup of SQL Azure database
• How to manually backup azure SQL database to storage account
• How to backup Azure SQL Database to Local Machine
• Backup Azure SQL Database To Blob Storage
• Azure SQL Managed Instance Backup
• How to restore database backup(.bak) in SQL Azure
• Azure SQL Backup Pricing
• Generate sas and connection string greyed out
• Backup/Restore to URL device error: Error while decoding the storage key.
• A Backup device of type URL was specified without a Credential, Backup/Restore operation cannot proceed.
• Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request.. (Microsoft.SqlServer.Smo).
• Azure SQL Database Backup And Restore To Different Server
• You have the standard service tier. how many days are your backups retained?

Hope you have enjoyed this article !!!