none
Error while taking backup using SSMS

    Question

  • I am accessing my sql server 2008 r2 express server using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login(not windows authentication) In user roles it has all permission for almost all database.For some database it has only datareader.

    Now when i try to take backup of database using SSMS and when try to select backup destination by clicking button i get error like

    "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup
    Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists. 

    If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box."

    Even though I have db_owner and all permissions i am getting this error. But if I select the path manually in the file name field then it allows to take backup without any error.
    So why does it happen? I want this user to take backup without these error messages.(But i can't give sysadmin for the user).How can I solve it?
    (I already some article stating that use sql query and sqlcmd to take backup.but i want it to work using SSMS itself)
    Wednesday, April 24, 2013 1:54 PM

All replies

  • Hello,

    Please grant the SQL Server service account at least write permissions to the folder:C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup
    Reference:
    http://blog.sqlauthority.com/2011/04/13/sql-server-fix-error-msg-3201-level-16-cannot-open-backup-device-operating-system-error-5access-is-denied/

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Thursday, April 25, 2013 7:32 AM
  • Hello,

    Please grant the SQL Server service account at least write permissions to the folder:C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup
    Reference:
    http://blog.sqlauthority.com/2011/04/13/sql-server-fix-error-msg-3201-level-16-cannot-open-backup-device-operating-system-error-5access-is-denied/

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    If i use BACKUP DATABASE  command to backup database then it works. If i use the SSMS and then it gives error while i press "Destination on disk button" and the error message is shown. Below is image of that

    Now if i click on ok then i will get a blank browse window.(there will not be any directory list).But if i fill the file name textbox with the same path with file name then backup is created successfully.Below is image where i have given same path in file name field

    So if i do this way backup works. Also if i use t-sql command for backup then backup works.So i think it's not problem with permission on the folder.

    Then as you said i gave full permission for the folder and even whole drive for "network service" which is sql service account. But result was same as before that error message still appears.

    I think the SSMS has  a problem where it does not allow to show the directory list.

    I saw may link where there was mentioned to give permission, but which didn't work.

    In this link i saw(http://dba.stackexchange.com/questions/7701/cannot-browse-for-backup-files-using-ssms) that xp_fixeddrives  is used for displaying drives by SSMS. So this is preventing from displaying the directory list and giving error. Then i just run the statement "exec master..xp_fixeddrives" and no result was displayed. when i run the same sql command in login permission with sysadmin i get results with drives and its memory free.So is this the problem causing this error?

    So this is based on my observation. If it is not then please, reply me with the proper solutions for this problem

    Thursday, April 25, 2013 8:03 AM
  • Anyone help me to solve this problem.
    Thursday, May 02, 2013 7:15 AM
  • Hello,

    Can you try to run the SSMS as adminstrator?

    Summer

    Friday, May 03, 2013 4:56 AM
  • omg I have seen this in 12 places today.  and everyone suggests running SSMS as admin and everyone reports that it never works.  What is the deal with SSMS only accepting one path as the backup folder.  This feature needs an OFF switch!

    I'd rather live with false hope than with false despair.

    Tuesday, May 14, 2013 6:43 PM