Skip to content

How to Configure Azure SQL Backup Retention and Restore a Database (Point in Time)

Published: at 01:44 PM

Table of contents

Open Table of contents


I am currently working on an Azure project that is focused on high availability and backup and restore operations. I have never been a “SQL” person or database admin, so this aspect at first appeared a little daunting.

After digging in, I was pleasantly surprised to see that Microsoft has built-in mechanisms that make it pretty straightforward to manage the backup, retention, and restore processes.

Automated Backups

The first bit of business is to understand the backup process. This article outlines the specifics around the automated backups for SQL databases in Azure. Below are a couple of key highlights from the article.

Backup frequency

Azure SQL Database creates:

Backup storage redundancy

By default, Azure SQL Database stores backups in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases in a different region in the event of a regional outage.

The storage redundancy mechanism stores multiple copies of your data so that it’s protected from planned and unplanned events. These events might include transient hardware failure, network or power outages, or massive natural disasters.

To ensure that your backups stay within the same region where your database is deployed, you can change backup storage redundancy from the default geo-redundant storage to other types of storage that keep your data within the region. The configured backup storage redundancy is applied to both short-term retention (STR) backups and LTR backups. To learn more about storage redundancy, see Data redundancy.

You can configure backup storage redundancy when you create your database, and you can update it at a later time. The changes that you make to an existing database apply to future backups only. After you update the backup storage redundancy of an existing database, the changes might take up to 48 hours to be applied.

You can choose one of the following storage redundancies for backups:

  • Geo-restore is disabled as soon as a database is updated to use locally redundant or zone-redundant storage.

  • The storage redundancy diagrams all show regions with multiple availability zones (multi-az). However, some regions provide only a single availability zone and do not support ZRS.

Configure Retention

To configure the retention settings, locate your SQL Server in the Azure portal. Select “Backups”, under “Data management”.

Within the “Backups” blade, you will see several details such as the available backups and you can configure the retention settings. To do so, click on “Retention policies” and select the policy you wish to configure by selecting “Configure policies”.

The next screen presents you with the settings. Configure them per your requirements.

Restore a Database


Point-in-time restore is useful in recovery scenarios, like for an incident that’s caused by error or failure when data doesn’t load correctly, or if crucial data is deleted. You can also use it simply to test and audit your database deployment. Azure backup files are kept for 7 to 35 days depending on your database settings.

You can use point-in-time restore to restore a database in these scenarios:

Restore an existing database

You can restore an existing database in the same subscription by using the Azure portal, PowerShell, or the Azure CLI. If you restore to a different instance in the same subscription by using PowerShell or the Azure CLI, be sure to specify the properties for the target SQL Managed Instance resource. The database is restored to the same instance by default.

To restore an existing database, you can do so by going to the database page in the Azure portal, and selecting Restore.

Alternatively, to restore your database, you can follow these steps:

  1. Sign in to the Azure portal.

  2. Go to the target SQL Managed Instance where you plan to restore your database to.

  3. On the Overview page, choose + New database to open the Create Azure SQL Managed Database page.

    Screenshot that shows the SQL Managed Instance overview pane in the Azure portal, with adding a new database selected.

  4. On the Basics tab of the Create Azure SQL Managed Database page, provide subscription and resource group details under Project details. Then, under Database details provide the new name of the database you plan to restore. Confirm the correct managed instance is listed in the drop down. Then select Next: Data source >

    Screenshot of the Azure portal that shows the Basics tab of the Create Azure SQL Managed Database page.

  5. On the Data source tab, choose Point-in-time restore under Use existing data. Provide the subscription, resource group and managed instance that contains the source database. From the Managed database drop-down, choose the database you want to restore, and then choose the point in time you want to restore the database from. The source and target instance can be the same, or two different instances. Select Next : Additional settings >

    Screenshot of the Azure portal that shows the data source tab of the Create Azure SQL Managed Database page, with point-in-time restore selected.

  6. On the Additional settings tab, you can check the box to inherit the retention policy from the source database, or, alternatively, you can select Configure retention to open the Configure policies page, and set your desired retention policies for your restored database. When finished, select Review + create.

    Screenshot of the Azure portal that shows the additional settings tab of the Create Azure SQL Managed Database page.

  7. On Review + create, when validation is successful, select Create to restore your database.

This action starts the restore process, which creates a new database and populates it with data from the original database at the specified point in time. For more information about the recovery process, see Recovery time.

Restore a Deleted Database

To restore a deleted managed database by using the Azure portal:

  1. In the Azure portal, go to your source managed instance.

  2. In the left menu under Data management, select Backups.

  3. Under Show databases, select Deleted.

  4. For the database to restore, select Restore.

    ![Screenshot that shows available databases in the portal, with the Restore button highlighted to restore a deleted database.]( align=“left”)

  5. In Create Azure SQL Managed database, enter or select details for the target managed instance to restore your database to. Select the Data source tab.

  6. In Data source, enter or select the details for your source database. Select the Additional settings tab.

  7. In Additional settings, configure retention settings. Select the Review + create tab.

  8. In Review + create, select Create to restore your deleted database.

Overwrite an Existing Database

To overwrite an existing database, you must do the following:

  1. Drop the original database that you want to overwrite.

  2. Rename the database restored from the point-in-time to the name of the database you dropped.

Drop the original database

You can drop the database by using the Azure portal, PowerShell, or the Azure CLI.

Another option to drop the database is to connect to your managed instance directly in SQL Server Management Studio (SSMS), and then use the DROP Transact-SQL (T-SQL) command:


DROP DATABASE WorldWideImporters;

Use one of the following methods to connect to the database in your managed instance:

  1. In the Azure portal, select the database in your managed instance.

  2. In the command bar, select Delete.

    Screenshot that shows how to delete a database by using the Azure portal.

Change the new database name to match the original database name

Use SQL Server Management Studio (SSMS) to connect directly to your managed instance. Then run the following T-SQL query. The query changes the name of the restored database to the name of the dropped database you intend to overwrite.


ALTER DATABASE WorldWideImportersPITR MODIFY NAME = WorldWideImporters;

Use one of the following methods to connect to the database in your managed instance: