SQL Server Restore Error - Access is Denied

Question

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

User · Answer

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).

User · Answer

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

User · Answer

I had this issue  I logged in as administrator and it fixed the issue

User · Answer

The backup creator had MSSql version 10 installed  so when he took the backup it also stores the original file path  to be able to restore it in same location   but I had version 11  so it could not find the destination directory       So I changed the output file directory to C  Program Files Microsoft SQL Server MSSQL11 MSSQLSERVER MSSQL DATA   and it was able to restore the database successfully    Source

User · Answer

I was having the same problem  It turned out that my SQL Server and SQL Server Agent services logon as were running under the Network Services account 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 Account and this fixed the problem

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Got problem like this  Error caused by enabled compression on SQL Server folders

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I ended up making new folders for Data and Logs and it worked properly  must have been a folder file permission issue

User · Answer

Then try moving it to a sub folder under the C   but verify that the user has full rights on the folder your use

User · Answer

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

User · Answer

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

User · Answer

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] SQL Server Restore Error - Access is Denied

Examples related to sql-server

Examples related to sql-server-2005

Examples related to backup

Examples related to restore