I recently encountered a problem while trying to restore a SQL Server 2005 database on a Windows XP machine. The database backup was created on a Windows Vista machine and I thought that different versions of the operating system were the culprit. However, it has been found to be linked to SQL Server instances and folder paths. Here is the full error I received when I tried to restore the database using SQL Server Management Studio:
Restore failed for Server ‘localhost\sqlexpress’. (Microsoft.SqlServer.Express.Smo)
Additional information:
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.2\MSSQL\[my database name].mdf’. (Microsoft.SqlServer.Express.Smo)
I searched the Internet using the error message and found several messages stating that it was a problem with the privileges of the user account under which my SQL Server Express service was running. I hadn’t changed the account it was running under and had restored other databases in the past, but still checked the SQL Server Configuration Manager. Since I suspected that the service was still operating as the default account (network service), this was not the case.
After I couldn’t find a quick fix on the Internet I decided to look around the options in the Restore Database window. It turns out the problem was with the paths under the restore options. The backup was trying to restore the .mdf and .ldf files to the c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\ directory. The instance of SQL Server I was working with was storing all it’s data files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\. Once I changed the paths for the database file and log file to be the same as where my other database files were being stored the restore ran fine with no errors. For additional reference, here is an article that explains the naming of folders for SQL Server 2005 instances: http://weblogs.sqlteam.com/tarad/archive/2006/06/07/10114.aspx
Leave A Comment