How to backup Azure SQL database

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

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.

How to backup Azure SQL database

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

How to configure backup Azure SQL database

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

Configure backup Azure SQL database

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.

Create backup Azure SQL database

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

Configure backup Azure SQL database

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

Why to Configure backup Azure SQL database

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

How to Configure backup 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.
How to backup Azure SQL Database to Local Machine

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 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.
How to Create Azure storage account
  • On the Storage accounts page, click on the +Add button to create the storage accounts.
Create Azure storage account

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.

Steps to Create Azure storage account
  • 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.
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 on the + Container button.
How to Create Azure Blob container
  • 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
Create Azure Blob container

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.

How to Backup Azure SQL Database To Blob Storage

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.

How to Create A SQL Server Credential

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

Create A SQL Server Credential

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.
 How to take Azure SQL DB Backup using Back Up Database wizard

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.

Backup On-Premise SQL Server to Azure BLOB Storage

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.

How to connect and perform a SQL Server database restore from Azure BLOB storage

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.

Azure sql server backup to Azure blob

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.

Generate sas and connection string greyed out
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. 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).

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.

Backup to URL received an exception from the remote endpoint. Exception Message.

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.

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

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.
Azure SQL Database Backup And Restore To Different Server

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.

import the local SQL server database to Azure

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 onlyData onlySchema, 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:

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 !!!