SQL Server on Azure VM Backup minimizing data duplication

Question

A while back, a Microsoft support engineer recommended using SQL on VM backups. The suggestion was to create an SQL DB in an Azure VM job and an Azure VM job, excluding the disks that host the SQL files, to avoid data duplication.

If the VM is deleted and I need to restore it from backups, SQL disks will not be included in the restore. When I run the restore of the DBs, I’m guessing the job expects the disks to exist and will fail to restore them. I know we can run both backups and include the disks on the VM, but we are trying to save on storage costs and avoid data duplication. Excluding file type from the VM backup job would be ideal, but I haven’t found anything indicating if this is possible. I want to do an SQL Database in Azure VM type of backup where I can have log backups and ensure I have a good RPO and back up the VM, minimizing data duplication. How do we handle this scenario?

Answer 1

Hello Josue Del Valle,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Regarding your explanations, scenarios and question: There are best practices for SQL Server backups on Azure VMs that you can follow.

1. Separate the VM backups and SQL Server backups. This will give you granular control over your backups and ensures you can meet your Recovery Point Objective (RPO) and also excluding the disks that host the SQL Server data and log files to avoid data duplication.
2. If you're using Azure Backup for the VMs, exclude the disks that contain the SQL Server data and log files is possible and it can be done during the backup configuration process and also, to make sure your database backups are stored separately from the VM backups. you can configure SQL Server to back up databases to a separate storage account or a different disk attached to the VM.
3. With the above, you can restore the VM from the Azure Backup without the SQL Server disks and you can restore the SQL Server databases from the backups stored in the separate storage account or disk after VM restoration.
4. By separating the SQL Server backups from the VM backups, you can optimize storage costs. Use Azure Blob Storage for SQL Server backups, which can be more cost-effective than using premium disks.
5. Use Azure Automation or SQL Server Agent jobs to automate the backup process for SQL Server databases because, this will ensure regular backups and reduces the risk of human error and if you use Azure Monitor and SQL Server Management Studio (SSMS) for monitoring, it will give robust information for both VM and SQL Server backups if they are running successfully.

Read more from these links:

• https://techcommunity.microsoft.com/t5/azure-sql-blog/azure-backup-for-sql-server-in-azure-vm-tips-and-tricks-from-the/ba-p/4190264
• https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/backup-restore?view=azuresql
• https://www.mssqltips.com/sqlservertip/6956/microsoft-sql-server-virtual-machine-best-practices

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.