We got a new server recently and one of my colleaguesran into an errorwhen restoring a database. The error was a quite generic (reformatted below for readability):

Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf" failed with the operating system error 5(Access is denied.). Msg 3156, Level 16, State 3, Line 1 File 'AdventureWorks2014_Data' cannot be restored to 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

My immediate reaction was to review therestore script.

USE [master]; GO RESTORE DATABASE [AdventureWorks2014] FROM DISK = 'C:\Program Files\Microsoft SQL Server\mssql13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak' WITH MOVE 'AdventureWorks2014_Data' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf', MOVE 'AdventureWorks2014_Log' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Log.ldf'; GO

All looked well,I subsequently moved to the environmental aspect of troubleshooting.It was a new serverand we had just created the target folders to which the database was to be restored.

We attempted to restore to the default database locations configuredduring SQL Server installation and the restore worked. So, onething that became clear: the SQL Server service did not have appropriate security rights on the destination folder .

The Solution

Once we determined that it was the security rights on the destination folder,the onlything remaining was to grant therights. Here’s how we do it.

Cross-check the userset as the service user for Microsoft SQL Server database engine (use the SQL Server Configuration Manager for interacting with the SQL Server service here’swhy). Under Folder properties, ensure that this user has full security rights (or at least equivalent to the rights assigned on the default database folders specified at the time of installation)

Here’s are detailed screenshots showing the above process.


#0405   SQL Server   Msg 5133   Backup/Restore Errors   Directory lookup for ...

Identifying the user running the SQL Server Database Engine service


#0405   SQL Server   Msg 5133   Backup/Restore Errors   Directory lookup for ...

Navigating into file system folder security options to grant access to the SQL Server service


#0405   SQL Server   Msg 5133   Backup/Restore Errors   Directory lookup for ...

Choosing the appropriate account running the SQL Server service


#0405   SQL Server   Msg 5133   Backup/Restore Errors   Directory lookup for ...

Applying appropriate rights to folder

By the way:If you encounter similar issues in accessing your backup files, the root cause and solution are the same. Checkthe permissions on the folders housing your backups and you should see that the database engine does not have the necessary security rights.

Further Reading

Maintaining permissions on data folders and appropriate registry entries is something that is handled by the SQL Server Configuration Manager when you change the service account under which the database engine is running. If you use services.msc (the old way), this is not done and your SQL Server may stop working.

Changing SQL Server Service Account or Password Avoid restarting SQL Server [Blog Link] Blog Post #0344 SQL Server Missing Configuration Manager on windows 8 [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerRESTWindows
分页:12
转载请注明
本文标题:#0405 SQL Server Msg 5133 Backup/Restore Errors Directory lookup for ...
本站链接:http://www.codesec.net/view/558304.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(37)