Azure SQL Database Backup and Restore Overview (2023)

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


I have written several tips about how to manage SQL Server when usingAzure SQLDatabase. In this tip we look at how to create a backup for adatabase, 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, Iget an error saying "Backup Database is not supported in this version of SQLServer".

Azure SQL Database Backup and Restore Overview (1)

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


This is my sixth article that is part of a tutorial to help people that are movingto SQL Azure. If you did not read my previous articles and wantto learn how to create a database in Azure SQL Database, perform basic configurations, operationsetc., please check out the following links and then return to thisarticle 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 thisarticle as a tutorial and follow it step-by-step. I will also try to share my experienceon the differences that I have found when I was moving from SQL Server to AzureSQL 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 possibleto change this behavior. This is a service offered when you create a SQL Azure databaseand the first full backup occurs immediately after you create a new SQL Azuredatabase 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 transactionlog backups generally happen every 5 - 10 minutes. This means that all SQL Azuredatabases are usingFull Recovery Model, so we can always restore to a point-in-time.

The backup retention period depends on theSQL Azure database service-tier. Basic service-tier offers a 7 day retentionpolicy and Standard and Premium service-tiers offer 35 day retention policies. Beaware 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 forSQL 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 andfeatures may change in the future.

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

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

The long-term backup retention can be configured in the SQL Azure server, butas a prerequisite it needs a Recovery Service Vault to store thebackups. This service does not appear by default in the Azure options so we needto 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 newpane where it allows you to find any Azure service. Type ‘vault’ in theSearch field. As you are typing it will automatically filter the results. You will see 4 services with the word ‘vault’:

Azure SQL Database Backup and Restore Overview (2)

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

Azure SQL Database Backup and Restore Overview (3)

(Video) Database restore in Azure SQL | Overview of Database backup | Azure SQL Tutorials

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

Azure SQL Database Backup and Restore Overview (4)

Provide a Name and assign it to an Azure Subscription,Resource group and Location. The Recovery Servicevault needs to be created in the same Azure subscription and location of the SQLAzure 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 vaultresource.

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

Azure SQL Database Backup and Restore Overview (5)

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 thatyou want to configure a long-term backup retention for. The Configurebutton in the context toolbar should be enabled after you select one or more databasesfrom the grid. Click on the Configure button to set the new long-termbackup retention policy.

Azure SQL Database Backup and Restore Overview (6)

Click on the Recovery service vault to select the previously created RecoveryService vault. Create a new retention policy by entering a name and selecting fromthe combo boxes the desired retention period (by default is 1 year selected). Withall 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 previousscreen where you can Save this new long-term backup retention policy:

Azure SQL Database Backup and Restore Overview (7)

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

Azure SQL Database Backup and Restore Overview (8)

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.

(Video) Backup/restore in Azure SQL | Azure SQL for beginners (Ep. 46)

Restore a deleted database

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

To restore a deleted database, in the Azure Portal go to the SQL Azure serverwhere the deleted database was stored and then click on the Deleted databasesoption:

Azure SQL Database Backup and Restore Overview (9)

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

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

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

Azure SQL Database Backup and Restore Overview (10)

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

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

Azure SQL Database Backup and Restore Overview (11)

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

Azure SQL Database Backup and Restore Overview (12)

Point in time restore

In SQL Azure, when restoring from automated backups, it is always a point in timerestore, 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 intime. I will simulate an inadvertently truncated table command soI can use the point in time restore to show how it works. Using SQL ServerManagement Studio (SSMS) I willregister the current date and time so I know when was the last time I saw the correctrecords. I will also show the contents of the language table so Ican confirm that all rows will be restored:

Azure SQL Database Backup and Restore Overview (13)

(Video) Automatic Database Backup in Azure SQL | Overview of Database backup |Azure SQL Tutorials

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

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

Azure SQL Database Backup and Restore Overview (14)

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 SQLAzure database in the Azure portal and positioning ourselves in the Overviewoption. We can see in the top of the right pane, the context menu for SQL Azuredatabases and below that menu, the information about the database where we canalso find the oldest restore point time. This tells us that we cannot restorethe database before the presented date and time (unless we have a long-term backup retention):

Azure SQL Database Backup and Restore Overview (15)

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

Note that SQL Azure does not let you restore over an existing database, soyou 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 databaseusing the old database name

Azure SQL Database Backup and Restore Overview (16)

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

Click on the OK button when you have provided all the necessaryinformation for the restore operation and wait for the notification that the databasehas been restored:

Azure SQL Database Backup and Restore Overview (17)

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

Azure SQL Database Backup and Restore Overview (18)

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

(Video) Backup and Restore in Azure SQL Database

Restore from a long-term backup retention

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

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

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

Azure SQL Database Backup and Restore Overview (19)

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

Azure SQL Database Backup and Restore Overview (20)

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

Azure SQL Database Backup and Restore Overview (21)

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

Azure SQL Database Backup and Restore Overview (22)

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

Next Steps
About the author

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.

View all my tips

(Video) Azure SQL Database – Como funciona o processo de backup e restore?


How do I backup and restore my Azure SQL Database? ›

  1. In the Azure portal, navigate to your server and then select Backups. ...
  2. In the Available LTR backups pane that appears, review the available backups. ...
  3. To restore from an available LTR backup, select the backup from which you want to restore, and then select Restore.
Mar 3, 2023

How to check if backup completed successfully in SQL Server? ›

Open SSMS, right click on a database then select Tasks > Back Up. A screen similar to the below image will open. After you select all of the backup options and click OK, you can monitor the progress on the lower left side of the GUI as shown in the below image. This will give you an idea of the status of the backup.

How do I enable preview features in order to use restore? ›

How do I enable preview features?
  1. Select the Gear icon in the bottom-left corner and then select Settings from the context menu. ...
  2. Type "enable preview features" in the search bar.
  3. To enable preview features, check the checkbox for Enable unreleased preview features under Workbench: Enable Preview Features.
Mar 6, 2023

How many days does the Azure SQL database allow you to go back and recover data? ›

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: From an existing database.

How do I manually backup my Azure SQL Database? ›

Run an on-demand backup
  1. In your Recovery Services vault, choose Backup items.
  2. Select "SQL in Azure VM".
  3. Right-click on a database, and choose "Backup now".
  4. Choose the Backup Type (Full/Differential/Log/Copy Only Full) and Compression (Enable/Disable). ...
  5. Select OK to begin the backup.
Feb 1, 2023

Is Azure SQL Database automatically backed up? ›

Azure SQL Database creates: Full backups every week. Differential backups every 12 or 24 hours. Transaction log backups approximately every 10 minutes.

How to get full backup history in SQL Server? ›

Checking backup history in SQL Server can be done by looking at the SQL Server error log, which will contain information about successful and failed backups. You can also use the SQL Server Management Studio to view backup history, or query the msdb system tables.

How do I check SQL database recovery progress? ›

In a SQL Server database, three main methods are available for monitoring database backup and restore progress:
  1. Use SSMS to monitor the percentage of backup and restore progress.
  2. Use the T-SQL stats keyword to show the percentage.
  3. Use Dynamic View to monitor the percentage of backup and restore completion.
Jan 17, 2019

How do you tell when a SQL database was restored? ›

We get the following database restoration history in my environment.
  1. restore_date: It shows the database restoration date.
  2. destination_database_name: We can get the destination database name using this column.
  3. user_name: it gives user name that performed the restoration for that particular database.
Mar 11, 2019

How do I enable preview in Azure? ›

  1. Sign in to the Azure portal.
  2. In the search box, enter subscriptions and select Subscriptions.
  3. Select the link for your subscription's name.
  4. From the left menu, under Settings select Preview features.
  5. Select the link for the preview feature you want to register.
  6. Select Register.
  7. Select OK.
Mar 8, 2023

What does the preview feature do? ›

Preview is a computing function to display a document, page, or film before it is produced in its final form. In the case of printed material this is known as "print preview".

How do I enable preview features? ›

To enable a Preview feature, you must be an administrator.
  1. Sign in to Power Apps.
  2. Select your environment from the top-right corner, and select Settings ( ...
  3. Select Settings > Administration.
  4. Select System Settings, and then select the Previews tab.
May 4, 2023

Why does Azure backup take so long? ›

This delay might be caused by one or more of the following: There are performance bottlenecks on the computer that's being backed up. Another process or antivirus software is interfering with the Azure Backup process. The Backup agent is running on an Azure virtual machine (VM).

What is the backup limit for Azure SQL? ›

Azure Backup supports a consistent data transfer rate of 350 MBps for full and differential backups of large SQL databases (of 500 GB).

What is the maximum time period that you can retain backups from Azure backup? ›

Azure Virtual Machine backup policy supports a minimum retention range from seven days up to 9999 days.

How do I check my Azure SQL Database backup? ›

From the Azure management portal, go to “Create a Resource,” then select “Storage,” and then “Storage account.” Provide details such as resource group name, storage account name, and location based on your preference. Optimal settings for account type, replication, and access tier can be configured as shown below.

What are the different types of backups in SQL Server? ›

SQL Server Backup Types. Microsoft SQL Server supports five types of backup: full, differential, transaction log, tail log, and copy-only backup.

How do I copy Azure SQL Database backup to local machine? ›

The Azure portal
  1. To export a database using the Azure portal, open the page for your database and select Export on the toolbar.
  2. Specify the BACPAC filename, select an existing Azure storage account and container for the export, and then provide the appropriate credentials for access to the source database. ...
  3. Select OK.
Dec 20, 2022

Does Azure backup backup all disks? ›

Azure Backup supports backing up all the disks (operating system and data) in a VM together using the virtual machine backup solution.

Where are Azure SQL Database backups stored? ›

Azure SQL Server automatically creates full backups every week, differential backups every 12 hours, and transaction log backups every 5-10 minutes. The backups are stored in RA-GRS storage blobs that are replicated to a paired data center for protection against a data center outage.

Where is Azure backup data stored? ›

Backups are stored in a Recovery Services vault with built-in management of recovery points. Configuration and scalability are simple, backups are optimized, and you can easily restore as needed.

How to restore full and log backup in SQL? ›

Right-click the database, point to Tasks, point to Restore, and then click Transaction Log, which opens the Restore Transaction Log dialog box. If Transaction Log is grayed out, you may need to restore a full or differential backup first.

How do I clean up SQL backups? ›

Delete old database backup files automatically in SQL Server using SQL Server Maintenance plan:
  1. Under Delete files of the following type: we select Backup files by clicking on the radio button.
  2. Under File location: we select Search folder and delete files based on an extension.
Jan 19, 2017

How do I restore a SQL database from a full and differential backup? ›

To restore a differential database backup
  1. After you connect to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases. ...
  3. Right-click the database, point to Tasks, point to Restore, and then click Database.
Mar 3, 2023

How long does a SQL database restore take? ›

The majority of database restores complete within 12 hours. There is no built-in functionality to do bulk restore. The Azure SQL Database: Full Server Recovery script is an example of one way of accomplishing this task.

How long does SQL recovery take? ›

By default, the target recovery time is 60 seconds, and the database uses indirect checkpoints.

What are the phases of restore in SQL Server? ›

SQL Server database recovery consists of three phases: the analysis phase, the redo phase, and finally the undo phase.

What is the difference between database restore and recovery? ›

Restoring involves copying backup files from a secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location. Recovery is the process of applying redo logs to the database to roll it forward.

How to restore database in SQL Server step by step? ›

Procedure. Log in to the computer on which you want to restore the database. Open Microsoft SQL Server Management Studio. In the left navigation bar, right-click on Databases and then click Restore Database.

How do you know if your database backups are restorable? ›

Question 4: How do you know if your database backups are restorable? Issue the RESTORE VERIFYONLY command to validate the backup.
  1. Review the SQL Server error log for backup related entries.
  2. Query the msdb. ...
  3. Review the file system where the backups are issued to validate they exist.
Jan 28, 2012

Which are the two types of previews available in Azure? ›

Each Azure Service and Feature is on Public or Private Preview under specific terms and conditions. In most cases, Private Preview does not offer Support and the Public Preview offers limited support. Sometimes a service/feature might not respect the roadmap and additional work is required.

What is the difference between private preview and public preview in Azure? ›

Private preview – During this phase we invite a few customers to take part in early access to new concepts and features. This phase does not include formal support. Public preview – During this phase we allow any customer with the proper Azure AD license to evaluate the new feature.

How do I preview data in Azure? ›

How to enable/disable preview experience
  1. After opening Settings, you will see an option to turn on Azure Data Factory Studio preview update.
  2. Toggle the button so that it shows On and click Apply.
  3. Your data factory will refresh to show the preview features. Similarly, you can disable preview features with the same steps.
Apr 14, 2023

What is preview feature in Azure? ›

Azure offers preview features to you for evaluation purposes. A preview may include preview, beta, or other pre-release features, services, software, or regions.

Why is preview necessary? ›

Not only do previews mentally prepare us to learn, but they also activate our existing background knowledge and make it easier to create new neural connections. We all have background knowledge on various topics, but students don't always know how to apply what they know to what they are learning.

Why do we use preview? ›

A preview gives you a look at something that hasn't been released yet.

How do I get Preview options? ›

Right-click on the Windows Start button in the bottom-left corner. Open File Explorer. Go to the View tab. Select the Preview pane option in the ribbon.
Here are the steps you need to take if you are running Windows 11 on your device:
  1. From the taskbar, open File Explorer.
  2. Click the View tab.
  3. Go to the Show menu.
Nov 24, 2022

How do I disable Preview features in Azure? ›

Choose Account manager for your account and then choose Preview features. To enable or disable a feature, choose the slider.

What is Preview mode? ›

Preview mode allows you to validate that a variant renders as intended before you start an experiment. You can also preview a running experiment. We recommend using preview mode on Chrome. Safari users must go to Preferences > Privacy and then clear Prevent cross-site tracking to use preview mode.

Which is the fastest storage in Azure? ›

Azure ultra disks are the highest-performing storage option for Azure virtual machines (VMs). You can change the performance parameters of an ultra disk without having to restart your VMs. Ultra disks are suited for data-intensive workloads such as SAP HANA, top-tier databases, and transaction-heavy workloads.

What are the different types of backups in Azure? ›

Store backups in locally redundant storage (LRS), geo-redundant storage (GRS), and zone-redundant storage (ZRS).

What is full backup in Azure? ›

SQL Server backup types
Backup typeDetails
Full backupA full database backup backs up the entire database. It contains all the data in a specific database or in a set of filegroups or files. A full backup also contains enough logs to recover that data.
2 more rows
Jan 31, 2023

How do I manage Azure SQL backups? ›

In the Azure portal, navigate to your server and then select Backups. Select the Retention policies tab to modify your backup retention settings. On the Retention policies tab, select the database(s) on which you want to set or modify long-term backup retention policies. Unselected databases will not be affected.

What is the maximum database size in Azure SQL? ›

Maximum size of each data file is 8 TB. Use at least two data files for databases larger than 8 TB.

How many days does the Azure SQL Database allow you to go back and recover data? ›

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: From an existing database.

What is the difference between backup and snapshot in Azure? ›

The main distinction between backups and snapshots is that backups are independent, self-contained files that don't require cross-file dependencies to restore a VM, whereas snapshots rely on dependent files for VM restoration.

What is the limitation of disk backup in Azure? ›

From these backups, you can restore full virtual machines and restore guest files and folders. You can back up and restore Azure managed disks that are up to 8 TB.

How do I copy Azure SQL Database to another subscription? ›

To copy a database by using the Azure portal, open the page for your database, and then choose Copy to open the Create SQL Database - Copy database page. Fill in the values for the target server where you want to copy your database to.

How do I restore my Azure SQL data warehouse? ›

Restore an existing dedicated SQL pool (formerly SQL DW) through the Azure portal
  1. Sign in to the Azure portal.
  2. Navigate to the dedicated SQL pool that you want to restore from.
  3. At the top of the Overview page, select Restore.
  4. Select either Automatic Restore Points or User-Defined Restore Points.
Dec 14, 2022

How to restore Azure SQL Database from local backup from SSMS? ›

Open SSMS and connect to your managed instance. In Object Explorer, right-click the Databases folder of your managed instance, and then select Restore Database to open the restore wizard.

How to restore database from backup file in Azure data Studio? ›

Open the Restore database dialog box by selecting Restore on the Tasks widget. Select Backup file in the Restore from box. Select the ellipses (...) in the Backup file path box, and select the latest backup file for TutorialDB. Ensure the SQL Server service has READ permissions on the backup file.

How to recover data from SQL Server database? ›

A. Restore a full database backup
  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. Right-click Databases and select Restore Database...
  3. On the General page, use the Source section to specify the source and location of the backup sets to restore.
Mar 3, 2023

How do I copy Azure SQL database backup to local machine? ›

The Azure portal
  1. To export a database using the Azure portal, open the page for your database and select Export on the toolbar.
  2. Specify the BACPAC filename, select an existing Azure storage account and container for the export, and then provide the appropriate credentials for access to the source database. ...
  3. Select OK.
Dec 20, 2022

How to migrate data from one database to another in Azure SQL? ›

  1. Open the Azure SQL Migration extension for Azure Data Studio.
  2. Connect to your source SQL Server instance.
  3. Click the Migrate to Azure SQL button, in the Azure SQL Migration wizard in Azure Data Studio.
  4. Select databases for assessment, then click on next.
Jan 13, 2023

Can you replicate Azure SQL database? ›

Transactional replication is a feature of Azure SQL Managed Instance and SQL Server that enables you to replicate data from a table in Azure SQL Managed Instance or a SQL Server instance to tables placed on remote databases. This feature allows you to synchronize multiple tables in different databases.

How do I recover data from Azure backup? ›

Sign in to the Azure portal and in the left pane, select Virtual machines. From the list of virtual machines, select the virtual machine to open that virtual machine's dashboard. In the virtual machine's menu, select Backup to open the Backup dashboard. In the Backup dashboard menu, select File Recovery.

What is full backup in SQL Server? ›

A full database backup backs up the whole database. This includes part of the transaction log so that the full database can be recovered after a full database backup is restored. Full database backups represent the database at the time the backup finished.

How do I retrieve data from Azure? ›

Create an export job in your source storage account in Azure portal. Specify source blobs or container paths for the data to be exported. Provide the return address and carrier account number for shipping the drives back to you. Ship the disk drives to the shipping address provided during job creation.


1. Azure SQL Backups - Part 9
(Cloud Academy - All in One)
2. Azure SQL - Automated Backups (Part 1) | Data Exposed
(Microsoft Developer)
3. Azure SQL Database : Manage & Restore Backups - EP02
(Curious Drive)
4. Backup and restore SQL Server to Azure Blob storage
(TSInfo Technologies)
5. Video 28: SQL Database Backup and Restore | Microsoft Azure
(IT Channel)
6. How to restore database in azure
Top Articles
Latest Posts
Article information

Author: Reed Wilderman

Last Updated: 29/06/2023

Views: 5393

Rating: 4.1 / 5 (52 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Reed Wilderman

Birthday: 1992-06-14

Address: 998 Estell Village, Lake Oscarberg, SD 48713-6877

Phone: +21813267449721

Job: Technology Engineer

Hobby: Swimming, Do it yourself, Beekeeping, Lapidary, Cosplaying, Hiking, Graffiti

Introduction: My name is Reed Wilderman, I am a faithful, bright, lucky, adventurous, lively, rich, vast person who loves writing and wants to share my knowledge and understanding with you.