SQL 2005 Agent Not able to write to the local File system for a backup job.

When I run the following statement in a SQL 2005 Agent Job:

backup Database [dbMain] TO DISK = N'C:\DBMainBU'  WITH INIT, NOUNLOAD, NAME = N'dbMainBU.bak', NOSKIP, STATS=10,NOFORMAT

I get the following result:

Message
Executed as user: Mainoffice\administrator. Cannot open backup device 'C:\DBMainBU'. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

The SQL DB and SQL Agent are both running under the local system account.  I have tried running them both under the administrator account to no avail.  I am able to run a manual backup from SSMS sucessfully, but not from SQL Agent.

This should be a simple backup job with no problems.  I have created other jobs in the same manner on other servers that run flawlessly.  What could be wrong?  Why is the agent not able to write to the file system?
LVL 1
dtflaAsked:
Who is Participating?
 
dtflaAuthor Commented:
Emabrassing as this is, I wrote the File Names wrong.  This was simply my carelessness.  The incorrect and correct statement are as follows:
backup Database [dbMain] TO DISK = N'C:\DBMainBU'  WITH INIT, NOUNLOAD, NAME = N'dbMainBU.bak', NOSKIP, STATS=10,NOFORMAT
backup Database [dbMain] TO DISK = N'C:\DBMainBU\dbMainBU.bak''  WITH INIT, NOUNLOAD, NAME = N'dbMainBU', NOSKIP, STATS=10,NOFORMAT
Note that the file name and the logical names were changed.
 
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Its an access violation error.
Right click the services in SQL Server Configuration Manager and check what account is configured in the Security Tab. It should be System Account by default.
Do it for all SQL Services and if not change it to System account so that System account can write Backup file in the specified path.

You can also provide your user account provided your user account have write permissions on the mentioned share path.

Kindly refer "Setting up Windows Services Accounts" in BOL for more info.
0
 
dtflaAuthor Commented:
Thank you for the reference. That is my point (though I was not descriptive enough).  All services for SQL in the SQL Server Configuration Manager are set to the "Local System" account.  I have checked that "Full Control" rights have been given to the Local System account for the target folder.  That is what is baffeling me.  It should be set up correctly, but it just will not work.
I also tried to run the Backup Database statement in a Query Window in SSMS and received the same error.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.