Questions Regarding Azure Arc-Enabled SQL Managed Instance Point-in-Time Restore

Question

Hello Microsoft Team,

I have a few questions regarding Azure Arc-enabled SQL Managed Instance (SQL MI) and its point-in-time restore feature:

Retention Period Update: How can we update the retention period to be greater than 35 days? Is there a solution to move backups to Azure Blob Storage for Long-Term Retention (LTR)?

Backup Schedule Customization: How can we change the default backup schedule? Currently, it performs full backups weekly, differential backups every 12 hours, and transaction log backups every 5 minutes. Is there a way to modify this schedule?

Backup Schedule Applicability: Is this backup schedule applied universally across all databases? Can we customize the schedule for specific databases?

Thank you for your assistance.

https://learn.microsoft.com/en-us/azure/azure-arc/data/point-in-time-restore#automatic-backups

Answer 1

Hii @Demo

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

Azure Arc-enabled SQL Managed Instances keep backups for up to 35 days by default. If you need to keep backups for longer, you can use Long-Term Retention (LTR) policies, but currently, LTR is not available for Azure Arc-enabled SQL MI like it is for regular Azure SQL Managed Instances.

Azure SQL Managed Instances don’t support moving backups to Azure Blob Storage for Long-Term Retention (LTR) like Azure SQL Database does. Instead, you can manually export your backups to Blob Storage for long-term storage or automate the process with Azure Automation or scripts to regularly back up your databases and store them in Blob Storage.

Azure Arc-enabled SQL Managed Instances have a fixed backup schedule: full backups weekly, differential backups every 12 hours, and transaction log backups every 5 minutes. You can't change this schedule directly. If you need a different backup schedule, you'll have to manage backups manually or use custom scripts to set up your preferred backup times.

Example PowerShell Script for Manual Backups: -

PowerShell script to backup a SQL Server database and upload to Azure Blob Storage

Define variables

$serverName = "your_sql_server_name"

$databaseName = "your_database_name"

$backupFilePath = "C:\Backups\your_database_backup.bak"

$storageAccountName = "your_storage_account_name"

$containerName = "your_blob_container_name"

$blobName = "your_database_backup.bak"

$storageAccountKey = "your_storage_account_key"

Load SQL Server module

Import-Module SqlServer

Backup the database

Invoke-Sqlcmd -ServerInstance $serverName -Query "BACKUP DATABASE [$databaseName] TO DISK = N'$backupFilePath' WITH INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10"

Upload the backup file to Azure Blob Storage

$storageContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey

Set-AzStorageBlobContent -File $backupFilePath -Container $containerName -Blob $blobName -Context $storageContext

Optional: Clean up local backup file

Remove-Item $backupFilePath

The default backup schedule for Azure Arc-enabled SQL Managed Instances applies the same settings to all databases, so you can't set different schedules for individual databases. To have different backup schedules, you would need to handle backups manually using tools like SQL Server Agent Jobs (if using SQL Server on a VM) or custom scripts. You can also use Azure Automation to set up and manage backups according to your specific needs.

If you have any further queries, do let us know.

---

If the answer is helpful, please click "Accept Answer" and "Upvote it".