Azure SQL Database backup monitoring

Question

I have Azure SQL Database with configured LTR weekly backup that can be found in Azure portal.

Our client asked for a report of failed backups related to this SQL Database.

Questions:

• Is there any place where I can found list of failed backups? I already checked all auditing logs from SQL sever and cannot find there any entry related to successful or unsuccessful backups.
• Why I cannot see reports from this backups in Azure Backup center? I can select there only Datasource type = "SQL in Azure VM", but not Azure SQL Database as PaaS.
• Since this Azure SQL Database is PaaS, is it true that it's Microsoft responsibility to ensure that configured backup is done correctly?

Answer 1

Hi @Łukasz Zbińkowski (UZ) ,

Welcome to Microsoft Q&A forum and thanks for using Azure services.

As I understand from the question, you want to know about Azure SQL Database monitoring having configured LTR weekly.

In Azure SQL Database, the first full backup is scheduled immediately after a new database is created or restored. This backup usually finishes within 30 minutes, but it can take longer when the database is large. For example, the initial backup can take longer on a restored database or a database copy, which would typically be larger than a new database.

After the first full backup, all further backups are scheduled and managed automatically. The exact timing of all database backups is determined by the SQL Database service as it balances the overall system workload. You can't change the schedule of backup jobs or disable them.

All backups are shown for the in the SQL Server as below:

Use following T-SQL command to query sys.dm_database_backups and view list of all active backups for this database:

Select * from sys.dm_database_backups 
ORDER BY backup_finish_date DESC

References: Azure SQL DB Backup History - View backups of your Azure SQL Database

Long-term backup retention (LTR) leverages the full database backups that are automatically created to enable point in time restore (PITR). If an LTR policy is configured, these backups are copied to different blobs for long-term storage. The copy is a background job that has no performance impact on the database workload. The LTR policy for each database in SQL Database can also specify how frequently the LTR backups are created.

Note:

The timing of individual LTR backups is controlled by Azure. You cannot manually create an LTR backup or control the timing of the backup creation. After configuring an LTR policy, it may take up to 7 days before the first LTR backup will show up on the list of available backups.

Refer: Long-term retention - Azure SQL Database

If you want to see Backups in Azure Backups Center, you may share you idea in Azure SQL Database Feedback Channel to get this feature added. Concerned team will work on the feedbacks depending on the importance and up-votes.

Hope this helps. Let us know if further queries. Thank you.

Answer 2

Hello @Łukasz Zbińkowski (UZ)

Thank you for posting in Q & A forum.

By default, Azure SQL Database & Azure SQL Managed Instance stores data in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases/instance to a different region in the event of a regional outage/disaster.

Monitoring Backup History for Azure SQL Database & Azure SQL Managed Instance

---

If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.