mssql '5 (Access is denied.)' error during restoring database

user_272757 0
Viewed 133735

I want to restore a database from a file (Tasks → Restore → Database; after I select from device and select file) via SQL Server Management Studio.

After that, I get this error:

The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\XXXXXX.mdf'. Msg 3156, Level 16, State 8, Server XXXX, Line 2

How do I fix this problem? Is it a security error?

ssms sql-server
17 Answers

I just ran into this same problem but had a different fix. Essentially I had both SQL Server and SQL Server Express installed on my computer. This wouldn't work when I attempted to restore to SQL Express, but worked correctly when I restored it to SQL Server.

user_12741310

A good solution that can work is go to files > and check the reallocate all files

Files relocate

Admin1

There are several causes for this error, I got this error because I checked "Reallocate all files to folder" in the Files tab of Restore Database window but the default path did not exist on my local machine. I had the ldf/mdf files in another folder, once I changed that I was able to restore.

user_36262050

enter image description hereThe operating system returned the error '5(access denied.)' when restoring database in sql server can be solved by enabling the Relocate all files to folder in the Files options as follows:

user_63334070

this happened to me earlier today, i was a member of the local server's admin group and have unimpeded access, or i thought so. I also ticked the "replace" option, even though there is no such DB in the instance.

Found out that there used to be DB of the same name there, and the MDF and LDF files are still physically located at the data and log folders of the server, but the actual metadata is missing in the sys.databases. the service account of SQL server also can't ovewrwrite the existing files. Found out also that the files' owner is "unknown", i had to change ownership, to the 2 files above so that it is now owned by the local server's admin group, then renamed it.

Then finally, it worked.

user_14650730

The account does not have access to the location for backup file. Take the following steps to access the SQL Server Configuration Manager via Computer Manager easily

  1. Click the Windows key + R to open the Run window.
  2. Type compmgmt.msc in the Open: box.
  3. Click OK.
  4. Expand Services and Applications.
  5. Expand SQL Server Configuration Manager.
  6. Change User Account in Log On As tab .

Now you can Restore Data Base easily

user_65708600

The fix for me was to go into Options when trying to Restore the database and change the path to the new path. Here is the screenshot

user_125546650

I encountered the same problem, but my setup is a bit different.

  • I run my database in a linux docker container
  • sqlserver management tool in Windows.

What I did was:

sudo docker exec -u root -it sqlserver /bin/bash

This enters the docker container as a root user.

Then:

chmod 777 /path/to/file.bak

777 gives read, write & execute permissions to the file for any group, user

user_152771550
Related