Manage and monitor backed up SQL Server databases
This article describes common tasks for managing and monitoring SQL Server databases that are running on an Azure virtual machine (VM) and that are backed up to an Azure Backup Recovery Services vault by the Azure Backup service. You'll learn how to monitor jobs and alerts, stop and resume database protection, run backup jobs, and unregister a VM from backups.
If you haven't yet configured backups for your SQL Server databases, see Back up SQL Server databases on Azure VMs
Monitor backup jobs in the portal
Azure Backup shows all scheduled and on-demand operations under Backup jobs in the portal, except the scheduled log backups since they can be very frequent. The jobs you see in this portal include database discovery and registration, configure backup, and backup and restore operations.
View backup alerts
Because log backups occur every 15 minutes, monitoring backup jobs can be tedious. Azure Backup eases monitoring by sending email alerts. Email alerts are:
- Triggered for all backup failures.
- Consolidated at the database level by error code.
- Sent only for a database's first backup failure.
To monitor database backup alerts:
Sign in to the Azure portal.
On the vault dashboard, select Backup Alerts.
Stop protection for a SQL Server database
You can stop backing up a SQL Server database in a couple of ways:
- Stop all future backup jobs, and delete all recovery points.
- Stop all future backup jobs, and leave the recovery points intact.
If you choose to leave recovery points, keep these details in mind:
- All recovery points will remain intact forever, and all pruning will stop at stop protection with retain data.
- You'll be charged for the protected instance and the consumed storage. For more information, see Azure Backup pricing.
- If you delete a data source without stopping backups, new backups will fail. Old recovery points will expire according to the policy, but the most recent recovery point will always be kept until you stop the backups and delete the data.
To stop protection for a database:
On the vault dashboard, select Backup Items.
Under Backup Management Type, select SQL in Azure VM.
Select the database for which you want to stop protection.
On the database menu, select Stop backup.
On the Stop Backup menu, select whether to retain or delete data. If you want, provide a reason and comment.
Select Stop backup.
For more information about the delete data option, see the FAQ below:
Resume protection for a SQL database
When you stop protection for the SQL database, if you select the Retain Backup Data option, you can later resume protection. If you don't retain the backup data, you can't resume protection.
To resume protection for a SQL database:
Open the backup item and select Resume backup.
On the Backup policy menu, select a policy, and then select Save.
Run an on-demand backup
You can run different types of on-demand backups:
- Full backup
- Copy-only full backup
- Differential backup
- Log backup
While you need to specify the retention duration for Copy-only full backup, the retention range for on-demand full backup will automatically be set to 45 days from current time.
For more information, see SQL Server backup types.
Modify policy to change backup frequency or retention range.
Any change in the retention period will be applied retrospectively to all the older recovery points besides the new ones.
In the vault dashboard, go to Manage > Backup Policies and choose the policy you want to edit.
Policy modification will impact all the associated Backup Items and trigger corresponding configure protection jobs.
Sometimes, a modify policy operation can lead to an inconsistent policy version for some backup items. This happens when the corresponding configure protection job fails for the backup item after a modify policy operation is triggered. It appears as follows in the backup item view:
You can fix the policy version for all the impacted items in one click:
Unregister a SQL Server instance
Unregister a SQL Server instance after you disable protection but before you delete the vault:
On the vault dashboard, under Manage, select Backup Infrastructure.
Under Management Servers, select Protected Servers.
In Protected Servers, select the server to unregister. To delete the vault, you must unregister all servers.
Right-click the protected server, and select Unregister.
Re-register extension on the SQL Server VM
Sometimes, the workload extension on the VM may become impacted for one reason or another. In such cases, all the operations triggered on the VM will begin to fail. You may then need to re-register the extension on the VM. The Re-register operation reinstalls the workload backup extension on the VM for operations to continue. You can find this option under Backup Infrastructure in the Recovery Services vault.
Use this option with caution. When triggered on a VM with an already healthy extension, this operation will cause the extension to get restarted. This may cause all the in-progress jobs to fail. Check for one or more of the symptoms before triggering the re-register operation.
For more information, see Troubleshoot backups on a SQL Server database.