Share via

Azure Managed Instance locate backup history

Gokhan Varol 111 Reputation points
2020-11-22T04:56:39.6+00:00

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

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ricardo Piekarski 6 Reputation points
    2020-11-22T14:49:12.073+00:00

    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
    2021-01-23T10:24:46.247+00:00
    0 comments
  3. SunilTallapaneni-5739 1 Reputation point
    2021-05-19T18:58:05.64+00:00

    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