question

GokhanVarol-4103 avatar image
1 Vote"
GokhanVarol-4103 asked SunilTallapaneni-5739 answered

Azure Managed Instance locate backup history

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @GokhanVarol-4103

I believe you were looking for something like https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/managed-backup-functions-transact-sql?view=sql-server-ver15

Let me know if you were looking for something more specific in your case.

Cheers,
Luiz Thome

0 Votes 0 ·
ricardopiekarski avatar image
0 Votes"
ricardopiekarski answered GokhanVarol-4103 commented

Hello @GokhanVarol-4103 ,

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][1]

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

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

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][4]. 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][5]

[1]: https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?tabs=single-database
[2]: https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?tabs=single-database#monitor-consumption
[3]: https://docs.microsoft.com/en-us/azure/azure-sql/database/features-comparison
[4]: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server#backup
[5]: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

Cheers and best regards,
Ricardo T. Piekarski
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The question is not how to backup databases in Azure Managed Instance. The question is I want to query the backup history (full backup or diff backup or log backup).
During backup the performance degrades dramatically, we may need to regularly adjust backup start times with Azure team etc
To do that first I need to know the backup durations, sizes, compressed sizes , backup start/end time etc.
In a local sql server installations these are exposed in msdb.dbo.backupset tables, I was not able to find where these are stored in Azure Managed Instance.

1 Vote 1 ·

I don't use Managed Instance, so I don't know the answer. But I am a little curious how you conclude that the performance degradation you see is related to the backup?

1 Vote 1 ·

@GokhanVarol-4103 Backup operations do consume some system resources but should not cause any severe performance degradation.
When you are experiencing performance issues, could you please run the below and check if there are any backup operations going on?

 SELECT r.command, query = a.text, start_time, percent_complete,
       eta = dateadd(second,estimated_completion_time/1000, getdate())
 FROM sys.dm_exec_requests r
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
  WHERE r.command IN ('BACKUP DATABASE','BACKUP LOG')
0 Votes 0 ·

I got the same response from MS support. Backups are actually affecting system performance.
zNq8c3xjT7


The question remains why msdb.dbo.backupset does not store the auto backup history or where I can query this data. We will need to work around the automatic full backups since it's drastically reducing our production performance (processes are taking 6x+ longer during backups are running)


0 Votes 0 ·
Show more comments
MladenAndzic-6168 avatar image
0 Votes"
MladenAndzic-6168 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SunilTallapaneni-5739 avatar image
0 Votes"
SunilTallapaneni-5739 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.