Backup in Azure SQL happens automatically and involves full, differential, and log backups.
- Full backups: These are comprehensive backups of your entire database, including both data and the system objects metadata. Depending on the database size, full backups can consume considerable time and storage space. In Azure SQL, full backups occur weekly.
- Differential backups: These backups are smaller and faster than full backups. They only back up the data that has changed since the last full backup. They play a vital role in maintaining modern recovery solutions without the need to restore every log backup. Differential backups in Azure SQL Database and Azure SQL Managed Instance occur every 12 hours.
- Log backups: They record all the transactional changes made since the last log backup. They are created every 5-10 minutes, allowing point-in-time restore.
Point-in-Time Restore
One advantageous feature in Azure SQL Database and Azure SQL Managed Instance is the point-in-time restore capability. Suppose there’s a need to recover the database from mistakes made (like accidental data deletion). In that case, the point-in-time restore enables recovery to any point in the retained backup period (defaults to 7 days for Azure SQL Database and 35 days for Azure SQL Managed Instance, can extend up to 10 years).
To perform the point-in-time restore using Azure portal, PowerShell, or Azure CLI, you need to provide the target time for the restore as an input.
Long-Term Backup Retention
For business continuity and disaster recovery purposes, Azure SQL Database offers long-term backup retention (LTR) policy. Once you define this policy, the Azure SQL Database automatically moves the weekly full backups to an Azure Backup vault.
The backups are retained based on the number of weekly backups you want to maintain, which you can keep up to 10 years. Each of these weekly backups allows a restore operation to any point within it with a granularity of one hour.
Geo-Backup
Geo-redundant storage (RA-GRS) is a standard feature in Azure SQL Database that provides geographical redundancy to your backups. The RA-GRS replicates your backups in a paired data center located hundreds of miles away from the original location. In the event of a data center outage, you can restore your database from the geo-redundant backup.
While all these are automatically done by Azure, if you have requirements for copying backups to different locations for compliance or business purposes, you can manually export a database to a BACPAC file (a format for SQL Server backups that contain the database schema and data).
Let us explore how to do that using Azure portal and Azure CLI:
- Azure portal: Navigate to your SQL Database -> Settings -> Export -> Provide storage details and credentials -> Click OK.
-
Azure CLI: You can use the az sql db export command to export the database.
az sql db export --name
--resource-group
--server
[--admin-password]
[--admin-user]
[--storage-key]
[--storage-key-type {ServiceSharedAccessKey, StorageAccessKey}]
[--storage-uri]
[--subscription]
By utilizing these inbuilt tools and features provided by Azure SQL, you can ensure your data is protected and available, even during unexpected failures. This not only provides peace of mind but also attests to how Azure SQL Database is a robust solution for database administration tasks.
Practice Test
1) True or False: The Azure SQL Database automatically creates backups constantly.
- True
- False
Answer: True
Explanation: Azure SQL Database and SQL Managed Instance automatically create full backups, differential backups, and transaction log backups in every two hours.
2) Which of the following is not an Azure Backup service feature?
- A) Back up SQL databases to Azure blob storage
- B) Create a database backup throughout the Azure portal
- C) Restore the database to the exact second
- D) Store long term retention backups
Answer: A) Back up SQL databases to Azure blob storage
Explanation: Azure Backup service feature does not include backing up SQL databases to Azure blob storage. It stores backups in a Recovery Services vault with built-in management of recovery points.
3) What are recovery points in Azure?
- A) An Azure service for disaster recovery
- B) All configurations and data at a specific point in time
- C) A backup of your SQL database
- D) A log of all database transactions made
Answer: B) All configurations and data at a specific point in time
Explanation: Recovery points in Azure refers to backups representing the state of all configurations and data at a specific point in time.
4) True or False: It is not possible to restore a deleted database backup in Azure.
- True
- False
Answer: False
Explanation: Deleted Azure SQL databases can be restored within a retention period. The retention period is typically 7-35 days for automated backups.
5) Which of the following tools does not support Azure SQL Database backup and restore operations?
- A) PowerShell
- B) Azure portal
- C) BCP utility
- D) Azure CLI
Answer: C) BCP utility
Explanation: The BCP utility does not support backup and restore operations. It is used for copying data between an instance of Microsoft SQL Server and a data file.
6) True or False: Point-in-time restore in Azure allows you to restore the database to any point within the backup retention period.
- True
- False
Answer: True
Explanation: Point-in-time restore allows you to restore the Azure SQL database to a previous state at any point within the backup retention period.
7) What is the maximum retention period for Long-term retention backups of Azure SQL Database?
- A) 10 years
- B) 7 years
- C) 35 days
- D) 1 year
Answer: A) 10 years
Explanation: The maximum retention period for Long-term retention backups in Azure SQL Database is 10 years.
8) Multiple Select: Which types of backups are automatically created by Azure for SQL databases?
- A) Full backups
- B) Differential backups
- C) Transaction log backups
- D) No backups
Answer: A) Full backups, B) Differential backups, C) Transaction log backups
Explanation: Azure automatically creates Full, Differential, and Transaction log backups for SQL databases.
9) In Azure, the backups are automatically encrypted using __________.
- A) Azure Active Directory
- B) Transport Layer Security (TLS)
- C) Service Managed Keys
- D) Customer’s own Key
Answer: C) Service Managed Keys
Explanation: In Azure, the backups are encrypted using Service Managed Keys and are automatically enabled.
10) True or False: The restored database in Azure will always replace the existing database.
- True
- False
Answer: False
Explanation: The restored database from backup is created as a new database. It does not replace the existing database.
11) Single Select: Where does Azure store its automatic backups?
- A) In a storage account managed by the customer
- B) In a Recovery Services vault
- C) In a database’s blob storage
- D) In the original SQL Server
Answer: B) In a Recovery Services vault
Explanation: Azure stores automatic backups in a Recovery Services vault, not in any customer-managed storage or blob storage.
12) What option needs to be specified for Point-in-time restore?
- A) The exact second
- B) The backup retention period
- C) The service tier
- D) The geographical region
Answer: A) The exact second
Explanation: For Point-in-time restore, the exact second within the backup retention period must be specified.
13) True or False: For Azure SQL Database, the database size affects the backup and restore time.
- True
- False
Answer: True
Explanation: The larger the size of the database, the more time it will take to complete the backup and restore process.
14) What is the first step in the process to back up your Azure SQL database using Azure portal?
- A) Select the desired database
- B) Go to the SQL databases page
- C) Download the SQL Server Management Studio
- D) Set up a Recovery Services vault
Answer: B) Go to the SQL databases page
Explanation: The first step in backing up your Azure SQL database using Azure portal is to navigate to the SQL databases page.
15) True or False: There is only one way to perform a backup in Azure, and that is via the portal.
- True
- False
Answer: False
Explanation: There are multiple ways to perform a backup in Azure. These include using the Azure portal, PowerShell, Azure CLI or REST API.
Interview Questions
What is the primary purpose of performing a database backup?
The primary purpose of performing a database backup is to create copies of data that can be used to restore the original after a data loss event.
Which feature of Azure SQL database allows you to backup your database?
Azure SQL Database uses Automated backups feature to take full, differential, and transaction log backups for Point in Time Restore.
Where are the Azure SQL backups stored?
Azure SQL backups are stored in Read Access Geo-Redundant Storage (RA-GRS) to ensure the backup is still available even if the data center is down.
How long are automatic backups retained in Azure SQL Database?
Basic tier databases retain backups for 7 days, while Standard and Premium tier databases retain backups for 35 days.
Can you adjust the backup retention period for SQL databases in Azure?
Yes, you can configure the backup retention period to be between 7 and 35 days for automated backups, and up to 10 years for long-term retention backups.
What is the Point-in-Time Restore feature in Azure SQL database?
The Point-in-Time Restore feature allows you to restore the database to any specific point within the retained period.
What is a Full Database backup?
A Full Database backup is a complete copy of the database at the time the backup finished.
Can you download a backup from Azure SQL database?
No, backups cannot be directly downloaded from Azure SQL Database. Instead, you can export a .bacpac file to Azure storage, which can be downloaded.
How often are the transaction log backups taken?
Transaction logs are backed up every 5-10 minutes.
What happens if there’s a failure to backup the Azure SQL database?
If the backup fails, Azure will automatically retry the backup operation.
Is taking a backup of an Azure SQL database disruptive to the database services?
No, the backup process is an online operation that does not disturb the database services.
How can you manually backup an Azure SQL database?
Manually backing up an Azure SQL database can be performed by exporting a BACPAC of the database.
Besides Automated Backup, what other backup strategy does Azure offer?
Besides Automated Backup, Azure offers Long-Term Retention (LTR) that allows you to keep your backups for up to 10 years.
Where can you restore an Azure SQL database backup?
You can restore an Azure SQL database backup either to a new database on the same server or to a database on a different server.
What is a differential backup in Azure SQL database?
A differential backup captures only the changes made to the database since the last full backup, reducing the storage needed for backups.