Coming Soon: GET:IT Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET Coming Soon: GET:IT Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET
Microsoft Azure

Azure SQL Backups

In this post, I will explain how Azure automatically protects any databases you create with the Azure SQL service.



Sponsored Content

Say Goodbye to Traditional PC Lifecycle Management

Traditional IT tools, including Microsoft SCCM, Ghost Solution Suite, and KACE, often require considerable custom configurations by T3 technicians (an expensive and often elusive IT resource) to enable management of a hybrid onsite + remote workforce. In many cases, even with the best resources, organizations are finding that these on-premise tools simply cannot support remote endpoints consistently and reliably due to infrastructure limitations.

Database-as-a-Service Benefit

When you run SQL Server in a virtual machine, you are responsible for backups. I have found that people typically underestimate the workload involved here. Azure Backup for IaaS virtual machines will do a very nice job of backing up your virtual machines. But what about your databases? An “SQL backup” does not take place, therefore your transaction logs don’t get truncated. After some time, the volume(s) that contain transaction logs will be filled and SQL Server will grind to a halt. And to be honest, your DBAs probably won’t be happy with VM-only backups.

Note: Azure Backup announced at Microsoft Ignite 2017 that they are working on a service to back up SQL Server in Azure virtual machines with SQL-native full, differential, and transaction log backups.

If you deploy databases using Azure SQL, then Microsoft takes care of all your backups for you. Unless you go outside the norms, there’s nothing for you to do or even enable! Have I caught your interest now?

What Backups Are Done?

When you create your database, Azure will automatically start protection. The first full backup will commence and complete within 30 minutes of database creation. After that you will have:

  • A weekly full backup
  • Differential backups every few hours
  • Transaction log backups every 5-10 minutes

The “prime order” of Azure SQL backups is that impact on the performance of the database should be minimized; this is why the above includes phrases such as “every few hours” or “5-10 minutes”, instead of something more precise. Azure is protecting your workload and other people’s workloads that are running on the underlying service fabric.

Note: Azure SQL runs on Azure Service Fabric, the PaaS service which was later made available to customers that want to develop microservices.

Backup Retention

There are two elements to the retention policy of your backups:

  • Storage allocation versus requirements
  • How long backups will be retained for
  • Encryption
  • Database deletion

You are given a certain amount of storage for free, based on the size of your database; this amount is 200 percent. For example, if you have created a database with 250GB of data storage, then you are also given 500GB of backup storage at no additional cost. If your backups require more than the allocated 200 percent, then there is an additional storage charge.

Note that the storage used for Azure SQL backups is Read-Access Geo-Redundant Storage (RA-GRS). That means there are 3 synchronous copies of the backups in the same region as the database and 3 synchronous copies in the paired region.

Backup retention is based on the tier of the database:

  • Basic tier: 7 days of backup retention
  • Standard and Premium tiers: 35 days of backup retention

If you require more retention, then it is possible to long-term retention (LTR) for up to 10 years using a recovery services vault. This was in preview at the time of writing.

Enabling long-term retention of Azure SQL backups [Image Credit: Aidan Finn]
Enabling Long-Term Retention of Azure SQL Backups [Image Credit: Aidan Finn]

If you are using Transparent Data Encryption (TDE) in your database, then the backups of that database will be encrypted. Any new databases that are created have TDE enabled by default.

Backups are retained for the above retention periods after a database is deleted; this means that an accidental “drop” can be recovered from.


You can restore Azure SQL databases in many circumstances including:

  • Restore to a point in time (thanks to transaction log backups) within the retention period, resulting in a new database on the same server.
  • Restore a deleted database to the same server that it was originally on, either to how it was when it was deleted or any point in time in the retention period.
  • Restore a database to another Azure region to another server, providing you with a form of disaster recovery (with a relatively slow recovery time objective or RTO).
  • Restore a database using an LTR recovery point from an Azure Recovery Service Vault. This allows you to restore from up to 10 years ago, depending on how you configured LTR.
Doing a point-in-time restore of an Azure SQL database [Image Credit: Aidan Finn]
Doing a Point-in-Time Restore of an Azure SQL Database [Image Credit: Aidan Finn]

Related Topics:


Don't have a login but want to join the conversation? Sign up for a Petri Account

Comments (0)

Leave a Reply

Aidan Finn, Microsoft Most Valuable Professional (MVP), has been working in IT since 1996. He has worked as a consultant and administrator for the likes of Innofactor Norway, Amdahl DMR, Fujitsu, Barclays and Hypo Real Estate Bank International where he dealt with large and complex IT infrastructures and MicroWarehouse Ltd. where he worked with Microsoft partners in the small/medium business space.
Live Webinar: Active Directory Security: What Needs Immediate Priority!Live on Tuesday, October 12th at 1 PM ET

Attacks on Active Directory are at an all-time high. Companies that are not taking heed are being punished, both monetarily and with loss of production.

In this webinar, you will learn:

  • How to prioritize vulnerability management
  • What attackers are leveraging to breach organizations
  • Where Active Directory security needs immediate attention
  • Overall strategy to secure your environment and keep it secured

Sponsored by: