Backup transparency in Azure SQL Managed Instance

Applies to: Azure SQL Managed Instance

In this article, learn how to use backup transparency in Azure SQL Managed Instance to view the backup history of your managed instance.

Overview

Backup transparency in SQL Managed Instance is available through the msdb database, which makes backup history tables queryable.

It's important to know about a few key differences between the backup tables in a traditional SQL Server msdb database and the msdb database in SQL Managed Instance. The main differences include the information that's visible, supported tables, and the fields you can use.

Included information

The msdb database in SQL Managed Instance displays the following backup information:

  • The type of automated backup taken, such as full, differential, or log.
  • Metadata about native backups taken manually, although fields like file path and usernames might not be populated. Use the is_copyonly column to determine whether a backup was taken manually or automatically.
  • Metadata about the backup, including status, size, time, and location.
  • The replica where the backup was taken, such as the primary or secondary managed instance. The ability to take backups from the secondary replica currently is available only on the Business Critical service tier.

The msdb database does not have the following information:

  • Backups that are stored for long-term retention. Backups for long-term retention are made by copying files at the storage level. This type of backup isn't visible to the instance.

Supported tables

The msdb database in SQL Managed Instance supports these backup tables:

SQL Managed Instance doesn't use the following backup tables, and the tables aren't populated with data:

Removed fields

Because SQL Managed Instance is a cloud service that stores data in storage, the following fields aren't populated with data:

  • Fields that pertain to the user who is logged in.
  • Fields that pertain to the path of the backup file.
  • Backup expiration information.

Considerations

When you review your backup history in the msdb database, consider the following information:

  • Backup history doesn't contain all information about user-initiated backups, like the username of the user who initiated the backup.
  • Fields that aren't relevant to the cloud aren't populated. Examples include the machine name, the physical drive, and the physical name.
  • Backup information is inserted into the msdb database when the backup is finished. Ongoing backups aren't supported.

Next steps