Most of DBA are coming from On-premise Database Servers they know very well How we can manage SQL backup using SQL Server native backup or even using SQL Server Backup third party tool but when we moved our database to cloud (Azure SQL) all of us starting to ask How the backup is taken in Azure for SQL Database, what is the RTO and RPO, How I can restore the backup all of this information I will list them in my article of today.

Introduction

Before starting in our main subject of today let us explain what is backup and what is the database backup types in Microsoft SQL Server:

  • Backup: is taken a Copy of data that you can recover at any time and the backup concept should be implemented not in SQL Database only but should be considered in any critical system contain data like VM and disks, even we should have a backup from our development projects and codes.
  • SQL Server Backup types: we have 3 common types we should consider them in any backup solution for SQL Server database (FULL, Differential, Log transaction) and we have another backup type depend on your needs, for more information check Microsoft documentation

Also, you should know that you can take a backup from your On-premise database to azure blob storage for more information check this article to know the full steps for how to implement and manage SQL Server backup on Azure Blob Storage.

Azure SQL types

I will not talk more here but just I need to let you that we have {SQL Server on Azure VM, Azure SQL Database and Azure MI (Managed instance)}

Our subject today Azure SQL Backup on Azure SQL Database and Azure MI because SQL Server on Azure VM it is 100% like your SQL Server On-premise.

Azure SQL Backup Tips

  1. Backup on SQL Azure is taken Automatically.
  2. First Backup is taken after the Provisioning process.
  3. You can’t download the backup of Azure SQL to restore it on SQL Server On-Primes Database.
  4. Azure SQL Backup is stored in geo-replicated blob storage (RA-GRS storage type)
  5. Backups retained for 7/35 days depend on Azure SQL Services tier
  6. Backups are compressed to reduce the size and cost of Azure storage.
  7. Full Backup is taken weekly one backup per week.
  8. Differential backup every 12 hours.
  9. Log transaction backup every 5- 10 Minutes.
  10.  Backup and Restore process in Azure are automatically consider integrity checks by Azure SQL Database engineering team Check this Link
  11. Azure SQL Team Testing the automated backup by doing automatically test restore.
  12. Azure SQL backup considered automatic page repair, same as in SQL Server Mirroring and SQL Server Always On.
  13.  LTR Long-term retention (up to 10 years) of backups.
  14. LTR can be configured in Azure SQL Database and Azure MI.
  15. A full backup in LTR is copied automatically to different storage weekly.
  16. LTR not supported on Hyperscale till this moment.
  17. Manual COPY_ONLY Backup/Restore with Managed Instance only.
  18. You can Restore deleted database based on the backup retention period configured for this database before delete process
  19. If you delete the server all of the database will be deleted and you can’t restore them, but if you are configuring the LTR Long-term retention you can restore the database in case you deleted the Azure SQL Server or Azure SQL MI Managed instance.
  20. The backup price depends on your Azure SQL Purchase model (DTU or vCore).
  21. Backup Storage cost changed by Azure Storage redundancy (LRS, ZRS, RA-GRS).
  22. storage redundancy for backups can be configured only on Azure MI Managed instance, and this configuration you can do it during the provisioning process after that you can’t change it.
  23. The backup of the database is automatically encrypted because TDE is enabled in Azure SQL by default.
  24. Restore from backup meaning create a new Database.
  25. You can deploy a new database from backup.

Resources

Learn Azure SQL

Keep Following

Cloud Tech Website blog survey

IF you found this blog is helpful and sharing useful content please take few second to do rate the website blog from here

Published by Mustafa EL-Masry

I am Microsoft database consultant working as a Database administrator for more than +10 Years I have very good knowledge about Database Migration, Consolidation, Performance Tuning, Automation Using T-SQL, and PowerShell and so many other tasks I do it in multiple customers here in KSA and as of now, I am working in Bank Albilad managing the core banking system that is hosted in SQL Server Database 8 TB. Also, I am Microsoft certified 2008 and 2016 in SQL Server (2x MCTS, 2x MCTIP, MCSA, MCSE) and I am Microsoft Certified Trainer (MCT) also I am azure Certified (AZ-900, AZ-103) also I was awarded by Microsoft Azure Heroes 3 times as (Azure Content hero, Azure Community hero and Azure Mentor) For more information check my page https://mostafaelmasry.com/about-me/

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.