Thursday, February 9, 2017

Cannot open backup device. Operating System error 5

I created small utility program for different routine tasks to run on different schedules, one of which is to take database backup to a local folder, and it was working fine. To have access it across network for selected users, and making it available for copy/move to other backup devices, we shared this folder on network. This was the moment I start getting this error in utility error log. In this post I will show the trick I used to solve this problem in our case. First let me share the script I used to take backups, here it is:

BACKUP DATABASE MyDatabaseName
TO DISK = 'D:\DBBackups\mydb-datetimestamp.Bak'  
 

After sharing the folder on network for specific users, it started generating the following error message:

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device 'D:\DBBackups\mydb-datetimestamp.Bak'. Operating system error 5(Access is denied.).

    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

Solution

  • Open Windows Services Screen. (Start > Administration > Services)
  • Find the Service in the list with name SQL Server (MSSQLSERVER), check for the Log On As column (if it doesn't already exist in the list, you can add it from the View menu > Add / Remove Columns...).

    The name of the account you see in Log on As column, is the account you need to give permissions to the directory where you want to place backups.

  • Right click on the folder. Go to > Properties > Share and Security tabs. And add permissions for required account.
  • Click Apply / OK, and let the permissions take effect.

Now try taking new backup, and it should work successfully.

No comments:

Post a Comment