Error when doing Full back up on SQL Server 2016

Question

Dear Community,

I wanted to allow SQL Server 2016 to do back up at midnight.

However, my server keeps prompting me this error every night.

To be sure, when I manually do the backup, I was able to do it without any issue everytime. It only happens when I try to backup automatically.

Please advise what is the issue.

What does it mean when It states "Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Date 30/6/2021 3:00:00 AM
Log Job History (Full Backup at 3am Test2.Subplan)

Duration 00:00:04
Step ID 1
Server ABCDatabase\Navisiondb1
Job Name Full Backup at 3am Test2.Subplan
Step Name Subplan_1
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: NT Service\SQLAgent$NAVSQL. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5026.0 for 64-bit
Copyright (C) 2016 Microsoft. All rights reserved.
Started: 3:00:01 AM Progress: 2021-06-30 03:00:02.03
Source: {96CD49D3-2F2A-424D-998D-81B47CC91B7D}
Executing query "DECLARE @George Wang UNIQUEIDENTIFIER
EXECUTE msdb..sp...".: 100% complete End Progress

Error: 2021-06-30 03:00:02.83 Code: 0xC002F210
Source: Back Up Database (Full) Execute SQL Task
Description: Executing the query "BACKUP DATABASE [Navisiondb1] TO DIS..."
failed with the following error: "Cannot open backup device
'D:\Database Backup\SQl backups\Navisiondb1_backup_2021_06_30_030002_7387952.bak'.
Operating system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.".

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly. End Error
DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:00:01 AM Finished: 3:00:02 AM Elapsed: 1.875 seconds.
The package execution failed. The step failed.

Thank you!

Answer 1

Hi,

Description: Executing the query "BACKUP DATABASE [Navisiondb1] TO DIS..."
failed with the following error: "Cannot open backup device
'D:\Database Backup\SQl backups\Navisiondb1_backup_2021_06_30_030002_7387952.bak'.
Operating system error 5(Access is denied.).

As mentioned in the error, access is denied, that is, the startup account of the SQL Server agent service does not have the permission to write the backup to the above disk location.

Please grant the startup account of the SQL Server agent service read and write permissions to the directory(D:\Database Backup\SQl backups). Since NT Service\SQLAgent$NAVSQL is a virtual account, you can only directly enter the account name and search.

Or change the startup account of the SQL Server agent service to a privileged account.

Answer 2

What does it mean when It states "Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

I take that to be a generic blurb from SSIS or whatever you are running this from. The important part is "5(Access is denied.)".

To be sure, when I manually do the backup, I was able to do it without any issue everytime. It only happens when I try to backup automatically.

When you backup manually, do you use that exact path?

Since BACKUP is performed by SQL Server without impersonating the actual user, it should not really matter if if you are running it as a job or manually, I think.