2
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 
     '\\nst-shantanug\c$\Dummy Db Backup\dummydb.bak'. 
Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

What all permissions do I need to assign to drive or pc where I want to take backup of my database.

Shantanu Gupta
  • 331
  • 1
  • 3
  • 11

2 Answers2

2

The SQL Service account should have write access to the folder to be able to write the backup there.

Depending if you also want to create subdirectories, overwrite backups or delete old backups you need appropriate rights for that too.

Tom V
  • 15,670
  • 7
  • 63
  • 86
  • I am manually running the script on SSMS. SQL Server is running under service account. Can I assign permission to service account on a shared folder? – Shantanu Gupta May 27 '15 at 09:09
  • depending on the file server yes, the easiest would be to create a dedicated service account for each service or groups of services – Tom V May 27 '15 at 09:33
  • 1
    If the SQLServer is running under the service account you will need to give the computer account access to the fileshare. @TomV the SQL Server service account will always make the backups - The agent passes in the command but the SQL Server service will write the backups. – Spörri May 27 '15 at 09:52
  • Ah learn something new every day, i'll update the answer – Tom V May 27 '15 at 10:27
2

Typically the SQL Server service account does not have access to administrative shares like c$ - unless you are running your SQL Server service using an account that is a domain administrator (or a domain account with administrative rights on the other box) - which you definitely should not do.

Try using a defined share rather than the built-in $ shares, and giving the SQL Server service account (which can be a domain account but not in a highly-privileged role) explicit read/write privileges on the share and on the underlying NTFS.

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614