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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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-    Open Sql Server Configuration manager Right click on SQL Server  MSSQLSERVER  Go to Properties      change log On As value to LocalSystem     Hoping this will help you too

User · Answer

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 · Answer

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

User · Answer

There are several causes for this error  I got this error because I checked  quot Reallocate all files to folder quot  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 · Answer

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

User · Answer

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   Click the Windows key   R to open the Run window  Type compmgmt msc in the Open  box  Click OK  Expand Services and Applications  Expand SQL Server Configuration Manager  Change User Account in Log On As tab     Now you can Restore Data Base easily

User · Answer

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

User · Answer

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

[sql-server] mssql '5 (Access is denied.)' error during restoring database

Examples related to sql-server

Examples related to ssms