0

The Managed Instance allows the admin to ENABLE Compress backups at the instance level.

  1. Does that affect the managed backups?
  2. How does the Managed backups work? as i don't see any record in backupset table.
4
  • I would have assumed it being either a "real" backup or a snapshot backup. Both of those are visible in the backup history on an on-prem SQL Server. Did you check the errorlog files for any clues? Oct 4, 2019 at 12:44
  • @TiborKaraszi It looks like a backup, but the history is not stored in msdb. I can only catch them in progress since my largest database takes ~4 hours to take a full backup. Oct 4, 2019 at 12:58
  • @TiborKaraszi : The backups we initiate are visible in backupset ,the auto backups from Azure cannot be seen in sql server backup history. Wouldreally like to know how the APIs actually backup the databases. Oct 4, 2019 at 13:00
  • And nothing in the errorlog file? Oct 5, 2019 at 18:32

1 Answer 1

1

I doubt that the "compress" setting affects the managed backups either way. But there is no way to check as you don't have direct access to it. I have caught the system taking backups before, the commands show using sp_whoisactive, but can't say that I've ever looked at the full command string before. I will try and catch one of mine at it and update my answer if I can.

As for how managed backups work. The management system periodically takes full and log backups according to it's own schedule. There are some events that seem to spark a full backup to be taken that we have observed. Creating/Restoring a database, failing between failover group nodes and when the service restarts (probably Azure balancing it's internal workload).

These backups are stored in a hidden storage account and can only be accessed through powershell. Cross-subscription restores are not possible through powershell using managed backups.

You can additionally take COPY_ONLY backups to URL and then restore from there. But the backups can only be restored onto another managed instance (so no restore to on-prem of any version). However, if you have enabled TDE with Azure managed key rotation, this option is locked out for you. Enabling TDE is the default for databases created through CREATE DATABASE, although databases restored (as in restored from on-prem) carry their TDE setting with them.

1
  • We have disabled TDE for ad-hoc backup/restores for the dev team.Recntly there was a backup failing at 60% every time, enabled compress and it went through. Still trying to figure out.I suppose the backups to be pre-compressed even if the compress is not enabled. Thanks for the points. Oct 4, 2019 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.