Today, I tried to restore a MS SQL Server 2008 R2 backup file. From a production server, I made a backup file. I tried to restore that backup on a named instance of SQL Server on a test server (on a physical other machine), to do some tests on the data without disturbing the live environment. The database didn't exist on the restore location.
To get the picture correct: on the test server, I have a default MS SQL Server instance and an extra, named instance. The restore was performed on the named instance!
I got 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\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\pfwork.mdf'. (Microsoft.SqlServer.Smo)Strange enough, I was logged in with full administrator privileges.
I found some possible reasons at http://stackoverflow.com/questions/7031792/sql-server-restore-error-access-is-denied, but that was not the case in my situation.
Having a closer look at the message above opened my eyes. It was trying to create the database file on the data folder of the default instance, instead of the data folder of the named instance. That location would be C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVSSRS\MSSQL\DATA\.
So, when you start a restore from a device file to a (not existing) database, verify the location in the 'Options' page of the 'Restore Database' window. In my case it was pointing to the default SQL Server instance, despite the default file location of the named instance where pointing to the correct place.