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.
  • 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? – Tibor Karaszi Oct 4 '19 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. – Jonathan Fite Oct 4 '19 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. – Ramakant Dadhichi Oct 4 '19 at 13:00
  • And nothing in the errorlog file? – Tibor Karaszi Oct 5 '19 at 18:32
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.

| improve this answer | |
  • 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. – Ramakant Dadhichi Oct 4 '19 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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