+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: SQL Server Restore Error - Access is Denied (/Thread-SQL-Server-Restore-Error-Access-is-Denied)
SQL Server Restore Error - Access is Denied - synartesisppio - 07-31-2023
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?
RE: SQL Server Restore Error - Access is Denied - Sirheard10 - 07-31-2023
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.
<strike>
The user that's running the restore on your remote server obviously doesn't have access to that directory on the remote server.
</strike>
<strike>
`C:\program files\....` is a protected directory - normal (non-admin) users don't have access to this directory (and its subdirectories).
</strike>
<strike>
Easiest solution: try putting your BAK file somewhere else (e.g. `C:\temp`) and restore it from there
</strike>
RE: SQL Server Restore Error - Access is Denied - rocketers745664 - 07-31-2023
Then try moving it to a sub folder under the C:, but verify that the user has full rights on the folder your use.
RE: SQL Server Restore Error - Access is Denied - civilizes218640 - 07-31-2023
> 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](
[To see links please register here]
)RE: SQL Server Restore Error - Access is Denied - broadcastingwe - 07-31-2023
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.
RE: SQL Server Restore Error - Access is Denied - unremunerative487316 - 07-31-2023
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.
RE: SQL Server Restore Error - Access is Denied - leas - 07-31-2023
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.
RE: SQL Server Restore Error - Access is Denied - karolal - 07-31-2023
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.
RE: SQL Server Restore Error - Access is Denied - board176 - 07-31-2023
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.
RE: SQL Server Restore Error - Access is Denied - motherfucking88408 - 07-31-2023
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.