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 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 original database state. As soon as I ran the backup command I ended up with following error. This error is due to permissions issue on the local disk and user account which is running SQL Server.

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 account my SQL Server services are running.

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

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

Reference: Pinal Dave (http://blog.SQLAuthority.com)

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

  1. 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.

    Like

  2. 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

    Like

    • 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.

      Like

      • 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.

        Like

  3. 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 ‘\\Computername\SharedFoldername’. 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.

    Like

  4. 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

    Like

  5. 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!

    Like

  6. 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.

    Like

    • 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

      Like

  7. Pingback: SQL SERVER -Fix Error – Cannot open backup device. Operating system error 5(Access is denied.) | Journey to SQL Authority with Pinal Dave

  8. Pingback: SQL SERVER – Fixing Backup Error – Operating system error 5(Access is denied.) – SQL in Sixty Seconds #077 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s