Troubleshoot SQL Server database backup by using Azure Backup

This article provides troubleshooting information for SQL Server databases running on Azure virtual machines.

For more information about the backup process and limitations, see About SQL Server backup in Azure VMs.

SQL Server permissions

To configure protection for a SQL Server database on a virtual machine, you must install the AzureBackupWindowsWorkload extension on that virtual machine. If you get the error UserErrorSQLNoSysadminMembership, it means your SQL Server instance doesn't have the required backup permissions. To fix this error, follow the steps in Set VM permissions.

Troubleshoot discover and configure issues

When the creation and configuration of a Recovery Services vault is complete, discovering databases and configuring backup is a two-step process.

During the backup configuration, if the SQL VM and its instances aren't visible in the Discovery DBs in VMs and Configure Backup (refer to above image) ensure that:

Step 1: Discovery DBs in VMs

• If the VM isn't listed in the discovered VM list and also not registered for SQL backup in another vault, then follow the Discovery SQL Server backup steps.

Step 2: Configure Backup

• If the vault in which the SQL VM is registered in the same vault used to protect the databases, then follow the Configure Backup steps.

If the SQL VM needs to be registered in the new vault, then it must be unregistered from the old vault. Unregistration of a SQL VM from the vault requires all the protected data sources to be stop protected and then you can delete the backed-up data. Deleting backed up data is a destructive operation. After you've reviewed and taken all the precautions to unregister the SQL VM, then register this same VM with a new vault and retry the backup operation.

Troubleshoot Backup and Recovery issues

At times, either random failures may happen in backup and restore operations or those operations might get stuck. This may be because of antivirus programs on your VM. As a best practice, we suggest the following steps:

1. Exclude the following folders from antivirus scanning:

   C:\Program Files\Azure Workload Backup C:\WindowsAzure\Logs\Plugins\Microsoft.Azure.RecoveryServices.WorkloadBackup.AzureBackupWindowsWorkload

   Replace C:\ with the letter of your SystemDrive.

2. Exclude the following three processes running within a VM from antivirus scanning:

   • IaasWLPluginSvc.exe
   • IaaSWorkloadCoordinatorService.exe
   • TriggerExtensionJob.exe

3. SQL also offers some guidelines about to work with antivirus programs. See this article for details.

Faulty instance in a VM with multiple SQL Server instances

You can restore to a SQL VM only if all the SQL instances running within the VM are reported healthy. If one or more instances are "faulty", the VM won't appear as a restore target. So this could be a possible reason why a multi-instance VM may not appear in the "server" dropdown during the restore operation.

You can validate the "Backup Readiness" of all the SQL instances in the VM, under Configure backup:

If you'd like to trigger a restore on the healthy SQL instances, do the following steps:

1. Sign in to the SQL VM and go to C:\Program Files\Azure Workload Backup\bin.

2. Create a JSON file named ExtensionSettingsOverrides.json (if it's not already present). If this file is already present on the VM, continue using it.

3. Add the following content in the JSON file and save the file:

   {
                 "<ExistingKey1>":"<ExistingValue1>",
                   …………………………………………………… ,
             "whitelistedInstancesForInquiry": "FaultyInstance_1,FaultyInstance_2"
           }
   
           Sample content:        
           { 
             "whitelistedInstancesForInquiry": "CRPPA,CRPPB "
           }
   
   

4. Trigger the Rediscover DBs operation on the impacted server from the Azure portal (the same place where backup readiness can be seen). The VM will start appearing as target for restore operations.

   

5. Remove the whitelistedInstancesForInquiry entry from the ExtensionSettingsOverrides.json file once the restore operation is complete.

Error messages

Backup type unsupported

Severity	Description	Possible causes	Recommended action
Warning	Current settings for this database don't support certain backup types present in the associated policy.	Only a full database backup operation can be performed on the master database. Differential backup and transaction log backup aren't possible. Any database in the simple recovery model doesn't allow for the backup of transaction logs.	Modify the database settings so all the backup types in the policy are supported. Or change the current policy to include only the supported backup types. Otherwise, the unsupported backup types will be skipped during scheduled backup or the backup job will fail for on-demand backup.

UserErrorSQLPODoesNotSupportBackupType

Error message	Possible causes	Recommended action
This SQL database does not support the requested backup type.	Occurs when the database recovery model doesn't allow the requested backup type. The error can happen in the following situations: A database that's using a simple recovery model doesn't allow log backup. Differential and log backups aren't allowed for a master database. For more detail, see the SQL Server recovery models documentation.	If the log backup fails for the database in the simple recovery model, try one of these options: If the database is in simple recovery mode, disable log backups. Use the SQL Server documentation to change the database recovery model to full or bulk logged. If you don't want to change the recovery model, and you have a standard policy to back up multiple databases that can't be changed, ignore the error. Your full and differential backups will work per schedule. The log backups will be skipped, which is expected in this case. If it's a master database and you've configured differential or log backup, use either of the following steps: Use the portal to change the backup policy schedule for the master database, to full. If you have a standard policy to back up multiple databases that can't be changed, ignore the error. Your full backup will work per schedule. Differential or log backups won't happen, which is expected in this case.

OperationCancelledBecauseConflictingOperationRunningUserError

Error message	Possible causes	Recommended action
Operation canceled as a conflicting operation was already running on the same database.	You may get this error when the triggered on-demand, or the scheduled backup job has conflicts with an already running backup operation triggered by Azure Backup extension on the same database. The following are the scenarios when this error code might display: Full backup is running on the database and another Full backup is triggered. Diff backup is running on the database and another Diff backup is triggered. Log backup is running on the database and another Log backup is triggered.	After the conflicting operation fails, restart the operation.

UserErrorFileManipulationIsNotAllowedDuringBackup

Error message	Possible causes	Recommended actions
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.	The following are the cases where this error code might surface: Adding or dropping files to a database while a backup is happening. Shrinking files while database backups are happening. A database backup by another backup product configured for the database is in progress and a backup job is triggered by Azure Backup extension.	Disable the other backup product to resolve the issue.

UserErrorSQLPODoesNotExist

Error message	Possible causes	Recommended actions
SQL database does not exist.	The database was either deleted or renamed.	Check if the database was accidentally deleted or renamed. If the database was accidentally deleted, to continue backups, restore the database to the original location. If you deleted the database and don't need future backups, then in the Recovery Services vault, select Stop backup with Retain Backup Data or Delete Backup Data. For more information, see Manage and monitor backed-up SQL Server databases.

UserErrorSQLLSNValidationFailure

Error message	Possible causes	Recommended actions
Log chain is broken.	The database or the VM is backed up through another backup solution, which truncates the log chain.	Check if another backup solution or script is in use. If so, stop the other backup solution. If the backup was an on-demand log backup, trigger a full backup to start a new log chain. For scheduled log backups, no action is needed because the Azure Backup service will automatically trigger a full backup to fix this issue.

UserErrorOpeningSQLConnection

Error message	Possible causes	Recommended actions
Azure Backup is not able to connect to the SQL instance.	Azure Backup can't connect to the SQL Server instance.	Use the additional details on the Azure portal error menu to narrow down the root causes. Refer to SQL backup troubleshooting to fix the error. If the default SQL settings don't allow remote connections, change the settings. See the following articles for information about changing the settings: MSSQLSERVER_-1 MSSQLSERVER_2 MSSQLSERVER_53 If there are login issues, use these links to fix them: MSSQLSERVER_18456 MSSQLSERVER_18452

UserErrorParentFullBackupMissing

Error message	Possible causes	Recommended actions
First full backup is missing for this data source.	Full backup is missing for the database. Log and differential backups are parents to a full backup, so be sure to take full backups before triggering differential or log backups.	Trigger an on-demand full backup.

UserErrorBackupFailedAsTransactionLogIsFull

Error message	Possible causes	Recommended actions
Cannot take backup as transaction log for the data source is full.	The database transactional log space is full.	To fix this issue, refer to the SQL Server documentation.

UserErrorCannotRestoreExistingDBWithoutForceOverwrite

Error message	Possible causes	Recommended actions
Database with same name already exists at the target location	The target restore destination already has a database with the same name.	Change the target database name. Or, use the force overwrite option on the restore page.

UserErrorRestoreFailedDatabaseCannotBeOfflined

Error message	Possible causes	Recommended actions
Restore failed as the database could not be brought offline.	While you're doing a restore, the target database needs to be brought offline. Azure Backup can't bring this data offline.	Use the additional details on the Azure portal error menu to narrow down the root causes. For more information, see the SQL Server documentation.

WlExtGenericIOFaultUserError

Error Message	Possible causes	Recommended Actions
An input/output error occurred during the operation. Please check for the common IO errors on the virtual machine.	Access permissions or space constraints on the target.	Check for the common IO errors on the virtual machine. Ensure that the target drive / network share on the machine: has read/write permission for the account NT AUTHORITY\SYSTEM on the machine. has enough space for the operation to complete successfully. For more information, see Restore as files.

UserErrorCannotFindServerCertificateWithThumbprint

Error message	Possible causes	Recommended actions
Cannot find the server certificate with thumbprint on the target.	The master database on the destination instance doesn't have a valid encryption thumbprint.	Import the valid certificate thumbprint used on the source instance, to the target instance.

UserErrorRestoreNotPossibleBecauseLogBackupContainsBulkLoggedChanges

Error message	Possible causes	Recommended actions
The log backup used for recovery contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time according to the SQL guidelines.	When a database is in bulk-logged recovery mode, the data between a bulk-logged transaction and the next log transaction can't be recovered.	Choose a different point in time for recovery. Learn more.

FabricSvcBackupPreferenceCheckFailedUserError

Error message	Possible causes	Recommended actions
Backup preference for SQL Always On Availability Group cannot be met as some nodes of the Availability Group are not registered.	Nodes required to perform backups aren't registered or are unreachable.	Ensure that all the nodes required to perform backups of this database are registered and healthy, and then retry the operation. Change the backup preference for the SQL Server Always On availability group.

VMNotInRunningStateUserError

Error message	Possible causes	Recommended actions
SQL server VM is either shutdown and not accessible to Azure Backup service.	The VM is shut down.	Ensure that the SQL Server instance is running.

GuestAgentStatusUnavailableUserError

Error message	Possible causes	Recommended actions
Azure Backup service uses Azure VM guest agent for doing backup but guest agent is not available on the target server.	The guest agent isn't enabled or is unhealthy.	Install the VM guest agent manually.

AutoProtectionCancelledOrNotValid

Error message	Possible causes	Recommended actions
Auto-protection Intent was either removed or is no more valid.	When you enable auto-protection on a SQL Server instance, Configure Backup jobs run for all the databases in that instance. If you disable auto-protection while the jobs are running, then the In-Progress jobs are canceled with this error code.	Enable auto-protection once again to help protect all the remaining databases.

CloudDosAbsoluteLimitReached

Error message	Possible causes	Recommended actions
Operation is blocked as you have reached the limit on number of operations permitted in 24 hours.	When you've reached the maximum permissible limit for an operation in a span of 24 hours, this error appears. For example: If you've hit the limit for the number of configure backup jobs that can be triggered per day, and you try to configure backup on a new item, you'll see this error.	Typically, retrying the operation after 24 hours resolves this issue. However, if the issue persists, you can contact Microsoft support for help.

CloudDosAbsoluteLimitReachedWithRetry

Error message	Possible causes	Recommended actions
Operation is blocked as the vault has reached its maximum limit for such operations permitted in a span of 24 hours.	When you've reached the maximum permissible limit for an operation in a span of 24 hours, this error appears. This error usually appears when there are at-scale operations such as modify policy or auto-protection. Unlike the case of CloudDosAbsoluteLimitReached, there isn't much you can do to resolve this state. In fact, Azure Backup service will retry the operations internally for all the items in question. For example: If you have a large number of datasources protected with a policy and you try to modify that policy, it will trigger configure protection jobs for each of the protected items and sometimes may hit the maximum limit permissible for such operations per day.	Azure Backup service will automatically retry this operation after 24 hours.

WorkloadExtensionNotReachable

Error message	Possible causes	Recommended actions
AzureBackup workload extension operation failed.	The VM is shut down, or the VM can't contact the Azure Backup service because of internet connectivity issues.	Ensure the VM is up and running and has internet connectivity. Re-register extension on the SQL Server VM.

UserErrorVMInternetConnectivityIssue

Error message	Possible causes	Recommended actions
The VM is not able to contact Azure Backup service due to internet connectivity issues.	The VM needs outbound connectivity to Azure Backup Service, Azure Storage, or Microsoft Entra services.	If you use NSG to restrict connectivity, then you should use the AzureBackup service tag to allows outbound access to Azure Backup Service, and similarly for the Microsoft Entra ID (AzureActiveDirectory) and Azure Storage(Storage) services. Follow these steps to grant access. Ensure DNS is resolving Azure endpoints. Check if the VM is behind a load balancer blocking internet access. By assigning public IP to the VMs, discovery will work. Verify there's no firewall/antivirus/proxy that are blocking calls to the above three target services.

UserErrorOperationNotAllowedDatabaseMirroringEnabled

Error message	Possible cause	Recommended action
Backup of databases participating in a database mirroring session is not supported by AzureWorkloadBackup.	When you enable the mirroring operation on a SQL database, this error appears. Currently, Azure Backup doesn't support databases with this feature enabled.	You can remove the database mirroring session of the database for the operation to complete successfully. Alternatively, if the database is already protected, do Stop backup operation on the database.

UserErrorWindowsWLExtFailedToStartPluginService

Error message	Possible cause	Recommendation
Operation failing with UserErrorWindowsWLExtFailedToStartPluginService error.	Azure Backup workload extension is unable to start the workload backup plugin service on the Azure Virtual Machine due to service account misconfiguration.	Step 1: Verify if NT Service\AzureWLBackupPluginSvc user has Read permissions on: - C:\windows\Microsoft.NET \assembly\GAC_32 - C:\windows\Microsoft.NET \assembly\GAC_64 - C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config. If the permissions are missing, assign Read permissions on these directories. Step 2: Verify if NT Service\AzureWLBackupPluginSvc has the Bypass traverse checking rights by going to Local Security Policy > User Right Assignment > Bypass traverse checking. Everyone must be selected by default. If Everyone and NT Service\AzureWLBackupPluginSvc are missing, add NT Service\AzureWLBackupPluginSvc user, and then try to restart the service or trigger a backup or restore operation for a datasource.

Re-registration failures

Check for one or more of the following symptoms before you trigger the re-register operation:

• All operations (such as backup, restore, and configure backup) are failing on the VM with one of the following error codes: WorkloadExtensionNotReachable, UserErrorWorkloadExtensionNotInstalled, WorkloadExtensionNotPresent, WorkloadExtensionDidntDequeueMsg.

• If the Backup Status area for the backup item is showing Not reachable, rule out all the other causes that might result in the same status:

  • Lack of permission to perform backup-related operations on the VM.
  • Shutdown of the VM, so backups can't take place.
  • Network issues

  

• In the case of an Always On availability group, the backups started failing after you changed the backup preference or after a failover.

These symptoms may arise for one or more of the following reasons:

• An extension was deleted or uninstalled from the portal.
• An extension was uninstalled from Control Panel on the VM under Uninstall or Change a Program.
• The VM was restored back in time through in-place disk restore.
• The VM was shut down for an extended period, so the extension configuration on it expired.
• The VM was deleted, and another VM was created with the same name and in the same resource group as the deleted VM.
• One of the availability group nodes didn't receive the complete backup configuration. This can happen when the availability group is registered to the vault or when a new node is added.

In the preceding scenarios, we recommend that you trigger a re-register operation on the VM. See here for instructions on how to perform this task in PowerShell.

Size limit for files

The total string size of files depends not only on the number of files but also on their names and paths. For each database file, get the logical file name and physical path. You can use this SQL query:

SELECT mf.name AS LogicalName, Physical_Name AS Location FROM sys.master_files mf
               INNER JOIN sys.databases db ON db.database_id = mf.database_id
               WHERE db.name = N'<Database Name>'"

Now arrange them in the following format:

[{"path":"<Location>","logicalName":"<LogicalName>","isDir":false},{"path":"<Location>","logicalName":"<LogicalName>","isDir":false}]}

Here's an example:

[{"path":"F:\\Data\\TestDB12.mdf","logicalName":"TestDB12","isDir":false},{"path":"F:\\Log\\TestDB12_log.ldf","logicalName":"TestDB12_log","isDir":false}]}

If the string size of the content exceeds 20,000 bytes, the database files are stored differently. During recovery, you won't be able to set the target file path for restore. The files will be restored to the default SQL path provided by SQL Server.

Override the default target restore file path

You can override the target restore file path during the restore operation by placing a JSON file that contains the mapping of the database file to the target restore path. Create a database_name.json file and place it in the location C:\Program Files\Azure Workload Backup\bin\plugins\SQL*.

The content of the file should be in this format:

[
  {
    "Path": "<Restore_Path>",
    "LogicalName": "<LogicalName>",
    "IsDir": "false"
  },
  {
    "Path": "<Restore_Path>",
    "LogicalName": "LogicalName",
    "IsDir": "false"
  },  
]

Here's an example:

[
  {
   "Path": "F:\\Data\\testdb2_1546408741449456.mdf",
   "LogicalName": "testdb7",
   "IsDir": "false"
  },
  {
    "Path": "F:\\Log\\testdb2_log_1546408741449456.ldf",
    "LogicalName": "testdb7_log",
    "IsDir": "false"
  },  
]

In the preceding content, you can get the logical name of the database file by using the following SQL query:

SELECT mf.name AS LogicalName FROM sys.master_files mf
                INNER JOIN sys.databases db ON db.database_id = mf.database_id
                WHERE db.name = N'<Database Name>'"

This file should be placed before you trigger the restore operation.

Next steps

For more information about Azure Backup for SQL VMs.