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.

2 Answers 2

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.

4
  • 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? May 27, 2015 at 9: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, 2015 at 9: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, 2015 at 9:52
  • Ah learn something new every day, i'll update the answer
    – Tom V
    May 27, 2015 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.