Azure SQL Database Backup and Restore Overview

By:   |   Comments (7)   |   Related: > Azure Backup and Restore


I have written several tips about how to manage SQL Server when using Azure SQL Database. In this tip we look at how to create a backup for a database, since this does not quite work the same way for an Azure SQL Database. If I issue a BACKUP DATABASE statement as shown in the following screenshot, I get an error saying "Backup Database is not supported in this version of SQL Server".

sql query

So, if the BACKUP command is not available for an Azure SQL Database how can we backup the databases?  Also, how about doing restores?


This is my sixth article that is part of a tutorial to help people that are moving to SQL Azure. If you did not read my previous articles and want to learn how to create a database in Azure SQL Database, perform basic configurations, operations etc., please check out the following links and then return to this article to continue your learning.

  1. SQL Azure Create Database Tutorial
  2. Overview of Basic Configuration Options for SQL Azure
  3. How to use Azure SQL Database features and settings
  4. Different Ways to Connect to SQL Azure databases
  5. Migrate a SQL Server database to SQL Azure

You should use this article as a tutorial and follow it step-by-step. I will also try to share my experience on the differences that I have found when I was moving from SQL Server to Azure SQL Database.

In this tip I will explain how SQL Azure backup and restore works.

Backups for Azure SQL Database

In SQL Azure, database backups are executed automatically and it is not possible to change this behavior. This is a service offered when you create a SQL Azure database and the first full backup occurs immediately after you create a new SQL Azure database and the rest of the backups are scheduled by SQL Azure itself. We cannot see the schedule, but Microsoft says that Full database backups happen weekly, differential database backups generally happen every few hours, and transaction log backups generally happen every 5 - 10 minutes. This means that all SQL Azure databases are using Full Recovery Model, so we can always restore to a point-in-time.

The backup retention period depends on the SQL Azure database service-tier. Basic service-tier offers a 7 day retention policy and Standard and Premium service-tiers offer 35 day retention policies. Be aware that when you downgrade from a Standard or Premium service-tier to the Basic service-tier, you will lose backups older than 7 days. You can retain longer retention for SQL Azure backups by configuring a long-term retention.

Long-term backup retention

NOTE: This feature was still a Preview feature when I wrote this article, meaning that you will need to accept the current terms if you want to use it and features may change in the future.

The long-term retention feature allows you to store SQL Azure full database backups up to 10 years. It uses the Azure Recovery Services vault that allows up to 1000 databases per vault.

What the long-term backup retention does is to copy any existing full backups from the selected database(s) to a Recovery Service Vault and will keep those full backups for the configured retention time. Let's see how to configure a database to use the long-term backup retention.

The long-term backup retention can be configured in the SQL Azure server, but as a prerequisite it needs a Recovery Service Vault to store the backups. This service does not appear by default in the Azure options so we need to search for it. Go to the bottom of the Azure menu (left pane in the Azure portal) and click on the More services > option. This will open a new pane where it allows you to find any Azure service. Type ‘vault’ in the Search field. As you are typing it will automatically filter the results. You will see 4 services with the word ‘vault’:

Azure add more services - Description: Azure add more services

Choose the Recovery Services vaults. This will present a screen similar to the following:

Azure Recovery Services vaults - Description: Azure Recovery Services vaults

Click on the Add button to create a new Recovery Service vault. The following pane will be presented:

Add Recovery Services vault - Description: Add Recovery Services vault

Provide a Name and assign it to an Azure Subscription, Resource group and Location. The Recovery Service vault needs to be created in the same Azure subscription and location of the SQL Azure server.

If you want it to appear in the Dashboard, check the “Pin to dashboard” option. Then click on the Create button to create the new Recovery Service vault resource.

Now that we have a Recovery Service vault resource we can configure a long-term backup retention policy. To do so, select your SQL Azure server and click on the Long-term backup retention option as shown in the below image:

Long-term backup retention - Description: SQL Azure long-term backup retention

NOTE: You will need to accept the terms before using the long-term backup retention.

In the right pane, select from the database grid, the database or databases that you want to configure a long-term backup retention for. The Configure button in the context toolbar should be enabled after you select one or more databases from the grid. Click on the Configure button to set the new long-term backup retention policy.

Configure long-term backup retention - Description: Configure SQL Azure long-term backup retention

Click on the Recovery service vault to select the previously created Recovery Service vault. Create a new retention policy by entering a name and selecting from the combo boxes the desired retention period (by default is 1 year selected). With all fields completed the OK button at the bottom will be enabled. Click on it to create the new long-term backup retention and go back to the previous screen where you can Save this new long-term backup retention policy:

Save Long-term backup retention policy - Description: Save Long-term backup retention policy

Let some time pass and then check the Activity log for the Resource service vault to confirm that the copy of the backups is being performed correctly. You should be able to see Write operations, meaning that Full Backups are being stored in the Resource service vault:

activity log

NOTE: The process of copying the Full backup may take some time to show in the Resource service vault.

Restores for Azure SQL Database

Let's see some scenarios where and how to use the SQL Azure restore feature.

Restore a deleted database

Let start with maybe the most common need to restore a database that is when you inadvertently delete a database. I have previously deleted a database, so I can show you in this tip how to recover it.

To restore a deleted database, in the Azure Portal go to the SQL Azure server where the deleted database was stored and then click on the Deleted databases option:

SQL Azure deleted databases - Description: SQL Azure deleted databases

In the above screen you can see that I have a deleted database in this SQL Azure server. All the deleted databases will be listed in the right pane.

NOTE: If a database was deleted earlier than the last backup retention period you will not be able to restore it anymore. This mean, 7 days for a Basic Edition and 30 days for other editions.

To recover a deleted database, just click on the respective row in the right pane. This will present a similar screen as the following:

SQL Azure restore deleted database - Description: SQL Azure restore deleted database

Provide the new name for the database and click on the OK button to start the restore process.

Wait for the notification to confirm that the deleted database has been restored:

SQL Azure restore confirmation - Description: SQL Azure confirmation of the restore of a deleted database

You can now confirm in the SQL Azure server that the deleted database has been restored successfully:

SQL Azure deleted database restored - Description: SQL Azure deleted database restored

Point in time restore

In SQL Azure, when restoring from automated backups, it is always a point in time restore, meaning that you do not choose the backup file or files to be restored, but provide the exact restore point in time.

When restoring from an existing database, you must provide a restore point in time. I will simulate an inadvertently truncated table command so I can use the point in time restore to show how it works. Using SQL Server Management Studio (SSMS) I will register the current date and time so I know when was the last time I saw the correct records. I will also show the contents of the language table so I can confirm that all rows will be restored:

SSMS regist current date and time - Description: SSMS regist current date and time and return table records

We can confirm that at 14:39, 9 records existed in the languages table.

I will now run the truncate the table and register the time when it occurred and also confirm that the table was empty after the truncate table command:

SSMS truncate table command - Description: SSMS truncate table command

With this I can confirm that after 14:42 the languages table has been truncated. No records could be found in the table.

Let's start with the database restore process by accessing the respective SQL Azure database in the Azure portal and positioning ourselves in the Overview option. We can see in the top of the right pane, the context menu for SQL Azure databases and below that menu, the information about the database where we can also find the oldest restore point time. This tells us that we cannot restore the database before the presented date and time (unless we have a long-term backup retention):

SQL Azure restore option - Description: SQL Azure restore option

The Restore option is the second item in the context menu. Click on it to start the restore process.

Note that SQL Azure does not let you restore over an existing database, so you have two options here:

  1. Restore the database providing a new database name
  2. Rename the current database name so you can restore to a new database using the old database name
SQL Azure restore point in time - Description: SQL Azure restore point in time

I provided a new name for the restored database and explicitly stated that I want the database to be restored to 14:39:00 (2:39:00 PM) as that was the last time I knew the records were in the languages table.

Click on the OK button when you have provided all the necessary information for the restore operation and wait for the notification that the database has been restored:

SQL Azure new database restored - Description: SQL Azure notification for the new database restored

Let's confirm that the database has been successfully restored and the languages table has records:

Confirm data has been restored - Description: Confirm data has been restored

So, at 15:13, after restoring the database, we can confirm that table languages has all of its records again.

Restore from a long-term backup retention

In the case that you want to restore from a long-term backup retention, select the desired database that you want to be restored.

NOTE: Restoring from a long-term backup retention, does not allow you to restore to a point-in-time. It only restores from a full backup.

In my below example, my databases oldest restore point from the automatic backups is 2017-12-05, but I want to restore from a previous date:

SQL Azure database option - Description: SQL Azure database option

Click on the Restore button and then click on the Long-term tab so the option to restore from the Azure vault backups will be available. Click on that option to have a list of all the stored full backups so you can select the full backup to restore from. In my case I will select the oldest one (2017-12-01) and this will make the Select button enabled.

SQL Azure restore from long-term backup retention - Description: SQL Azure restore from long-term backup retention

Click on the Select button to return to the previous pane, where you can confirm that you have selected the correct backup to be restored from. Provide the name for the restored database:

Confirm SQL Azure restore - Description: Confirm SQL Azure restore

Click on the OK button and wait for the notification that states that the database has been fully restored:

Azure database restored notification - Description: Azure database restored notification

With this I have completed this Azure SQL Database backup and restore article.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Vitor Montalvao Vitor Montalvão is a SQL Server engineer with 20 years of experience in SQL Server, specializing in performance & tuning, data modelling, migration and security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, June 9, 2020 - 12:15:59 PM - Vitor Montalvão Back To Top (85880)

Hi Madhana,

If you perform the backup to an Azure Storage then you should be able to access it from your on premise SQL Server instance.


Vitor Montalvão

Monday, June 8, 2020 - 11:36:13 PM - MadhanaGopal Back To Top (85860)


I have a SQL Instance running on Azure (PAAS Model).

Is there a way i can take the backup and restore it on Premise SQL Database Instance.

Thanks and Regards


Wednesday, July 24, 2019 - 1:36:54 PM - Vitor Montalvão Back To Top (81858)

Hi Sam,

Unfortunately there's no MSDB database in Azure SQL Database so you can't get the restore history.


Vitor Montalvão

Wednesday, July 24, 2019 - 10:40:11 AM - Sam Back To Top (81852)

For on-premises we can get restore history details from MSDB tables.

Do we have any query to get restore details in Azure SQL Database?

Wednesday, July 24, 2019 - 10:37:42 AM - Sam Back To Top (81851)


Do we have any query to check the restore history in SQL Azure.



Thursday, July 4, 2019 - 7:34:49 AM - Subhraz Back To Top (81676)

Very helpful to know Azure SQL backup and restore work quite differently.

Wednesday, April 4, 2018 - 10:25:57 AM - Kevin3NF Back To Top (75604)

Thanks for this article...we are planning a move but Global IT won't let us use features that are "In Preview" such as Long-term retention. 


get free sql tips
agree to terms