Backup/Restore permission

  • AhTu_SQL2k+

    SSC Eights!

    Points: 839

    Hi there, I want to dedicate a person (eg. 'BackupManager') who can only performce backup and restore certain databases. At BackupManager's properties I had checked db_backupoperator.

    When I login using BackupManager and perform backup, at the add backup destination I receieve an error message like below (similarly to restore),

    TITLE: Locate Database Files - MYSERVER\SQL2005

    ------------------------------

    C:\Program Files\Microsoft SQL Server\MSSQL.1\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.

    Can anyone help?

    TIA

    Together, we can make wonders!
  • anjan.ashok

    SSCrazy

    Points: 2783

    Main issue is here the path problem change the path like D:\backup\ give the file name and try it will run and remove the old path if u r using enterprise manager and try it.

  • AhTu_SQL2k+

    SSC Eights!

    Points: 839

    If I spell out the destination path without clicking on the ... button it will be fine. But, this is not I expect, I thought it can search for the destination path just like "sa".

    Even if I manually set the destination path with a filename, I couldn't able to use the backup for restoring! I want to choose the backup file from a path and when I hit ... button, the following message is shown,

    The Database Engine service could not resolve the specified file location. Either the location does not exist, or the current login account does not have access to it. Verify that the file location exists, and that the login account has permissions on it.

    Thank you.

    Together, we can make wonders!
  • Vidhya Sagar

    SSCrazy Eights

    Points: 8216

    [font="Verdana"]Hi Williams,

    *) Make sure SQLService account has modify privilege to the path mentioned

    *) Try taking backup using T-SQL code

    Try executing the below and check whether its returning any output

    EXEC master.dbo.xp_cmdshell 'dir yourpath here'[/font]

  • AhTu_SQL2k+

    SSC Eights!

    Points: 839

    This is the command as your advice was tried, (I enabled xp_cmdshell at the Server Surface Configuration and then issue this command)

    EXEC master.dbo.xp_cmdshell 'C:\SQL2k5_Data'

    and the result shows 'C:\SQL2k5_Data' is not recognized as an internal or external command, operable program or batch file.'

    TQ

    Together, we can make wonders!
  • Vidhya Sagar

    SSCrazy Eights

    Points: 8216

    [font="Verdana"]You have missed dir infront of the code, try the below

    EXEC master.dbo.xp_cmdshell 'dir C:\SQL2k5_Data'[/font]

  • AhTu_SQL2k+

    SSC Eights!

    Points: 839

    Hi Vidhya, thanks for your fast reply.

    Now I can list out the directory content. I gave up using manual backup from SM at this moment because I am still unable to browse server directory using backup operator login. Instead, I use T-SQL to perform backup/restore.

    Also, can I ask you one more question regarding backup security? Which I posted yesterday at

    http://www.sqlservercentral.com/Forums/FindPost526416.aspx

    Thank you very much.

    Together, we can make wonders!
  • thara.jacob

    SSC Journeyman

    Points: 92

    Hi All,

    I have a problem. I have 2 sql servers running in two seperate locations. i created the backup from the first one and try to restore it in the second one. So i use Microsoft SQL Server Management Studio Express and go to restore option where i need to mention the backup file to be restored. In "Specify Backup" dialogue box When i click on "add" button i get the following error.

    In my local system there is no "E" drive. So it looks like the settings of the server for restoration. How do i change these settings? Even if i type in the path and filename it doesnt accept. Any help?

    =======================================================

    E:\Program Files\Microsoft SQL Server\MSSQL.1\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.

    ========================================================

    Thanks in advance

    Thara

  • Joy Smith San

    SSC-Insane

    Points: 24877

    thara.jacob (8/26/2009)


    Hi All,

    I have a problem. I have 2 sql servers running in two seperate locations. i created the backup from the first one and try to restore it in the second one. So i use Microsoft SQL Server Management Studio Express and go to restore option where i need to mention the backup file to be restored. In "Specify Backup" dialogue box When i click on "add" button i get the following error.

    In my local system there is no "E" drive. So it looks like the settings of the server for restoration. How do i change these settings? Even if i type in the path and filename it doesnt accept. Any help?

    =======================================================

    E:\Program Files\Microsoft SQL Server\MSSQL.1\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.

    ========================================================

    Thanks in advance

    Thara

    Thara,

    First of all, since it's a different question better you post it sperately.

    Well, It's better to copy the backup file in the server where you want to restore as it will be fast.

    If at all you want to restore from network path you can type the network path in the 'Selected Path' textbox. e.g "\\etworkPCName\folderName"

  • thara.jacob

    SSC Journeyman

    Points: 92

    Hi San,

    Thanks for the reply. I have restored it using query/script instead of file. I think the db had a different owner, different from the one logged in and that was the issue.

    Thara

  • Joy Smith San

    SSC-Insane

    Points: 24877

    Fine. Very gud.

    Mostly I do everything using script only than SSMS.

    Santhosh Nair.

  • PatM

    Newbie

    Points: 1

    I had this problem on a VPS with SQL 2014 Express. There was not enough security permission to a do a restore from the default backup directory: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup (although backups worked fine)

    The error was: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\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.

    Actually, you can follow these steps to change the authority:Log in to SSMS using Windows Authentication Navigate to Security->Logins Either double Click, or Right Click and select Properties on the user you're interested in . In the "Select a page" box, select "Server Roles" Make sure that the "sysadmin" role is selected Press Ok to save the change and close the dialog Exit SSMS Reopen SSMS and login as the User you wanted, and you should now have sufficient Privileges

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply