[sql-server] SQL Server Restore Error - Access is Denied

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?

This question is related to sql-server sql-server-2005 backup restore

The answer is


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


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


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.


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.


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.


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


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.


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


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.


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.


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!


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


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


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.


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.


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


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.


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


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to backup

input file appears to be a text format dump. Please use psql How can I backup a Docker-container with its data-volumes? Backup/Restore a dockerized PostgreSQL database Export MySQL database using PHP only Tar a directory, but don't store full absolute paths in the archive How to extract or unpack an .ab file (Android Backup file) mysqldump with create database line Postgresql 9.2 pg_dump version mismatch How to backup Sql Database Programmatically in C# Opening a SQL Server .bak file (Not restoring!)

Examples related to restore

"No backupset selected to be restored" SQL Server 2012 Opening a SQL Server .bak file (Not restoring!) What does "restore purchases" in In-App purchases mean? SQL Server Restore Error - Access is Denied How to take backup of a single table in a MySQL database? How to restore to a different database in sql server? Restore DB — Error RESTORE HEADERONLY is terminating abnormally. Restore a postgres backup file using the command line? When restoring a backup, how do I disconnect all active connections? Can I restore a single table from a full mysql mysqldump file?