SQL SERVER -Fix Error – Cannot open backup device. Operating system error 5(Access is denied.)

This has to be the most popular error in taking database backups. There are many reasons for this error and there are different ways to resolve it. I recently faced this error and for a while I was taking backup and found a very interesting solution to the problem. The error was as mentioned below:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘d:\Log\aw.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

SQL SERVER -Fix Error - Cannot open backup device. Operating system error 5(Access is denied.) error5-1

Personally, I know this error happens when

  1. There is no directory with Log
  2. There is not enough space on drive d:\
  3. There is not enough permissions on d:\Log drive

In my case all of the above were not the reason for above error. The error was there because of the file was read only. There was already another backup file which existed in the folder with the name aw.bak and it was marked as read only. Due to the same reason, my backup was failing. Once I removed the checkbox for reading only, the backup was successful.

SQL SERVER -Fix Error - Cannot open backup device. Operating system error 5(Access is denied.) error5-2

SQL SERVER -Fix Error - Cannot open backup device. Operating system error 5(Access is denied.) error5-3

Here are few other blog posts related to this error, which I have written earlier.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Finding if Current Week is Odd or Even – Script
Next Post
SQL SERVER – List the Name of the Months Between Date Ranges

Related Posts

20 Comments. Leave new

  • To expand just a bit on the inadequate permissions bullet point: It seems like I see this sort of thing when someone tries to give capability to backup and restore to a SQL Server Login, for obvious reasons. It doesn’t have file system access.

    Reply
  • Siddharth Pandey (@Siddharthp28)
    December 9, 2014 4:19 am

    Hi Pinal, thanks for these quick possible fixes for this issue. I experienced similar issue few weeks before and it was the permissions issue in my case. It was easy to solve as it has access word in the error.

    Reply
  • Unable to open the physical file “d:\sql_files\ctbse.mdf”. Operating system error 5: “5(Access is denied.)”.
    An attempt to attach an auto-named database for file d:\sql_files\ctbse.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    Reply
    • Check the service account by using SQL Server Configuration Manager. Then verify that account has permission on file “d:sql_filesctbse.mdf”

      Reply
  • Hi Pinal,
    I have win2k12 server having sql server when i take backup my data it will show a error like”cannot open backup device operating system error 5(error not found) backup databese is terminating abnormally” so help me to resolve the issue….

    Reply
  • Abhilesh Chandra
    August 13, 2015 5:07 am

    for me, adding serverusers account to my backup folder did the job. even though my sql login and my sqlserver service account had all the rights to modify the files in that location, adding users accounts with read access did the job.

    Reply
  • Sir I am trying to taking backing on network shared drive using ms sql server 2005
    and query is
    Backup Database [Data Sync] To Disk =’\\rnd-3\Share\Tese.bak’ With FORMAT

    and rnd-3\share is network path having rights everyone read/write
    but when i execute showing error


    Msg 3201, Level 16, State 1, Line 2
    Cannot open backup device ‘\\rnd-3\Share\Tese.bak’. Operating system error 5(Access is denied.).
    Msg 3013, Level 16, State 1, Line 2
    BACKUP DATABASE is terminating abnormally.

    Reply
    • Is that a share of machine? As per message, service account is not having permission. Check the service account by using SQL Server Configuration Manager.

      Reply
  • Hi sir ,
    Window Server 2008
    Sql Server 2012
    I am executing the SQL :
    BACKUP DATABASE [XYZ] TO DISK = N’C:backup-SQLXYZ.bak’
    WITH NOFORMAT, NOINIT, NAME = N’XYZ-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
    GO

    I my case this is not working and still the following error :

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device ‘C:backup-SQLXYZ.bak’. Operating system error 5(Access is denied.).
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    Reply
  • Hi Pinal,

    I am having issue while taking backup to URL

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device ‘https://XXXX.blob.core.windows.net/uxxxc01/xxxxxdb_backup_2017_04_05_094308.bak’. Operating system error 50(The request is not supported.).
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    I have scheduled job was running fine till yesterday but it is failing now i checked the access but seems like it have permission.

    Reply
  • walujwarvishal
    June 1, 2017 7:26 pm

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

    Reply
    • walujwarvishal
      June 1, 2017 7:27 pm

      What to do, I tried it from log , application log but didnt get any useful information.
      whenever i m trying to do backup for any database it will show this type of error massage.

      Reply
  • FYI… in the above explanation you are taking the backup ,, i am wondering how you given the permission to backup file,,, we are not restoring backup file, could please tell me how we give permission to the future backup file.,,
    can you explain me difference between backup and restore

    Reply
  • Kartheek Chandra Ambati
    June 9, 2018 4:38 am

    This did not solve my problem. I was finally able to solve this by following the below steps
    1. Navigate to Start/Services
    2. Look for the service SQL Server (MSSQLServer) and see the username under ‘Log On As’
    3. Add this user to have full control of permissions on the directory of back up by navigating to the folder / properties/ under secirity tab / Edit and give the same username and click on Check and Ad by giving full control.
    4. Save
    Now running the query of backup created the file.

    Reply

Leave a Reply

Menu