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.
I gave full permission to that account to get rid of the error.
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)
54 Comments. Leave new
Simple but useful topic =)
Thx
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.
worked by copying to backup directory .. thanks
Thanks you Jonathan Kehayias
Thanks @Yasser
Yes, I too faced this type of problem and used same workaround…..
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
Thanx, it worked
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.
how did you solve the issue as i having the same problem…
Please suggest..
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
Dave, You rock… It resolved my issue.
You are just too much. Thanks for this piece. It was so much help.
you are Genious
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!
Tom Mani (@Tom_Mani),
I think your “from DISK” should be the path of your BAK file..
Just try..
Thanks.
It works.
Thanks a lot
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.
sorry the error code is 17058 and not 10758
If the backup drive do not have enough space, this work around comes in handy!
\
Dear Sir where can i do that permission ?
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
Thanks Pinal, exactly my issue.
Thank you… this fixed my problem
My pleasure venkatesh