SQL Server Restore Error - Access is Denied

Question

I created a database on my local machine and then did a backup called tables.bak of table DataLabTables.

I moved that backup to a remote machine without that table and tried to do a restore but get the following error:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DataLabTables.mdf'.

How do I fix my rights, if that is the problem?

Answer 1

I have just had this issue with SQL Server 2012.

It turns out all I had to do was tick the box marked 'Relocate all files to folder' on the 'Files' section:

(Click to see image full size)

This of course assumes you have the correct version of SQL Server installed.

Answer 2

From the error message, it says there's an error when validating the target (c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DataLabTables.mdf) of your restore operation.

That sounds like:

a) that file already exists (because you've already restored it previously) and is in use by SQL Server

or

b) that directory doesn't exist at all

In your question, you mentioned you created a backup for that table - that's not how SQL Server backups work. Those backups are always the whole database (or at least one or several filegroups from that database).

My hunch is: you've already restored that database previously, and now, upon a second restore, you didn't check the checkbox "Overwrite existing database" in your restore wizard - thus the existing file cannot be overwritten and the restore fails.

The user that's running the restore on your remote server obviously doesn't have access to that directory on the remote server.

C:\program files\.... is a protected directory - normal (non-admin) users don't have access to this directory (and its subdirectories).

Easiest solution: try putting your BAK file somewhere else (e.g. C:\temp) and restore it from there

Answer 3

I was having the same problem. It turned out that my SQL Server and SQL Server Agent services logon as were running under the Network Services account which didn't have write access to perform the restore of the back up.

I changed both of these services to logon on as Local System Account and this fixed the problem.

Answer 4

Recently I faced this issue with SQL 2008 R2 and the below solution worked for me:

1) Create a new database with the same name as the one you are trying to restore 2) While restoring, use the same name you used above and in the options, click the overwrite option

You might give the above a shot if the other solutions don't work.

Answer 5

The backup creator had MSSql version 10 installed, so when he took the backup it also stores the original file path (to be able to restore it in same location), but I had version 11, so it could not find the destination directory.

So I changed the output file directory to C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\, and it was able to restore the database successfully.

Source

Answer 6

I had a similar problem. I tried to restore a 2005 .bak file, and i received exactly the same error. I selected the overwrite option as well to no avail.

my solution was to grant the SQL user access to the directory in question, by going to the folder and editing the access rights through the property screen.

Answer 7

lost a couple of hours to this problem too. got it going though:

"access denied" in my case really did mean "access denied". mssqlstudio's user account on my windows device did NOT have full control of the folder specified in the error message. i gave it full control. access was no longer denied and the restore succeeded.

why was the folder locked up for studio ? who knows ? i got enough questions to deal with as it is without trying to answer more.

Answer 8

I had this issue, I logged in as administrator and it fixed the issue.

Answer 9

Another scenario could be the existence of multiple database paths. First, make note of the path where new databases are currently being stored. So if you create a new empty database and then do Tasks/Restore, make sure that the path the restore is trying to use is the same directory that the empty database was created in. Even if the restore path is legal, you will still get the access denied error if it is not the current path you are working with. Very easy to spot when the path is not legal, much harder to spot when the path is legal, but not the current path.

Answer 10

Sorry because I cannot comment...

I had the same problem. In my case the problem was related to trying to restore in an old sql server folder (that existed on the server). This is due to old sql server backup (i.e. SQL Server 2012 Backup) restored in a new sql server (SQL Server 2014). The real issue is not too different from @marc_s answer. Anyway, I changed only the target folder to the new SQL Server DATA folder.

Answer 11

This may not be the best solution, but I was trying to do the restore at SQL Server 2005, but I changed to SQL Server 2008 and it worked.

Answer 12

Got problem like this. Error caused by enabled compression on SQL Server folders.

Answer 13

Frnds... I had the same issue while restroring database and tried every solution but could nt get resolved. Then i tried to re install SQL 2005 and the problem solved. Actully last time i forgot to check on customize option while instlling SQL.. It comes two times while installing and i checkd it for ones only..

Answer 14

In my case - I had to double check the Backup path of the database from where I was restoring. I had previously restored it from a different path when I did it the first time. I fixed the Backup path to use the backup path I used the first time and it worked!

Answer 15

I ended up making new folders for Data and Logs and it worked properly, must have been a folder/file permission issue.

Answer 16

This also happens if the paths are correct, but the service account is not the owner of the data files (yet it still has enough rights for read/write access). This can occur if the permissions for the files were reset to match the permissions of the folder (of course, while the service was stopped).

The easiest solution in this case is to detach each database and attach it again (because when attaching the owner is changed to be the service account).

Answer 17

Try this:

In the Restore DB wizard window, go to Files tab, Uncheck "Relocate All files to folder" check box then change the restore destination from C: to some other drive. Then proceed with the regular restore process. It will get restored successfully.

Answer 18

I had the same problem but I used sql server 2008 r2, you must check in options and verify the paths where sql going to save the files .mdf and .ldf you must select the path of your sql server installation. I solved my problem with this, I hope it helps you.

Answer 19

Then try moving it to a sub folder under the C:, but verify that the user has full rights on the folder your use.