Azure SQL Database Backup and Restore Overview (2023)

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

Problem

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".

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

Solution

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’:

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

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

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:

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.

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:

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:

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

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:

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:

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

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:

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:

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):

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

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:

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

So, at 15:13, after restoring the database, we can confirm that tablelanguages 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, 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:

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.

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:

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

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

Next Steps

• Stay tuned for my next articles in this series.
• Also check out this:Store Azure SQL Database backups for up to 10 years.

About the author