SQL Server 还原错误 - 访问被拒绝
我在本地计算机上创建了一个数据库，然后对 DataLabTables 表进行了名为 tables.bak 的备份。
我将该备份移动到没有该表的远程计算机并尝试进行恢复，但出现以下错误：
System.Data.SqlClient.SqlError：操作系统返回 尝试时出现错误“5（访问被拒绝。）” 'C:\Program 上的 'RestoreContainer::ValidateTargetForCreation' 文件\Microsoft SQL Server\MSSQL.1\MSSQL\DataLabTables.mdf'。
如果这是问题所在，我该如何解决我的权利？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（20）
我刚刚在 SQL Server 2012 中遇到了这个问题。
事实证明，我所要做的就是勾选“文件”部分上标记为“将所有文件重新定位到文件夹”的框：
（点击查看图像满的大小）
这当然假设您安装了正确版本的 SQL Server。
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.
从错误消息中可以看出，验证以下目标的目标 (
c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DataLabTables.mdf) 时出现错误您的恢复操作。这听起来像：
a）该文件已经存在（因为您之前已经恢复过它）并且正在被 SQL Server 使用
，或者
b）该目录根本不存在
在您的问题中，您提到您为该目录创建了备份表 - 这不是 SQL Server 备份的工作方式。这些备份始终是整个数据库（或至少该数据库中的一个或多个文件组）。
我的预感是：您之前已经恢复了该数据库，现在，在第二次恢复时，您没有选中恢复向导中的“覆盖现有数据库”复选框 - 因此现有文件无法被覆盖，恢复失败。
<罢工>
在远程服务器上运行还原的用户显然无权访问远程服务器上的该目录。
<罢工>
C:\program files\....是一个受保护的目录 - 普通（非管理员）用户无权访问此目录（及其子目录）。<罢工>
最简单的解决方案：尝试将 BAK 文件放在其他位置（例如
C:\temp）并从那里恢复它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我也遇到了同样的问题。事实证明，我的
SQL Server和SQL Server Agent服务logon as正在Network Services帐户下运行，该帐户没有执行备份恢复的写入权限。我将这两个服务更改为以
本地系统帐户登录，这解决了问题。I was having the same problem. It turned out that my
SQL ServerandSQL Server Agentserviceslogon aswere running under theNetwork Servicesaccount 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 Accountand this fixed the problem.最近，我在使用 SQL 2008 R2 时遇到了这个问题，以下解决方案对我有用：
1）创建一个与您尝试恢复的数据库同名的新数据库
2）恢复时，使用上面使用的相同名称，然后在选项中单击覆盖选项
如果其他解决方案不起作用，您可以尝试上面的方法。
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.
来源< /a>
Source
我有类似的问题。我尝试恢复 2005 .bak 文件，但收到了完全相同的错误。我也选择了覆盖选项，但无济于事。
我的解决方案是通过转到文件夹并通过属性屏幕编辑访问权限来授予 SQL 用户对相关目录的访问权限。
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.
这个问题也浪费了几个小时。不过，继续下去：
在我的例子中，“访问被拒绝”确实意味着“访问被拒绝”。我的 Windows 设备上的 mssqlstudio 用户帐户无法完全控制错误消息中指定的文件夹。我给了它完全的控制权。访问不再被拒绝并且恢复成功。
为什么工作室的文件夹被锁定？谁知道？我有足够的问题要处理，而无需尝试回答更多。
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.
我遇到了这个问题，我以管理员身份登录并解决了这个问题。
I had this issue, I logged in as administrator and it fixed the issue.
另一种情况可能是存在多个数据库路径。首先，记下当前存储新数据库的路径。因此，如果您创建一个新的空数据库，然后执行
Tasks/Restore，请确保还原尝试使用的路径与创建空数据库的目录相同。即使还原路径是合法的，如果它不是您当前使用的路径，您仍然会收到访问被拒绝的错误。当路径不合法时很容易发现，当路径合法但不是当前路径时则更难发现。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.抱歉，因为我无法发表评论...
我也遇到了同样的问题。就我而言，问题与尝试在旧的 sql server 文件夹（存在于服务器上）中恢复有关。这是由于旧的 sql server 备份（即 SQL Server 2012 备份）在新的 sql server（SQL Server 2014）中恢复所致。真正的问题与@marc_s 的答案并没有太大不同。不管怎样，我只将目标文件夹更改为新的 SQL Server DATA 文件夹。
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.
这可能不是最好的解决方案，但我试图在 SQL Server 2005 上进行恢复，但我更改为 SQL Server 2008 并且它有效。
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.
遇到这样的问题。由于对 SQL Server 文件夹启用压缩而导致的错误。
Got problem like this. Error caused by enabled compression on SQL Server folders.
Frnds...我在恢复数据库时遇到了同样的问题，并尝试了所有解决方案，但无法解决。然后我尝试重新安装SQL 2005，问题解决了。事实上，上次我在安装 SQL 时忘记检查自定义选项。安装时它出现了两次，我只检查了一次。
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..
就我而言 - 我必须仔细检查要恢复的数据库的备份路径。当我第一次这样做时，我之前已经从不同的路径恢复了它。我修复了备份路径以使用我第一次使用的备份路径，它成功了！
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!
我最终为数据和日志创建了新文件夹，它工作正常，一定是文件夹/文件权限问题。
I ended up making new folders for Data and Logs and it worked properly, must have been a folder/file permission issue.
如果路径正确，但服务帐户不是数据文件的所有者（但它仍然具有足够的读/写访问权限），也会发生这种情况。如果将文件的权限重置为与文件夹的权限匹配（当然，在服务停止时），则可能会发生这种情况。
在这种情况下，最简单的解决方案是分离每个数据库并再次附加它（因为附加时所有者更改为服务帐户）。
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).
我在同一台机器上有两个 SQL Server 实例。
两者都指向相同的“数据文件夹”
如果通过恢复备份创建的 .mdf 文件的文件名与现有文件相同，则它不会给出有用的错误消息就像“具有此名称的文件已存在”。
相反，它给出了过于笼统的“操作系统返回了错误'5（访问被拒绝。）'”
因此，此特定情况的解决方案是：
I had two instances of SQL Server on the same machine.
Both were pointed at the same "Data File Folder"
If the file name of the .mdf file that would be created by restoring the backup is the same as an existing file it does not give a helpful error message like "A file with this name already exists".
It instead gives the overly general "The operating system returned the error '5(Access is denied.)'"
So the solution for this specific case is to:
然后尝试将其移动到 C: 下的子文件夹，但请验证用户对您使用的文件夹具有完全权限。
Then try moving it to a sub folder under the C:, but verify that the user has full rights on the folder your use.
试试这个：
在“还原数据库”向导窗口中，转到“文件”选项卡，取消选中“将所有文件重新定位到文件夹”复选框，然后将还原目标从 C: 更改为其他驱动器。然后继续常规恢复过程。它将成功恢复。
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.
我遇到了同样的问题，但我使用的是 sql server 2008 r2，您必须检查选项并验证 sql 将保存文件 .mdf 和 .ldf 的路径，您必须选择 sql server 安装的路径。我用这个解决了我的问题，希望对你有帮助。
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.