Why we need SQL Databases backups even in Azure?

When deploying or migrating SQL databases to Azure Cloud, it’s important to understand how backups work, even though Azure automatically provides database backups as soon as you create a SQL Azure database.

Backing up databases in Azure is crucial to protect your data from corruption or accidental deletion. Additionally, these backups allow you to restore your database to a specific point in time, based on the retention period you have chosen.

By default, when you create a SQL Database or a SQL Managed Instance, Azure performs a full backup weekly, differential backups every 12–24 hours, and transaction log backups every 5 to 10 minutes. The frequency of transaction log backups depends on the size of the compute resource and the level of database activity.

Azure offers several options for backup storage redundancy, allowing you to choose between locally-redundant, zone-redundant, or geo-redundant storage blobs for SQL Managed Instances or SQL Databases. These options ensure that multiple copies of your data are maintained. This means that in case of hardware failures, network issues, power outages, or even natural disasters, you can still restore your database.

It’s also important to note that when you create a SQL database or SQL instance, Azure automatically replicates your data to what is known as a paired region. A paired region consists of a set of data centers within a defined latency perimeter, providing a dedicated low-latency network. This setup ensures optimal performance and security for your data.

Advantages of Database Backups:

• Point-in-Time Restore: Restore your database to any point within the retention period.
• Non-Overwriting Restores: When restoring a SQL Database, a new database is created with a different name on the same server, preserving the original database.
• Deletion After Restore: Once the restore is complete, you can delete the original database if needed.
• SQL Managed Instance: For SQL Managed Instances, the restore operation creates a copy of the database on the same or a different managed instance within the same subscription and region.
• Restore Deleted Databases: You can restore a deleted database to any point within the retention period.
• Geo-Restore: Restore a database to another geographic region if you cannot access the primary region. Geo-restore is available only for databases with geo-redundant backup storage.
• Long-Term Backup Restore: Restore from a specific long-term backup if your database has a long-term retention policy (LTR).
  Backup Scheduling
• Initial Full Backup: The first full backup is scheduled soon after a new database is created or restored, usually within 30 minutes, though it may take longer for larger databases.
• Automatic Management: After the first backup, all subsequent backups are scheduled and managed automatically. You cannot alter the backup schedule or disable it.

Restoring a Database:

• Point-in-Time Restoration: To restore a database to a specific point in time, you need a complete backup chain: one full backup, optionally one differential backup, and one or more transaction log backups.
• Automatic Backup Deletion: Backups no longer needed for point-in-time recovery (PITR) are automatically deleted. Backups are purged in weekly sets.
  Monitoring Storage Consumption
• For vCore Databases: Storage used by each type of backup (full, differential, and log) is reported separately on the database monitoring blade. This feature is not available for managed instances.

Managing Backup Storage Consumption:

• No Charge for Up to Maximum Size: Backup storage up to the maximum size of your database is not charged. Excess storage is billed.
• Shorten Retention Period: Reduce the backup retention period to the minimum necessary.
• Avoid Large Writes: Minimize large write operations, such as index rebuilds, to reduce backup storage needs.
• General-Purpose Tier: In this tier, data storage is cheaper than backup storage. If you frequently incur high backup storage costs, consider increasing data storage to save on backup storage.
• Locally-Redundant Storage: Use locally-redundant backup storage when possible.

Backup Retention:

• Default Retention: For new, restored, or copied databases, Azure SQL Database and Azure SQL Managed Instance retain backups for point-in-time recovery (PITR) within the last 7 days by default. This retention can be adjusted from 1 to 35 days.
• Retention for Deleted Databases: When you delete a database, its backups are retained. However, you cannot modify the retention period for deleted databases.
• Server or Managed Instance Deletion: Deleting a server or managed instance also deletes all databases on it, and they cannot be recovered. You cannot restore a deleted server or managed instance.
• Long-Term Retention (LTR): If you have configured LTR for a database or managed instance, long-term backups are preserved and can be used to restore databases on another server or managed instance within the same subscription. LTR backups can be kept for up to 10 years in Azure Blob storage.

Storage Costs:

• Factors Affecting Price: Your purchasing model (DTU or vCore), Chosen backup storage redundancy option, Your region, Backup storage is billed per GB per month.
• DTU Model: No additional charge for backup storage for SQL databases or elastic pools.
• vCore Model: For single databases, a backup storage amount equal to 100% of the maximum data storage size is provided at no extra charge.

Monitoring Costs:

To track backup storage costs, go to:

1. Cost Management + Billing in the Azure portal;
2. Select Cost Management;
3. Then Cost Analysis;
4. Choose your subscription as the scope; and
5. Filter by time period and service.

How to configure an Backup Policy on Azure SQL Databases?

1. Access your SQL Server in the Azure portal;
2. Select Backups;
3. Select Retention Policies;

1. Select the Database you want configure;
2. Select Configure Policies;
3. At Configure screen opened you can configure based on your preferences;

In short, Azure SQL database backups keep your important data safe and recoverable, giving you confidence that you can handle unexpected problems and continue your business without major issues.