I recently ran into a problem trying to restore a SQL Server 2005 database to a Windows XP machine. The database back up was created on a Windows Vista machine and I thought that the different OS versions was the culprit. However, it turned out to be related to SQL server instances and folder paths. Here is full error I received when I attempted 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 posts stating that it was a problem with the privileges of the user account that my SQL Server Express service was running as. I hadn't changed the account it was running as and I had restored other databases in the past, but I checked the SQL Server Configuration Manager anyway. As I suspected the service was still running as the default account (Network Service), so that wasn't it. Read More...