Wednesday, 24 October 2012

Restore failed for Server '' (MS SQL Server 2008 R2)

Problem
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!
It failed...
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.
Solution
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.

2 comments:

Golam Kibria said...


Hi There, I just spent a little time reading through your posts, which I found entirely by mistake whilst researching one of my projects. Please continue to write more because it’s unusual that someone has something interesting to say about this. Will be waiting for more!

Rob said...

Hi Golam Kibria, thanks, glad you appreciate it! I will continue. I post only on topics/problems when I had to search too long to find a proper solution for a specific problem, so I can help somebody else to solve his/here same (kind of) problems quicker than I did :)
Happy programming!