CODEWITHSUNDEEP
file-permissionssql-server-2008
faulty
faulty

Reputation: 8347

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

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.

Error from SSMS

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 enter image description here

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.

Upvotes: 6

Views: 26827

Answers (6)

Vincent Pelletier
Vincent Pelletier

Reputation: 63

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.

Upvotes: 0

user1528640
user1528640

Reputation: 26

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.

Upvotes: 0

Omer Faruk KAYA
Omer Faruk KAYA

Reputation: 396

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.

Upvotes: 1

ThatDataGuy
ThatDataGuy

Reputation: 2109

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.

Upvotes: 3

Mohamed Abulnasr
Mohamed Abulnasr

Reputation: 597

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.

Upvotes: 0

Samvel Siradeghyan
Samvel Siradeghyan

Reputation: 3583

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.

Upvotes: 2

Related Questions