CODEWITHSUNDEEP
sql-serverssmsazure-backup-vault
Aaron Krytus
Aaron Krytus

Reputation: 21

SQL VM - Azure Recovery Services Vault vs Manual Backup

I am sure this is an easy one, but I have no experts to consult and confirm. I am not a SQL DBA so I was hoping someone from the community could confirm my answer to this question.

Currently we are backing up SQL Server in an Azure VM using Recovery Services Vault. We have 2 policies:

There is also a manual database backup being performed in SSMS. Again I am not a DBA, but it appears this is being triggered via SQL Agent Job. It looks to be running a Maintenance Plan scheduled daily. I am not sure what type of backup is being performed.

Once the manual backup has been performed, it breaks the log chain and produces an error with the 1-hour Azure backup. I assume the manual backup is not necessary when performing the backups using the recovery services vault.

Question:

  1. Would the recommended solution be to eliminate the local SSMS backup that is being performed?
  1. As-is, if I had to restore a database, what would it look like? What issues would I run into?

Thank you for your time and assistance.

Note:

Backup and restore for SQL Server on Azure VMs - SQL Server on Azure VMs | Microsoft Learn

FAQ - Backing up SQL Server databases on Azure VMs - Azure Backup | Microsoft Learn

I have not tried anything at this time. Looking for advice.

Upvotes: 0

Views: 772

Answers (1)

Qiang Li
Qiang Li

Reputation: 1

Q1: Simply delete the backup maintenance plan. Or if you really need the local backup file, change it to copy_only full backup.

Your local backup will keep breaking the backup chain which is now maintained by Azure Recovery Service Vault, so don't do any non-copy_only backup in local.

Q2: The current situation is, when you try to restore a database to a point of time status, you may not be able to do it. But all the full backup should work no matter it was taken by Azure or SQL Agent.

For example your Azure RSV take a full backup at 1:00AM and then log backup once per hour. At 10:00AM your local SQL Agent take a full backup. Then when you try to restore this database, you can restore it to any point of time between 1:00 AM and 10:00AM. But any point in time restore after 10:00AM will not work since the Azure log backup will keep failing until the next full backup happen at the next 1:00AM. You can still use your local full backup file at to restore the database to 10:00AM.

Upvotes: 0

Related Questions