SQL Server Backup\Restore - Cannot access the specified path or file

Question

I've recently changed the default BackupDirectory for a SQL Server 2008 R2 instane in the registry from I:\ to K:\ and since then whenever I try to backup or restore using SSMS I'll get an error

Cannot access the specified path or file

when I browse for the path.

It happens to the root and all folders in K:\. If I key in I:\ in the path and browse, there's no such issue. I've replicated the permission on I:\ and later added the service account for SQL and MSSQLSERVER to K:\ with Full Access. I've even tried adding Everyone with Full Access and it still shows the same error. I've googled around and find a lot of similar questions but no working solution for me. I've also tested master.sys.xp_dirtree on K:\ and no error. I've even tried procmon.exe but no indication of what went wrong

K:\ is the newly created pool in our SAN allocated for backups only. The SQL Server is part of a Windows 2008 R2 Failover Cluster. Both I:\ and K:\ is configured as SQL Server's cluster resources.

Fyi, I login to SSMS using a SQL Server account, since my machine is not part of the domain. But it was never an issue with I:\ to begin with.

EDIT: There's no error if I backup/restore with T-SQL, meaning the read/write permission for SQL Server for K:\ is correct.

Honolulu

sqlcoffee.com/Troubleshooting069.htm – Aleta

@NagarajS both I:` and K:` are in the dependency of the SQL – Honolulu

Answer 1

I had this issue but realised that I was logging into the server (in SSMS) using SQL authentication. Once I hoped on the box as a local admin and then logged in with windows authentication to SQL server, I could see the files fine.

Kristofor

Answer 2

I has same issue and found solution on another post from SO. I moved my backup file to other directory and it worked. Solution can be found here.

Allysonalma

Thanks but I need to use a different drive to store the backup files – Honolulu

Answer 3

I tried everything on the web, but none of them successed. Then, I installed some new feature to existing SQL Server (Data Quality and Integration Service) and finally I successed to select backup file from Restore Database menu.

Malvoisie

Answer 4

It Because of no (sysadmin) Privilege for your login account to SSMS; mostly. by this way:

Open Security > Logins > Choose your account "if Windows Administrator, use it"

right click, properties > Server Roles > then check on "sysadmin" or "dbcreator" if this account must have less or specific permissions.

• "Take care" you've to use another account has "sysadmin" privileges to do this step because none allowed to give himself more privileges.

Afterburning

Thanks, I've been using account with sysadmin privileges – Honolulu

Answer 5

I had a similar issue, when new service account has been created for SQL server. To resolve this,Add the new service account to the local Administrator group on the back up server. This can be done by loggin into the server, then computer management->users and groups->Administrator.Double click administor and add the new service account created.

Dehydrogenase

Answer 6

It is possible that the software is having difficulty locating the file on your specific path due to permission issues or file path discrepancies. Putting it in the C: path worked for me.

Infidelity