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.

3 comments:

Unknown 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!

Anonymous said...

Roulette is considered to be the most well-liked among reside casino lovers. The video games are typically modified and modernized, and so they can 코인카지노 also be|may additionally be|can be} customized for the purposes of on-line casino brand consciousness. Live Casino video games are a reasonably latest phenomenon on the web casino panorama. Their origins could be traced again to 2003, when the very first product adopting Live Casino technology entered the market. Thanks to state-of-the-art technology, a reside video feed is distributed to gamers from a land-based studio that houses the video games.