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

Posted on 2009-04-23
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
    LVL 57

    Expert Comment

    by:Raja Jegan R
    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.
    LVL 1

    Author Comment

    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.  
    LVL 1

    Accepted Solution

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now