Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.).

Question

SQL 2005 Enterprise

Even if I logon as Administrator and try to backup any of my databases to local partitions, I get this error below. I have been operating fine up until this point and doing backups all the time.  As far as I know, and after checking, no permissions have changed and of course Administrator has ntfs permissions to my local C and other partitions on this server:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Backup failed for Server 'servername'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

In the Event log for Windows Server 2003 I see this:

 

Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
Date:  3/6/2006
Time:  3:35:06 PM
User:  NT AUTHORITY\NETWORK SERVICE
Computer: servername
Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
 to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20).  This security permission can be modified using the Component Services administrative tool.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Answer 1

You need to check the security permissions on the folder you are trying to backup to. Make sure the service account (Network Service) has write permissions. It may well not do.

SQL on changing the service account changes the permissions on the critical drives it knows about but won't to it for all the drives in a server.

Answer 2

well I added Network Service with the right permissions.  Still same error.  Like I said before this was never a problem.  I am the only one who touches the server and I haven't changed permissions except to change what permissions Network Service had in logons but I changed that back wto what it was previous and what my other server has in SQL logins.

Answer 3

It doesn't matter who *you* are logged in as, it is the service account for SQL Server service that 
matters

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/ 

Answer 4

Thanks a lot NNTP, that was it. I added my domain account that I had created to run all SQL Services to the security of the partitions on my server and I have access now.  I'm not sure though what changed, I was able to do this before unless someone took this login out of the security..I guess it's possible but as far as I know nobody it touching my server

Answer 5

Tnks God I found this post... It really save me!!!

rgds

Answer 6

Hi,

I have the same problem. I have changed the SQLAGENT  service to run as domain\administrator but are you suggesting the sql server instance service also?

Thanks

Paul

 

 

 

Answer 7

On a Windows 2000 Server with SQL Express 2005 freshly installed, giving the "SYSTEM" user full control on the target folder seems to do the trick without the need to change the service user.

Answer 8

SQL Service account is DOMAIN\SQLAdmin

Have given the SQLAdmin AD account access to the Backup shares on the file servers (2 file servers, FILE1, FILE2)

and I still get the error

Operating system error 5(Access is denied.).

RESTORE FILELISTONLY

FROM DISK = N'\\SERVER\APPS\backups\UA\DB\DB.BAK'

I trid to attach it as device, which runs

sp_addumpdevice 'disk', 'Network_Share', '\\SERVER\backup\APPS\UA\DB\DB.BAK'

BUT when restoring, same error occurred (Access denied)

Only case I can backup/restore to/from network shares, is from the machines where SQL Server runs (SQL1, SQL2)

e.g. I copy the BAK file from FILE1 to SQL2, share the folder (add SQLAdmin account with access), then restore on SQL1 with file from SQL2 Share

RESTORE FILELISTONLY

FROM DISK = N'\\SQL2\backups\UA\DB\DB.BAK'

Answer 9

 

When you try to backup, SQL Server 2005 shows the default backup path i.e. c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ . This directory default has SQL user rights. Go to Backup directory and check the user name. Its very big name and I have not checked how SQL server adds it in allowed users list. You can do 2 things:

 

1. Let the Server take backup on default directory and copy the file in your desired folder. I did that.

2. Give same kind of permissions to your desired folder as Backup directory has.

 

Hope it solves the issue.

 

Moonguy

 

 

Answer 10

This problem occurs when you are attempting to backup to a UNC patch and the SQL server does not have "modify" rights to the backup path. With the backup path sharepoint, and on the backup directory itself, grant the SQL server "modify" rights - when doing this be sure that "computers" is selected under "object types"

 

 

Hopes this helps

Answer 11

You are a life saver...  This was driving me nuts.  I must add this to my blog.....

 

Answer 12

Thanks alot MoonGuy,

also i found that the installation path & Backup Directory of SqlServer Express can be found at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer in BackupDirectory key.

Answer 13

I ran into a similar scenario where I wanted to backup my Testing databases on to the E:\ of my Dev database server for storage.

 

I just added the service account (LOCAL\sql05svc)  for Sql Server into the local admin group on the Dev machine where I am writing the backups to and it worked out fine too. The account did have the same password on both machines though.

 

I am guessing you could do the same with a Domain account that you use to run Sql Server service with.

 

Answer 14

Hey wat is the solution?? Can u plz share with me, I dint get wat exactlly u guys are talking about...  I am getting the same problem, I have a user_account and i dont know what permisions it has, I took a backup from the system connected in LAN, and when i tried to restore the database after taking backup, I got this error.  Its troubling a lot yaar, plz get me a solution and take me out of this.....

 

Answer 15

great help..i had the same problem and i have resolve it doing the follow

 

my operating system is winXP pro.

and MSDE 2005

 

Control panel->administrative tools->services-> SQL Server

at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'

 

thanks for the help i have spent more than four hours googling for it

 

Answer 16

"Control panel->administrative tools->services-> SQL Server

at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'"

 

If I believed in a God I'd ask him/her/it to bless you. I love it when people explain things

 

Dominic

Answer 17

This happened to me when I attempted to create a new database from a .bak file using RESTORE.  I just right-clicked on the .bak file, selected Properties and then clicked the Security tab.  Then, I did a Check Name for "SQL" and added the following two SQL accounts from the list:

1)  SQLServer2005MSSQLUser$<machine name>$MSSQLSERVER

2)  SQL

The reason this happened to me is because I was attempting to create a database from a .bak file which was not created on my machine.  Whenever you backup an existing database, the above SQL accounts are already added to the security profile of the backup file.  Because I was using a .bak file created on another machine, the necessary SQL accounts on my machine hadn't been granted permissions.  Adding them manually helped. 

If you run into a similar problem at the folder-level, try right-clicking to see if the above two accounts exist within the Security tab. 

Answer 18

Thanks, that helped a lot.

 

AvenueStuart wrote:

This happened to me when I attempted to create a new database from a .bak file using RESTORE.  I just right-clicked on the .bak file, selected Properties and then clicked the Security tab.  Then, I did a Check Name for "SQL" and added the following two SQL accounts from the list: 1)  SQLServer2005MSSQLUser$<machine name>$MSSQLSERVER 2)  SQL The reason this happened to me is because I was attempting to create a database from a .bak file which was not created on my machine.  Whenever you backup an existing database, the above SQL accounts are already added to the security profile of the backup file.  Because I was using a .bak file created on another machine, the necessary SQL accounts on my machine hadn't been granted permissions.  Adding them manually helped.  If you run into a similar problem at the folder-level, try right-clicking to see if the above two accounts exist within the Security tab.

Answer 19

"Control panel->administrative tools->services-> SQL Server

at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'"

totally agree. otherwise i was still lost. Thanks heaps.

Answer 20

 

Make sure you dhould be having enough access on the drive. from SQL level and OS level too.

Answer 21

Arxigos wrote:

great help..i had the same problem and i have resolve it doing the follow   my operating system is winXP pro. and MSDE 2005   Control panel->administrative tools->services-> SQL Server at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'   thanks for the help i have spent more than four hours googling for it

 

Thank you for explaining, i was lost, and i didnt know wut else to do thanks

Answer 22

I was having the same problem trying to restore a backup file sent to me.  Heres how I fixed it:

Copied the backup file into SQL's default backup directory (Program files\SQL\MSSQL.1\Backup)

Right clicked the file, properties

Under Security clicked Advanced

Check "Inherit from parent...." apply and ok


Should do the trick....in my case the directory had the right permissions so if I did a backup the file would get the right permissions but on a file sent to me it did NOT have the right permissions.  Doing this assigns the same permissions and the backup folder and also then you don't have to make any other system changes since you just changed the file.  Hope this helps someone.

-Allan

Answer 23

Thanks, this solution worked for me

Answer 24

Thank you Simon Sa,
Even i had the same error while i was trying to backup to my local drive and I added my NETWORK SERVICES to allow to write onto my local Drive this was very helpful to me Thankuuu....

Answer 25

Arxigos wrote:

great help..i had the same problem and i have resolve it doing the follow   my operating system is winXP pro. and MSDE 2005   Control panel->administrative tools->services-> SQL Server at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'   thanks for the help i have spent more than four hours googling for it

This worked perfect for me.
Thanks!!!

Answer 26

Thanks for all who gave this great info. Save me a lot of time...

 

Answer 27

 

Hello all,

 

I had a similar issue with this problem while restoring a database backup file.

We were able to solve the problem by giving Read permissions to the SQLBrowserUser.

 

You can find the discussions at http://www.kodyaz.com/forums/thread/3515.aspx

 

Answer 28

Thank You Thank You Thank You - This saved me too. I'm so thankful for you taking the time to reply.

 

Answer 29

İt worked for me . Thank you very much Simon.

I added NETWORK and NETWORK SERVICE accounts to the copy folder permisssions and security tabs.

 

SimonS_ wrote:

You need to check the security permissions on the folder you are trying to backup to. Make sure the service account (Network Service) has write permissions. It may well not do. SQL on changing the service account changes the permissions on the critical drives it knows about but won't to it for all the drives in a server.

Answer 30

Hi,

Just a google searcher who found your post and wanted to say thanks!  Your directions solved my issue.  Appreciate you taking the time to write it down!

-RandyB

Answer 31

Hi  Arxigos,

Thanks...

---

alg

Answer 32

Same problem, same solution... thanks a lot.

Answer 33

Hi I seem to be running into a similar issue. Trying to use the DBCopyTool from codeplex, my issue is that I receive the error when the application is trying to copy the .bak file from my local machine to the backup folder on the remote machine...so I new to SQL any detailed response would be helpful seems like most users are receving a .bak file to restore...I'm sending a file to a remote machine to have it restored, thanks.

Error restoring the database. Cannot open backup device 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\QCTL_new_CopyTool.bak'.
Operating system error 2(The system cannot find the file specified.).
RESTORE FILELIST is terminating abnormally.

Answer 34

I tangled with the same problem.  I solved it by changing the SQL Server Service Logon to Local System.  Which is weird because all my other SQL Servers use the NetworkService account.  

I've backed up and restored dozens of databases on many different servers all over the place and I've only seen this problem on this one machine.  I'm not sure why this is like this.  

The machine was recently rebuilt due to a RAID failure but I'm not sure that was the problem.  Bizarre.  

If anyone finds out why this happens in the first place I'd really like to know.  

Answer 35

Thanks to  Arxigos,

Was also googling several hours to find an answer.


Thanks again for the solution.

DKV

Answer 36

you guys are real life-savers. you save my day.

---

Man may come and Man may go, but I go on forever

Answer 37

Saved me too.  This one made no sense.  I was working with an application called MediaBin which invokes the backup out of SQL 2005.  Well, I moved the repository for MediaBin to a new bigger location and the backup immediately failed.  Thank god I was able to find the error message "Operating system error 5(Access is denied.)" in a log file.  Thanks!

Answer 38

Thanks for your help. It saved me too.

Answer 39

In order to simplify the issue, for our particular configuration, I just created a share on the SQL Server, and gave "Everyone" full control. Since our SQL install is run as the "Local System" account, I surmise that this allows that user to have full access to the share. I plan to copy the backup files to the new SQL Server, and do the same thing there for the restore operation. I might want to remove the share after the operation has completed... :D

Answer 40

Maybe EveryOne is not so suitable to use, ever... One way is to check the default backup location: C:\Program\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup and either use that path for backup, or use the same security settings on another folder. SQL server makes its own users and groups for different purposes at install and that works fine to use them, eg SQLServerMSSQLUser$computername$SQLEXPRESS.

Answer 41

 

I have written an article on SQL Server backups and the solution for this issue,

 

http://venkattechnicalblog.blogspot.com/2010/04/database-backups-in-sql-server.html

 

Cheers,

Venkatesan Prabu .J

---

Venkatesan Prabu .J http://venkattechnicalblog.blogspot.com/

Answer 42

This errors has been an ongoing issue:

1. System.Data.SqlClient.SqlError: Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

and

2. Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\//ves10/New/filenam.bak'. Operating system error 3(The system cannot find the path specified.)

 

The easy way to solve this is by:

1. going to the properies of : C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL ,  on the security tab give full access to the user SQLSerevr2005MSSQLUser$ the full access

2. Create a new user login account in the SQL server and give it all the privilages present which include  sysadmin, setupadmin, dbcreator etc.

Answer 43

>> Control panel->administrative tools->services-> SQL Server

>> at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'

 

Thanks a lot, it is still saving people lives years after posting this solution. But I wonder what made it behave like this in the first place!

Answer 44

Arxigos ... Thanks a lot buddy.

- Vijay Kumar Jayabal

Answer 45

Hi

    I am to getting this same error and I solved this by

 >> Control panel->administrative tools->services-> SQL Server

>> at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'

Is it possible by someother way to overcome this error possibly a sql query? or should we need to add the command line to enable 'local system account' at the time of SQL Express installation itself?

Please help me

Thanks in advance

Prabhakaran

 

 

Answer 46

here it is 6/2010 and this is still a problem...I understand the solution, but it shouldn't matter where I want to backup a db, becuase 1-it shouldn't matter where I want to backup a db and 2-becuase it my computer...who's in control here

Answer 47

wow..

SQL 2005:I am running into this same issue, but in my case, backing it up to a share in a different domain.

SQL Server\SQL Server agent started with Pduct\domain account and backing databases to a share in a non-pduct domain share.

It works fine if I backup for the same non-pduct domain sql server, but not from pduct domain.  Getting 'Access is denied'. I tried all the above options mentioned.

Any idea what else can I do to solve this. Having the network eng to look into permissions on the share, waiting for a reply, meantime I found this.

Answer 48

You may need to check the domain connectivity. I have seen where connect from one domain to other they used to use bridge domain. It means from domain A your AD group belogs to and you want to connect to domain B, then you have to connect to the domain C_B and from C_B domain you can connect to the domain B.

Definatly it is lengthy process to copy your files on the different domain.

As you mentioned "Access denied" that means your domain B account is not trusted with the server.

 

---

Manu Please click the Mark as Answer if my post solves your issue.

Answer 49

I had the same problem. checked the folder, removed the "READ ONLY" and "Service" to the list of accounts who have permission and it WORKED. THANKS to all of you

 

Michael

Answer 50

I have the same problem...

 

Trying to backup an external database, and save the backup to my local computer!

 

First of all the folder "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup" did not exist...

So I tried create it manually, and do backup... No file is created, but the program says, that the backup is successfull... Do not understand why???

Answer 51

Ok... I found another way to do it...

 

Downloaded this program to do a backup:

http://www.microsoft.com/downloads/details.aspx?familyid=56e5b1c5-bf17-42e0-a410-371a838e570a&displaylang=en

 

Maybe this could help someone... Helped me :)

 

-

Jesper

Answer 52

Thanks AvenueStuart. It worked for me.

Answer 53

I agree ... everything worked fine one day and then I'm guessing after installing some patch I now have to explicitly go to the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL" directory, right click and select Properties then go to the Security tab and add "NETWORK SERVICE" as having full access.  What the heck is that all about?  Why does it matter all of a sudden?  I've done this restore from database many times in the past and it never wrote any files to that directory before, it always wrote to the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory. 

Also I DID NOT change the "login as" for the SQL Server service because I WANT it to use the NETWORK SERVICE account not a local system account.

Answer 54

let me simplify it, go to control panel>services and see the "Logon As" for MSSQLSERVER. Remember this user.

Now go to ur desired backup folder(anywhere in ur Network).

Righ click>property>sharing>permission

Now the same user with full access control. Apply

u r done!! Go agead and make ur back up.

Answer 55

Solution posted by Moonguy close to the top worked for me.

Every database I worked with restored fine ecept the one I needed to work with, until I gave the MSSQL user account permissions to my folder, or copied the backup file to the MSSQL.2008\backup folder.

Why other databases worked fine, or this problem only croppe dup for this DB, I don't know, but it works now.

Answer 56

I had the same issue but fixed it like this:

1. Create and share the folder (in my case c:\backup\). Then give everyone read/write permissions for the folder (\\SERVERNAME\backup\). Alternative you could give the NETWORK SERVICE full control in the share

2. Use the UNC patch as export location \\SERVERNAME\backup\

And then rock on :)

Answer 57

@Moonguy!

Thanks for the solution it works "Go to Backup directory and check the user name. Its very big name and I have not checked how SQL server adds it in allowed users list"

I added the SQLusername to my folder and it works!

 

 

 

 

Answer 58

Thanks so much, this did the trick for me!

Answer 59

Adding the service account and giving write permissions to it solved the problem for me.

Answer 60

Had the same problem running Virtual Server 2008 with SQL 2008. Added the MSSQL account to the permission list for the folder I wanted to save backups in and it worked. Thanks

Answer 61

This worked for me. Just added those users to have permission on my backup folder. Thank you very much.

Answer 62

i am facing the same prblm i am wrking on asp.net & trying to backupa file on network folder

i have given all permission to everyone to the shared network folder still i get the above error msg

---

svk

Answer 63

Same do I, thanks alot

Answer 64

My solution was to copy the .bak file to a USB drive, then restore it from there.

Answer 65

You are great. Thanks for explaining step by step.

Thanks,

Rushabh.

---

Rushabh

Answer 66

This problem occurs when you are attempting to backup to a UNC patch and the SQL server does not have "modify" rights to the backup path. With the backup path sharepoint, and on the backup directory itself, grant the SQL server "modify" rights - when doing this be sure that "computers" is selected under "object types"

 

This fixed my issue. Thank you!!

Answer 67

When you try to backup, SQL Server 2005 shows the default backup path i.e. c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ . This directory default has SQL user rights. Go to Backup directory and check the user name. Its very big name and I have not checked how SQL server adds it in allowed users list.

Thank you, I hadn't even considered file permissions :)

Answer 68

Not sure I'm understanding this.  So...to test backups I tried to run a farm backup to c:\backups on my sharepoint app server - failed.

I gave the SQL svc, SQL agent, network and network service accounts full control on this directory.

Do I also need to give rights on the SQL server default backup directory actually on the SQL box?

Answer 69

Control panel->administrative tools->services-> SQL Server

at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'

 

THIS WORKED FOR ME - THANKS!

Answer 70

Here is a bit detail of the Answer that worked for me.

1. find out the sql server agent`s account.  Eg. Abebe (Services>right click on sql server agent> ppy> login...)

2. Go to the drive that you want to do backup to. Eg. M

3. right click on the M drive and select properties -> Security-> Edit-> Add-> the sql agent account( Abebe) and grant a MODIFY, READ and WRITE  rights(permissions)

4. Go to Services->Sql Server Agent (right click it and restart)

5. Now the SQL server Agent has enough permission on that drive to open and get access to it.

6.If you try to take a backup after the above steps it should work.

Good luck

P.S. If further help or clarification is needed let me know.

Also do the same for the account that runs the SQL Server as well.

Answer 71

This is correct.  I checked my SQL Server Service properties and I was running under "Network Service".  Once I granted Network Service read/write access to the location I was trying to back-up to, it worked.

Answer 72

I have gotten this error because when I created the database, I accidentally left a space at the end of the database name. This causes our backup job to produce an incorrect folder location and generates the very error you refer to. It is easy not to notice this in the backup job log and you would never notice this is the list of databases in management studio