Attempting to restore SQL Server 2005 database to a SQL Server 2008 R2 instance gives reason: 15105 error

Question

I have a SQL Server 2005 database I am attempting to restore a SQL Server 2008 R2 SP2 machine using this statement:

RESTORE DATABASE [Db] 
FROM  DISK = N'C:\temp\sql\DB.bak' WITH  FILE = 1,  
MOVE N'DB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.mdf',  
MOVE N'DB_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.ldf',  
REPLACE,  STATS = 10
GO

But It keeps giving this error.

Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(failed to retrieve text for this error. Reason: 15105)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.mdf'.
Msg 3156, Level 16, State 5, Line 1
File 'Db' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(failed to retrieve text for this error. Reason: 15105)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.ldf'.
Msg 3156, Level 16, State 5, Line 1
File 'Db_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I am logged into SQL Server as SA. I have Admin permissions on the system.

I have also tried to remove the restore location to another path (that has full permission) with the same error.

Answer 1

"MSSQL.1" is not the directory for a SQL Server 2008 R2 instance.

You should verify the directory you are trying to place the database in. The default data path for a normal installation is: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\Data".

Edit

If this is indeed the path of the databases for your SQL Server 2008 R2 instance then you can verify this by running the following query:

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = 1

You should get results similar to this, highlighted the path for my instance:

You can also verify this path through the restore prompt in SSMS, clicking on the "files": However also noting that the error message operating system returned the error '5, the 5 normally indicates permissions issue with the service account running the database engine. If the full message was provided you would see something like

The operating system returned the error '5(Access is denied)' while attempting 'RestoreContainer::ValidateTargetForCreation'

Answer 2

So it appears my SQL Server installation was mucked up. Running a repair on it produced the following output.

Setup has detected a non canonical discretionary access control list (DACL) on directory MSAS10_50.MSSQLSERVER.

Which is not the folder I was restoring to, but I assume something temporary was needing to be written.

Resetting the ACL on the folder corrected the restore issue. For good measure I reinstalled SQL Server as I feel uncertain as to the stability of the install.

After this process the original restore database command for the SQL Server 2005 database is now working successfully.

Answer 3

Try upgrading to SQL Server 2008 R2 SP2, you can find the patches here:

http://support.microsoft.com/kb/2527041

Was your backup created in SQL Server 2005 and you are trying to restore it to SQL Server 2008 R2 (without SP1/SP2)?

In some cases I have noticed that if you take a backup from SQL Server 2005 and try to restore it into SQL Server 2008 it will fail if you don't have SP1/SP2 installed (though I'm not sure why)...