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.

Restore failed for Server 'servername'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: RESTORE could not start database 'ECP'. (Microsoft.SqlServer.Smo)

The database is created, but is not accessible using Object Explorer:

The database ECP is not accessible. (ObjectExplorer)

Thanks for any advice on how I can make the database work.

EDIT: This query

select state_desc from sys.databases where name = 'ECP';

returns RECOVERY_PENDING.

I deleted the database again and had a new try with

restore verifyonly from disk = 'path\file';

This returns

Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "F:\ECPDATA1\ECPDATA1.mdf" is not in a valid directory.
Directory lookup for the file "F:\ECPDATA2\ECPDATA2.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15100).
Directory lookup for the file "F:\ECPDATA3\ECPDATA3.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
Directory lookup for the file "F:\ECPDATA4\ECPDATA4.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
Directory lookup for the file "E:\ECPLOG1\ECPLOG1.ldf" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
The backup set on file 1 is valid.

Since it says, that the backup set is valid, I just have to get the files specified, to make it work, right? But on this server I don't have a drive E: or F:. How can I adjust this in the backup file? Or is it possible (I'm not that familiar with windows-server-2008-r2) to have some kind of symlinks like on linux?

share|improve this question
1  
what drives do you have? have you tried WITH MOVE? –  swasheck Aug 20 '13 at 15:40
add comment

2 Answers

up vote 5 down vote accepted

Your restore is failing because there is no E:\ or F:\ on your machine.

  1. Perform a RESTORE FILELISTONLY FROM DISK = 'path\file'; - this will tell you the logical names of the data and log files.
  2. Construct a RESTORE DATABASE command based on this. For example (assuming the data files are named ECP_Data,ECP_Data_2, etc.):

    RESTORE DATABASE ECO FROM DISK = 'path\file'
      WITH REPLACE, RECOVERY,
      MOVE 'ECP_Data'   TO 'C:\...path to MSSQL\Data\ECP_Data.mdf',
      MOVE 'ECP_Data_2' TO 'C:\...path to MSSQL\Data\ECP_Data2.ndf',
      MOVE 'ECP_Data_3' TO 'C:\...path to MSSQL\Data\ECP_Data3.ndf',
      MOVE 'ECP_Data_4' TO 'C:\...path to MSSQL\Data\ECP_Data4.ndf',
      MOVE 'ECP_Log'    TO 'C:\...path to MSSQL\Data\ECP_Log.ldf';
    

    In addition to correcting the logical file names, replace C:\...path to MSSQL\Data\ with whatever path(s) you do want to use to store the data/log files. You don't need an individual folder per file, but you may still want to separate data and log files onto different disks. Note that it still might fail if whatever drive(s) you choose lack sufficient space to perform the restore.

share|improve this answer
add comment

It seems that you are restoring a database from a server that has an E: and F: drive. You need to MOVE the files to a location that actually exists during RESTORE. This would give a restore command that looks like this (scavenged from the SQL Server BOL):

RESTORE DATABASE MyAdvWorks
  FROM AdventureWorks2008R2_Backup
  WITH RECOVERY,
  MOVE 'AdventureWorks2008R2_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf', 
  MOVE 'AdventureWorks2008R2_Log' TO 'D:\MyLog\MyAdvWorks_Log.ldf'

You can do this from the UI as well, by going to the OPTIONS tab of the restore dialog and typing new paths into the "Restore as" column.

share|improve this answer
add comment

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.