Backup and restore to and from cloud storage

Azure SQL Database is a fully managed relational database service by Microsoft that supports diverse SQL workloads. It provides an automatic backup solution, which helps to guarantee that your data is always safe and available. Azure SQL backups are stored in the Standard Geo-Replication tier of Azure Blob Storage. By default, Azure SQL Database keeps full backups for seven days.

Here is a high-level procedure for backing up your data using Azure SQL:

• In the Azure portal, navigate to the SQL databases page, select your database.
• Click on the Restore button.
• In ‘Restore Scope,’ choose Point In Time.
• Already, the last backup time is selected. Choose the exact time of day of the backup.
• In ‘Restore Configuration,’ select the target server, pricing tier, and provide a new database name.
• Click Apply.

Azure SQL Database also supports export to a BACPAC, a package comprising schema and data. BACPAC can be stored in Azure Blob Storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation.

Restoring from Azure Cloud Storage

Restoring data from the Azure SQL database is also simple and straightforward. Azure keeps your storage account backups for 14 days.

Here is a high-level procedure for restoring your data from Azure SQL:

• In the Azure portal, navigate to the SQL databases page, select your database.
• Click on the Restore button.
• In ‘Restore Scope,’ select Point In Time.
• Select the preferable date and time.
• In ‘Restore Configuration,’ provide a new database name.
• Click Apply.

When you restore a database from a backup, settings such as firewall rules, active geo-replication links, transparent data encryption, and full-text search are not restored along with the database. You must reconfigure these settings after you restore your database.

Comparisons

There are some differences between Azure SQL backups and traditional SQL backups, as per the table below:

Conclusion

Understanding backup and restore operations in Azure SQL Database and Azure SQL Managed Instance is crucial to protect your critical business data. The beauty behind Azure SQL Database is the architecture allows for direct integration with Azure, reducing management overheads and enhancing data security. With a proper understanding of how these processes work, the efficiency and effectiveness of your data management operations are well optimized. Always keep in mind the backup and restore procedures as well as the Azure SQL Database limitations and necessities of your unique scenario to make more informed and specialized decisions for your organization.

Practice Test

True/False: Azure SQL Database supports automatic backups.

• True
• False

Answer: True

Explanation: Azure SQL Database automatically creates database backups and keeps them for a configurable amount of time.

SQL Server Backup and Restore with Microsoft Azure Blob Storage Service is possible.

• True
• False

Answer: True

Explanation: This feature enables you to backup SQL Server databases directly on Azure Blob Storage Service.

Which of the following are used by Azure for automatic backups? (Multiple Select)

• A. Full Database Backups
• B. Differential Database Backups
• C. Transaction Log Backups
• D. Snapshot Backups

Answer: A, B, and C

Explanation: Azure SQL Database uses a combination of full, differential, and transaction log backups to ensure data protection and business continuity.

Azure SQL Database supports Point in Time Restore for up to what period?

• A. 7 Days
• B. 14 Days
• C. 30 Days
• D. 35 Days

Answer: D. 35 Days

Explanation: Azure SQL Database supports Point in Time Restore for up to 35 days.

True/False: Azure SQL Managed Instance does not support transaction log backup.

• True
• False

Answer: False

Explanation: Azure SQL Managed Instance supports automated backups, including full, differential, and transaction log backups.

Restoring from a backup in Azure SQL Database usually results in data loss.

• A. True
• B. False

Answer: B. False

Explanation: Restoring from backup in Azure SQL Database does not lead to data loss as it maintains a replication of your data to enable quick restore.

In Azure, the full database backups are made at what frequency?

• A. Daily
• B. Weekly
• C. Monthly
• D. Yearly

Answer: A. Daily

Explanation: Azure SQL Database creates a full database backup every week, differential database backups every 12 hours, and transaction log backups every 5 minutes.

True/False: The backup stored in Azure Storage is counted towards the total database size.

• True
• False

Answer: False

Explanation: The backups stored in Azure Storage are not counted towards the total database size.

True/False: Microsoft automatically encrypts your Azure SQL Database backups.

• True
• False

Answer: True

Explanation: Azure SQL Database backups are encrypted using Microsoft-managed keys for enhanced data security.

Which temporal data can be restored through Point-in-time restore in Azure SQL?

• A. Deleted data
• B. Data from a specific time
• C. Data from the last backup
• D. All of the above

Answer: B. Data from a specific time

Explanation: Point-in-time restore in Azure SQL allows you to restore data to a specific point in the past, typically before an error occurred.

To restore a database from a backup, you need the backup file and the log chain.

• A. True
• B. False

Answer: A. True

Explanation: This is required to restore the database to a specific point in time.

In Azure, backups are used to create new databases.

• A. True
• B. False

Answer: A. True

Explanation: A full database backup is sufficient to create a new database from that backup.

Azure automatically checks the consistency of backups stored to Azure blob storage.

• A. True
• B. False

Answer: A. True

Explanation: Azure regularly verifies the consistency of Azure blob storage backups, helping to ensure recoverability.

Differential backups capture only the changes made since the last transaction log backup.

• A. True
• B. False

Answer: B. False

Explanation: Differential backups capture changes made since the last full backup, not the last transaction log backup.

Azure Managed Instance supports active geo-replication.

• A. True
• B. False

Answer: B. False

Explanation: Azure Managed Instance doesn’t support active geo-replication. It uses auto-failover groups for disaster recovery purposes.

Interview Questions

What is the primary purpose of backing up data to cloud storage in Microsoft Azure?

The primary purpose of backing up data to cloud storage in Microsoft Azure is to provide a way to recover the data in the event of a data loss scenario such as accidental deletion, hardware failure or disaster recovery.

What Microsoft Azure service facilitates backups and restores on Azure SQL Database?

Azure Backup service facilitates backups and restores on Azure SQL Database.

What is a point-in-time restore in Azure SQL Database?

A point-in-time restore allows you to restore a database to a specific point in time within its retention period. This is useful for recovering from situations like accidental data modification or deletion.

How long does Azure SQL Database retain automatic backups?

For Azure SQL Database, automatic backups are retained for 7 days for databases in Basic tier, and up to 35 days for databases in Standard and Premium tiers.

What types of data can the Microsoft Azure Backup service protect?

Microsoft Azure Backup service can protect SQL Server databases, files and folders, SharePoint Server, Windows clients, Azure Virtual Machines, and other Microsoft workloads.

How to restore a deleted Azure SQL database?

A deleted Azure SQL database can be restored by using the Azure portal, PowerShell, or Azure CLI. You access the list of available backups by selecting the “Deleted databases” option for the specific SQL Server.

Can the Azure Backup service be used to perform a cross-region restore?

Yes, Azure Backup service allows for cross-region restores for the purpose of disaster recovery.

Is it possible to take transaction log backups in Azure SQL Database?

In Azure SQL Database, transaction log backups are automatically taken every 5 – 10 minutes. However, these backups are managed by Azure and are primarily used for point-in-time restores.

What is the Azure SQL Long-Term Backup Retention feature?

The Azure SQL Long-Term Backup Retention feature allows for the storing of Azure SQL backups in an Azure Backup Vault for up to 10 years, providing a means for organizations to meet their long-term data retention requirements.

What are the options to encrypt backups in Azure Backup?

Azure Backup encrypts your data at rest using Azure Storage Service Encryption. For over-the-network encryption, Azure Backup uses Advanced Encryption Standard 256 (AES-256).

What are the implications if the Azure SQL database size exceeds the maximum backup storage limit?

If the database size exceeds the backup storage limit, the backups fail and an alert is raised to increase the maximum backup storage size.

How can you monitor the status of the backup jobs in Azure SQL?

You can monitor the status of backup jobs in Azure SQL by using Azure Monitor Logs, viewing the jobs in the Backup center in Azure portal, or querying the msdb database in SQL Server.

Does Azure Backup service support offline backup?

No, Azure Backup service doesn’t support offline backup. Azure Backup service performs online backups with no downtime.

Can Azure automatically handle the patching and updating of Azure Backup service?

Yes, Azure handles all of the patching and updates for Azure Backup service, reducing the need for manual intervention.

How can Azure SQL database backup and restore operations be automated?

Azure SQL database backup and restore operations can be automated using PowerShell scripts or Azure Automation service. You can schedule the operations at preferred times or set them to occur at periodic intervals.