SQL Server Restore Error - Access is Denied

T

20

235

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?

Thevenot answered 11/8, 2011 at 19:35 Comment(0)

Q

759

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.

Quackenbush answered 17/6, 2013 at 11:24 Comment(23)

Worked for me as well. Can anyone explain why? – Leader 7/8, 2013 at 16:49

Works for me as well. Just upgraded to 2012 and did not know about this. – Hendrickson 4/3, 2014 at 2:18

@atticae In my case I was restoring files from the original instance data dir \MSSQL\DATA so they needed to be reallocated to the new instance dir \MSSQL.SECOND\DATA in order for the second instance to have permission to access it – Vaivode 10/3, 2014 at 21:5

Can you also please share how this can be done via script instead of UI? – Stlouis 1/7, 2015 at 18:34

I had this problem with 2014, same fix. – Guilder 30/9, 2015 at 21:13

This was also the solution for me when backing up from SQL Express and restoring onto full SQL Server – Expectation 7/12, 2015 at 15:6

This worked for me for restoring a database from SQL Server 2012 on our live server into my local SQL 2016 Developer edition. – Intelligibility 2/8, 2016 at 14:48

I had similar issue while restoring a database on sql server express 2014. Your solution worked like a charm :) – Hightail 16/11, 2016 at 18:51

I NEED TO GIVE YOU A HUG. Now seriously, I was about to say no to a client, your answer saved my project. – Anemo 10/1, 2017 at 4:50

Fixes the same problem on SQL Server 2016. – Philender 13/6, 2017 at 7:34

The exact opposite solution worked for me. I had to uncheck that box for it to work! – Furthest 30/8, 2017 at 16:36

Did not work for me. when trying to browse to directory it says i do not have permissions and need to click continue as administrator. I changed the "relocate all files to " another directory and that worked fine. Not sure why I dont have access the data directory as my user any more.. probably a SQL 2017 thing? – Affenpinscher 20/9, 2017 at 15:8

The hoops SQL Server makes you jump through to restore a DB. I've had so many permissions problems etc that it was driving me insane and this was the final piece to the puzzle, thanks! – Professoriate 23/9, 2017 at 23:14

I just ran into this again with SQL Server 2016 Developer Edition (from SQL Server 2012) and I actually had to add the back slash at the end of DATA or it tried to put it in the MSSQL folder instead. – Intelligibility 1/11, 2017 at 21:39

A hour of my life wasted because of a hidden checkbox. Thanks again Microsoft - lol. Up voted. – Irremeable 7/5, 2018 at 14:18

This happens if the destination instance has different name (version). So the program tries to copy to nonexistent folder. This check-mark creates a new folder for you. – Elliellicott 13/2, 2019 at 8:38

Worked taking a full backup from SQL Server 2008 R2 to SQL Express 2017. If you expand the columns shown in the image you can see that it is copying the files to different locations on the server. – Adriene 23/10, 2019 at 15:29

You can see better why this needs to be done if you fully open the "files" tab and compare the Original File Name and the restore as file names. The default is for these to be the same. If you are restoring to a different database it is unlikely the file names and paths will be the same as the original. The check box just makes it easy to change the "restore to" to the file location you want. You could manually do the same thing by typing the full path to the mdf and ldf files in the restore too. I guess this is to make you definitively think about overwriting mdf file. – Microdont 27/11, 2019 at 13:11

@Leader - the reason why is it tries to restore from the original file path that the backup was created from - so if the backup was created on a different machine with a different version of sql server the original path might not exist on your machine that you are restoring to – Stymie 18/2, 2020 at 0:3

this helped me fixed issues in sql server 2017 – Volta 26/11, 2020 at 7:18

the solution worked for: database restore in MSSQL 2019 from MSSQL 2016 – Decimeter 25/4, 2022 at 18:8

Fixes the same problem on SQL Server 2022 Developer edition – Hewet 12/8, 2023 at 1:40

Worked for me with BAK file created in MSSQL 2018 and restored in MSSQL 2022. In 2022 the option "Relocate all files to folder" is in the "General" tab of the pictured dialog. I selected it after locating and loading the BAK file, and before the final "OK", which had previously lead to the failure. – Sowens 6/9, 2023 at 3:45

C

34

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

Charioteer answered 11/8, 2011 at 19:39 Comment(8)

I tried under C:\temp but the error still is the same as above, with the same path as I first mentioned which is weird – Thevenot 11/8, 2011 at 19:48

I right click on Databases in SQL Management Studio, then Tasks -> restore – Thevenot 11/8, 2011 at 19:56

@marc - it actually sounds like he already has an MDF for the database he is backing up – Kioto 11/8, 2011 at 20:0

@JNK: yeah, that's my hunch too - he says he created a backup of that one table - of course you cannot do that - you always back up the entire database (or at least a filegroup) - not a single table per se... – Charioteer 11/8, 2011 at 20:3

@chris: updated my answer with a new possible error cause - can you please check that and report back?? – Charioteer 11/8, 2011 at 20:6

@Charioteer thx, i forgot to edit the options as there is no directory for that file...its not ...MSSQL\DataLabTables.mdf but instead ...MSSQL\Data\DataLabTables.mdf – Thevenot 11/8, 2011 at 20:18

@marc_s: Minor comment on the "and is in use by SQL Server" portion of option A listed above: Turns out a standard RESTORE command fails if the file exists, even if it isn't in use by SQL Server (eg the MDF/LDF files remain in place after a previous detach). I came across this in a custom T-SQL-based log-shipping implementation for a major migration of hundreds of DBs over the last couple of weeks. I'm not sure the error message was "access denied", might have been something less specific. – Masterson 12/8, 2011 at 7:38

I had to manually rename the existing MDF/LDF files before I could restore via a backup - checking 'Overwrite' wasn't sufficient. – Tetraspore 17/2, 2014 at 14:57

H

29

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.

Heterozygous answered 7/12, 2013 at 10:20 Comment(3)

This is not a good idea. It masks the real problem which is the file location you are trying to restore to is not what you intend. – Maintopmast 11/10, 2016 at 14:27

My SQL Server service was running at "NT Service\MSSQLSERVER" adding permissions for this user to the data and log folder worked for me. – Dehnel 13/7, 2017 at 9:31

Fine, this helped to me – Affectation 24/9, 2018 at 10:25

K

10

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.

Khelat answered 14/9, 2013 at 9:24 Comment(0)

I

6

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

Illumination answered 12/11, 2012 at 11:19 Comment(0)

C

6

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.

Clift answered 14/2, 2014 at 9:57 Comment(0)

D

3

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.

Draftee answered 28/10, 2015 at 18:2 Comment(0)

C

1

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

Chaney answered 17/12, 2017 at 13:19 Comment(1)

Worked for me as well for the SSMS v17 – Houck 23/1, 2019 at 21:34

P

0

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.

Pantomime answered 30/9, 2013 at 16:44 Comment(0)

B

0

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.

Baerl answered 14/11, 2014 at 8:1 Comment(0)

V

0

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.

Vitrine answered 11/8, 2015 at 21:17 Comment(0)

M

0

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

Monotint answered 20/8, 2015 at 9:23 Comment(0)

E

0

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

Edmiston answered 23/8, 2015 at 7:37 Comment(0)

K

0

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!

Katar answered 9/7, 2019 at 18:44 Comment(0)

E

0

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

Erythroblast answered 21/10, 2019 at 20:52 Comment(0)

P

0

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

Plasmagel answered 15/5, 2020 at 13:54 Comment(0)

J

0

I had two instances of SQL Server on the same machine. Both were pointed at the same "Data File Folder"

If the file name of the .mdf file that would be created by restoring the backup is the same as an existing file it does not give a helpful error message like "A file with this name already exists".

It instead gives the overly general "The operating system returned the error '5(Access is denied.)'"

So the solution for this specific case is to:

Go to the Files section of the Restore dialogue.
Tick the "Relocate all files to folder" option.
Edit the "Restore As" option for both files.
I just added a suffix to the file name that contained the SQL instance name.
Press OK

Jarlath answered 26/10, 2023 at 11:9 Comment(0)

H

-1

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

Hosfmann answered 11/8, 2011 at 19:46 Comment(0)

B

-1

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.

Beak answered 10/3, 2016 at 8:17 Comment(0)

I

-1

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.

Innuendo answered 22/9, 2017 at 21:8 Comment(0)

Recommended topics

Hot tags