restore could not start database

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

If you are trying to restore db from a server to another server and db is not exist on restore server, you can try method below.

That problem may occur depends on this reason; DB files could be locate on different disk names which is not exist on restore server.

To solve the problem on restore server; create a db and db files which are attached with the db. DB and file names have to be same with db on backup server (to replace db). Then you can try to start restore again with replace mode is true.

Answer 4

If somebody came to this question when looking for answer to failed attempt to restore SQL Server database to localdb (like I did) then it might be localdb limitation.

I used SQL Server Express and was able to do a restore.