locked
SQL 2012 - Backup to remote device RRS feed

  • Question

  • Hi

    I need tips in resolving "access denied" errors on a network share.

    Using the agent account i can manually copy the local BAK to the remote share.

    No problem. The remote share has the agent account with full access.

    The Device is setup correctly with the correct remote path.

    Failed:(-1073548784) Executing the query "BACKUP DATABASE [Web TimeSheet] TO  [TimeSheet_Ful..." failed with the following error: "Cannot open backup device 'TimeSheet_Full.bak(\\\\our-network-fileserver\\sql_backups\\TimeSheet_Full.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.

    T-SQL

    Command:BACKUP DATABASE [Web TimeSheet] TO  [TimeSheet_Full.bak] WITH NOFORMAT, INIT,  NAME = N''Web TimeSheet_backup_2013_06_14_171333_0835022'', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10

    Thanks


    Friday, June 14, 2013 7:34 AM

Answers

  • How does one determine the account used by the database engine ? Is this "SQL Server" , it is using localsystem.

    Local system is not good if you want to access network devices. Change this to a domain account or a service SID with access to the remote drive. (I think that services SIDs can be granted access to remote shares.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 14, 2013 9:20 PM

All replies

  • Hello Greg,

    The backup job is may started by SQL Server-Agent, but the BACKUP itself is done by the SQL Server Database Engine in the context of that service account.

    So you eigther have to grant permission for the service account of DB engine, or you have to backup first to a local drive and then move the backup file by a SQL Server-Agent job to the network share.


    Olaf Helper

    Blog Xing

    Friday, June 14, 2013 7:47 AM
  • I wrote an article on this, you can refer to it for guide lines and prerequisite.

    http://sql-articles.com/articles/general/backup-sql-database-to-remote-location/


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Friday, June 14, 2013 9:17 AM
  • Olaf

    This is a real revelation as all indications are that the agent account ot the account you specified (using creds in SQL) is what runs the task!!

    How does one determine the account used by the database engine ? Is this "SQL Server" , it is using localsystem.

    I see no service for this and nothing in the "server roles" ?

    NB: Both the agent and the account used to setup SQL have full access to the network area.

    Thanks

    Friday, June 14, 2013 10:08 AM
  • How does one determine the account used by the database engine ? Is this "SQL Server" , it is using localsystem.

    Local system is not good if you want to access network devices. Change this to a domain account or a service SID with access to the remote drive. (I think that services SIDs can be granted access to remote shares.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 14, 2013 9:20 PM