Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

"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: enter image description here

You can also verify this path through the restore prompt in SSMS, clicking on the "files": enter image description here 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'

share|improve this answer
    
I agree its not the normal, it was the way it was setup But the folder does indeed exist and other mdf's live there. I have also attempted to restore to an alternative path C:\Data\ (which also exists) and same error. –  Jafin Dec 18 '13 at 6:59
    
So if you right click on the server name in SSMS, what path does it give you for the root directory –  Shawn Melton Dec 18 '13 at 7:03
    
name physical_name master C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf mastlog C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf –  Jafin Dec 18 '13 at 8:13
up vote 1 down vote accepted

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.

share|improve this answer
    
"MSAS10_50" is the directory for Analysis Services. –  Shawn Melton Dec 18 '13 at 9:31
    
And that isn't the directory you were trying to restore to anyway... –  Aaron Bertrand Dec 18 '13 at 12:32
    
Both valid comments, I assume I still don't know root cause, although as mentioned, resetting the folder permissions on that folder allowed for the restore to work. Even though it was not where I was restoring too... –  Jafin Dec 18 '13 at 21:04

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)...

share|improve this answer
    
Thanks Tod, I'll try that and let you know. –  Jafin Dec 18 '13 at 6:38
3  
Why patch it..? –  Shawn Melton Dec 18 '13 at 6:47
    
@Tod, Nope its still giving the original error even after applying the SP2. –  Jafin Dec 18 '13 at 6:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.