SQL SERVER – FIX – ERROR : Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)

Recently I formatted my computer and installed fresh SQL Server in it. I installed the AdventureWorks database in my database. Once done, I wanted to run few test scripts on my database. Just like every DBA, I decided to take backup of my database – this way I can restore it back to attain an original database state. As soon as I ran the backup command I ended up with the following error. This error is due to a permissions issue on the local disk and user account which is running SQL Server. In this blog post we will talk about the operating system error.


Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:\AdventureWorks.bak’. Operating system error 5(Access is denied.).

Fix/Resolution/Workaround:

I checked which user accounts my SQL Server services are running.

SQL SERVER - FIX - ERROR : Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.) NetworkError1

I gave full permission to that account to get rid of the error.

SQL SERVER - FIX - ERROR : Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.) NetworkError2

NOTE: This whole experience was done on the my personal machine and not on the production server. Please check with your network administrator for permission on production server.

Let me know if you have ever faced such error and if you have, how did you fix the error. Please leave a comment with your answer. I hope you find this blog post helpful.

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

, , ,
Previous Post
SQL SERVER – Query to Recent Query on Server with Execution Plan Function to Get SQL
Next Post
SQL SERVER – Transaction Log Impact Detection Using DMV – dm_tran_database_transactions

Related Posts

52 Comments. Leave new

  • Simple but useful topic =)
    Thx

    Reply
  • Jonathan Kehayias
    April 13, 2011 12:41 pm

    Hey Pinal,

    The secure way to solve this problem is to place the backup file in the BACKUP directory for SQL Server which will have the necessary permissions granted on it for the service account already. Access to the root of any storage device in Windows is secured by default in Windows Server 2008 and 2008R2 and I wouldn’t recommend adding to the ACL list there, I generally remove permissions from the root to further lock down the system.

    Reply
  • Yes, I too faced this type of problem and used same workaround…..

    Reply
  • Hi Pinal,
    I have a optimization question for you:
    I have 2 tables, one with rowcount of 280623 and the other with rowcount of 317723. Both the tables have a clustered index on a column (int data type) called ID. When 2 tables are joined on the ID column, the execution plan shows a CLUSTERED INDEX SCAN, i was thinking it will perform a SEEK. Any thoughts about this…

    SELECT A.ID FROM tablea A
    INNER JOIN tableb B
    ON A.ID=B.ID

    Thank you

    Reply
    • Hello Sir,

      The reason it may be when it is retrieving result it must be getting many of the rows of the both of the table and not only few. If you have restrictive (or very selective) WHERE condition it will do SEEK otherwise it will do scan.

      Reply
      • Thank you Pinal for the explanation. Keep up the good work, you are an inspiration for lot of the folks in the SQL Server Community.

  • Thanx, it worked

    Reply
  • Tom Mani (@Tom_Mani)
    November 4, 2011 11:57 pm

    Pinal, when trying to backup a database to another machine on the network I get an error of

    System.Data.SqlClient.SqlError: Cannot open backup device ‘\ComputernameSharedFoldername’. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

    The shared folder has permissions set for everyone full control. I even made the built in SQL user have full control. Not sure exactly where it is having the permission issue at. Thank you.

    Reply
    • how did you solve the issue as i having the same problem…
      Please suggest..

      Reply
      • I guess, the Problem here was that Tom only provided a SharedFoldername and not a Backup File.
        The correct path should be; \\Computername\SharedFoldername\BackupFileName.bak

  • hello
    I have one small problem:
    on an external drive I create a folder “data-storage” that all users can access the backup this folder is normally
    I chose a user that I gave him all the privileges of the root folder, but the backup is not on the root folder.
    error: can not open backup device

    Reply
  • Ravishankar Polepalli
    December 29, 2011 3:07 pm

    Dave, You rock… It resolved my issue.

    Reply
  • You are just too much. Thanks for this piece. It was so much help.

    Reply
  • you are Genious

    Reply
  • Thank you SQL Genius!

    You know what, I have now made a point from last 6 months, in case I face any issues with SQL Server, then I directly come to your blog and start searching.

    Thank you very very much for writing so nice articles!

    Reply
  • Tom Mani (@Tom_Mani),

    I think your “from DISK” should be the path of your BAK file..
    Just try..

    Reply
  • Mohammad Rafi
    July 10, 2012 4:23 pm

    Thanks.
    It works.

    Reply
  • Thanks a lot

    Reply
  • Hi Dave,

    Its glad that i got the solution to the abpve mentioned error code. but very sad i couldnt change the Log on as network service.

    when iam trying to change it to Network Service and restaring the service, iam getting the error saying that couldnt start the service. error code 10758.

    I need it as soon as possible. I will be very much thank ful if you provide a solution to this error as early as possible.

    Thanks,
    Pavani.

    Reply
  • sorry the error code is 17058 and not 10758

    Reply
  • If the backup drive do not have enough space, this work around comes in handy!
    \

    Reply
  • Dear Sir where can i do that permission ?

    Reply
    • Hi,
      Go to DATA folder of MSSQL (C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA) –> RightClick on DATA folder –> Properties –> Security Tab –> create NETWORK SERVICE group –> then give full control to that group

      Reply
  • Thanks Pinal, exactly my issue.

    Reply
  • Thank you… this fixed my problem

    Reply

Leave a Reply

Menu