SQL Server Restore Error - Access is Denied

问题:

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?

回答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.

回答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

回答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.

回答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.

回答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

回答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.

回答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.

回答8:

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

回答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.

回答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.

回答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.

回答12:

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

回答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..

回答14:

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.

回答15:

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.

回答16:

Go to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ and click allow access when a new window appears

回答17:

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