0

I have created a backup of a SQL database in SQL 2014 and want to use this backup to restore it to a different machine that also runs SQL 2014, hence no version issues. However, I keep receiving an error that the system cannot find the .mdf file. Once I have selected the backup file I receive the check mark that the database is ready to be restored but as soon as I click Ok the error appears. Error message below; the provided link to MSFT does not contain any information.

TITLE: Microsoft SQL Server Management Studio Restore of database 'BEWEBAPP' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\BEWEBAPP.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476

Does this means that a backup can only be restored to the machine it was taken from?? Frankly, I do not understand why restoring a database has to be so difficult.

Any solution for this problem are well appreciated.

0

Sql server stores file locations with the restores with the general idea you'd be restoring on a similarly setup machine. But you can override this -- look at the restore UI for the database files and switch those to appropriate locations based on your setup.

  • I followed the instructions provided by MSFT msdn.microsoft.com/en-us/library/ms186390.aspx but to no avail. The directory mentioned in the error "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\BEWEBAPP.mdf" does not even exist on my machine because I do not use SQL express but the regular SQL 2014. Restoring a backup made on my machine goes flawless. I have even copied the bak file to the same location as used for the other backups but to no avail. SQL keeps complaining about the .mdf file. I am clueless about how to solve this. – Filiep Samyn May 29 '15 at 17:04
  • @FiliepSamyn, did you perform step 7 and specify an existing folder to relocate the files to? If not, SQL Server will attempt to restore the files to the same location as the original database. You might try the T-SQL script like the example in the Books Online, which is hand to repeat the operation. – Dan Guzman May 29 '15 at 22:49
  • @Dan, Yes I followed the instructions, including step 7. I have only been able to solve the problem by changing the default name of the db MSFT restores to using another name. In my case the name of the backup was BEWEBAPP.bak hence the db MSFT restores to is BEWEBAPP. I overwrote this to read BEWEBAP et voila, problem solved. After restoring I changed the db back to its original name. Thank you for your input. – Filiep Samyn May 30 '15 at 15:06
0

I have renamed the destination database from the default (the original name) to a new name. Issue solved. For whatever reason the original name is associated with its file locations.

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