This Azure tutorial will discuss various approaches to backup Azure SQL database.
Table of Contents
- How to backup Azure SQL database
- Backup Azure SQL Database To Blob Storage
- How to backup Azure SQL Database to Local Machine
- Issues and Challenges
- 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)
- FAQs
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.
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.
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.”
Now you can see the changes are reflected in your Azure SQL database
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.
- 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.
- 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 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.
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.
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.
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.
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 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
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
- 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 take the backup of 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, 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.
- You can download and install the SqlBackupAndFtp.
- Now, you need to connect, and it will ask you to provide an Azure SQL Server name, a username, and a password.
- Click on the Select Databases link, then check the database and click the Save & Close button.
- On the “Store backups” section, select destination. You need to select the local folder, a network folder, NAS, etc.
- 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.
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).
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.
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.
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:
- Azure SQL Database service tiers
- How to create an Azure SQL database
- How to create SQL managed instance in Azure
Conclusion
In this tutorial, we learned how to backup 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