SQL Server Restore Error - Access is Denied

Question

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.

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

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.

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.

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