SQL Server 2005 Restore Database Error: The operating system returned the error '5(Access is denied.)'

I was trying to restore some old SQL Server 2005 databases to get some data they contained, when I ran into this 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\test.mdf'. (Microsoft.SqlServer.Smo)

Your error message may look similar, except for the file location on the C drive, or whatever drive you are using.

After some frustration, I figured out the problem. I was actually trying to write my .mdf and .ldf files in a folder where they should not be located. That is why "Access is Denied" and it should be! You should write the files where your other database files are written. (If you need to find where they are, in SQL 2005, set up your Restore Database popup to restore from a currently active database, then go to the Options page on the left, and under "Restore the database files as:" look at "Restore As" which will contain the correct file location.)

In my error case, this location was not where my currently active databases where located, but was set to:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

Your settings may be similar. In my case, this was definitely the wrong location, in fact, the wrong drive!

So to fix my situation, (and hopefully yours), I had to choose a new path and location for my .mdf and .ldf files. This is where the newly restored database files will be stored. (Which should be in the same folder as your other active databases):

1. Starting from the Restore Database popup, look to the left and choose the Options page (in SQL 2005), then look for "Restore the database files as" right in the middle of the page.
2. Just look to the right of the Restore As cell (on the Restore Database Options page), and click on the edit box symbol, which will bring up the Locate Database Files popup. Choose the correct path to the directory where you want the .mdf file to be stored, then enter the correct filename. Do the same for the .ldf file. 

Once I did this for both files, I was able to restore the database successfully. My assumption is that this will be similar for issues with SQL 2008 and 2012, etc.

Hope this helps you...