Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2009-04-23
Medium Priority
Last Modified: 2012-06-27
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:

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?
Question by:dtfla
  • 2
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24218724
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.

Author Comment

ID: 24218957
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.  

Accepted Solution

dtfla earned 0 total points
ID: 24219174
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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question