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

Question

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?

Answer 1

The account that sql server is running under does not have access to the location where you have the backup file or are trying to restore the database to. You can use SQL Server Configuration Manager to find which account is used to run the SQL Server instance, and then make sure that account has full control over the .BAK file and the folder where the MDF will be restored to.

Answer 2

I recently had this problem. The fix for me was to go to the Files page of the Restore Database dialog and check "Relocate all files to folder".

Answer 3

Well, In my case the solution was quite simple and straight.

I had to change just the value of log On As value.

Steps to Resolve-

1. Open Sql Server Configuration manager
2. Right click on SQL Server (MSSQLSERVER)
3. Go to Properties

4. change log On As value to LocalSystem

Hoping this will help you too :)

Answer 4

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.

Answer 5

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

Answer 6

I tried the above scenario and got the same error 5 (access denied). I did a deep dive and found that the file .bak should have access to the SQL service account. If you are not sure, type services.msc in Start -> Run then check for SQL Service logon account.

Then go to the file, right-click and select Security tab in Properties, then edit to add the new user.

Finally then give full permission to it in order to give full access.

Then from SSMS try to restore the backup.

Answer 7

I was getting the same error while trying to restore SQL 2008 R2 backup db in SQL 2012 DB. I guess the error is due to insufficient permissions to place .mdf and .ldf files in C drive. I tried one simple thing then I succeeded in restoring it successfully.

Try this:

In the Restore DB wizard windows, go to Files tab, change the restore destination from C: to some other drive. Then proceed with the regular restore process. It will definitely get restores successfully!

Hope this helps you too. Cheers :)

Answer 8

I found this, and it worked for me:

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'
GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO

Answer 9

In my case I had to check the box in Overwrite the existing database (WITH REPLACE) under Options tab on Restore Database page.

The reason I was getting this error: because there was already an MDF file present for the database and it was not getting overwritten.

Hope this will help someone.

Answer 10

If you're attaching a database, take a look at the "Databases to attach" grid, and specifically in the Owner column after you've specified your .mdf file. Note the account and give Full Permissions to it for both mdf and ldf files.

Answer 11

I had exactly same problem but my fix was different - my company is encrypting all the files on my machines. After decrypting the file MSSQL did not have any issues to accessing and created the DB. Just right click .bak file -> Properties -> Advanced... -> Encrypt contents to secure data.

Answer 12

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.

Answer 13

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

Answer 14

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

Answer 15

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.