Azure Managed Instance locate backup history

Gokhan Varol 111 Reputation points

I am trying to figure out backup history information (full/diff/incr) in Azure Managed Instance
Regular manual backup history is stored in SELECT * FROM msdb.dbo.backupset* tables (Or also in local sql server installations.
Is there a ways to find the backup history (when a database backup was taken for a database, start / end times, duration of the backup data size / compressed data size etc.
How can we gather this information (hopefully from the sqh instance itself)

Azure SQL Database
{count} vote

3 answers

Sort by: Most helpful
  1. Ricardo Piekarski 6 Reputation points

    Hello @Gokhan Varol ,

    Azure SQL Database and Azure SQL Managed Instances cannot be backed up the same way as a general SQL Server database. They both undergo the Automated Backup procedure by Azure. Full details here: automated-backups-overview

    You can review their retention period and monitor usage, which are explained on the full article mentioned, such as at automated-backups-overview.

    You may also see the full features comparison of Azure SQL and SQL Managed Instances on this link: features-comparison

    You will notice that on the full features comparison, it is mentioned that you can perform COPY-ONLY backups on SQL Managed Instances. This is detailed here: transact-sql-tsql-differences-sql-server. In case you are not aware yet, COPY_ONLY is mostly used when you want to refresh a development or acceptance environment and not for retention purposes:

    COPY_ONLY Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.

    Copy-only backups should be used in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted.

    Source: backup-transact-sql

    Cheers and best regards,
    Ricardo T. Piekarski

  2. Mladen Andzic - Msft 6 Reputation points Microsoft Employee
    0 comments No comments

  3. SunilTallapaneni-5739 1 Reputation point

    I tried the extended events option and it is not very efficient
    Are there any other options to capture the backup size; duration of the backup runtime for Azure SQL Managed instances?

    0 comments No comments