Problem description
VMware app aware backups of SQL databases complete successfully with errors - Error occurred while taking SQL snapshot and Application aware backup Failed.
Cause
App aware backups of virtual machines can fail because of the following reasons:
The credentials assigned to the virtual machine hosting SQL databases do not have
sysadminpermissions.The server authentication mode for the SQL instance is not set to the Windows Authentication mode.
The volume that hosts the volume shadow copy storage area has insufficient space for the shadow copies.
VSS errors on the virtual machine.
Traceback
Download Phoenix logs as described in the Logs article.
Error message: [2020-01-06 03:09:28,466] [WARNING] Error occurred while taking SQL snapshot 4295032853 Got error while taking SQL snapshot.
Progress Logs in the UI:
In the Phoenix Management Console, in the menu bar, click Jobs.
In the Jobs page, click the VMware tab.
In the VMware tab, under the Job ID column, click the job ID that corresponds to the app aware job that completed successfully with errors.
In the Job details page, click the Progress Logs tab. The Progress Logs will have the error, as illustrated below.
Resolution
Issue: The credentials assigned to the virtual machine hosting SQL databases do not have sysadmin permissions
Solution
Login to the SQL Server Management Studio and connect to the SQL database.
In the SQL Server Management Studio, in the Object Explorer, navigate to Security > Server Roles > sysadmin
Right click sysadmin and select Properties.
In the Server Role Properties - sysadmin dialog box, determine if the credential which is added in the Phoenix credential store and assigned to the VM is listed under the Members of this role section. If it is not listed, proceed to step 5.
In the SQL Server Management Studio, in the Object Explorer, navigate to Security > Logins. Locate the credential which is assigned to the VM from the Phoenix credential store. Right-click this credential, and select Properties.
In the Login Properties dialog box, in the left pane, click Server Roles.
In the right pane, select sysadmin and click OK.
Issue: The server authentication mode for the SQL instance is not set to the Windows Authentication mode.
Solution
Change the server authentication mode for the SQL instance to Windows authentication. See the Microsoft article Change server authentication mode for more details.
Issue: The volume that hosts the volume shadow copy storage area has insufficient space for the shadow copies.
Solution
Refer to the Microsoft article on VSS Storage Space Availability to resolve the issue.
Issue: VSS errors on the virtual machine
Solution
Refer to the Troubleshooting VSS (Volume Shadow Service) errors article to resolve the VSS errors.