LocalDb cannot restore a backup whose original data and log files are in different folders. - by tyrius

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


15
2
Sign in
to vote
ID 789845 Comments
Status Closed Workarounds
Type Bug Repros 12
Opened 6/10/2013 2:05:08 PM
Access Restriction Public

Description

When attempting to restore a database backup on a LocalDb instance that was created on SQL Server 2012 the restore operation fails with the error:
  The logical database file '<logical log file>' cannot be found. Specify the full path for the file.

After some investigation I discovered the problem is because the original data and log files were located in different folders (in our case the data files are stored on F:\MSSQL11.MSSQLSERVER\MSSQL\Data and the log files are stored on G:\MSSQL11.MSSQLSERVER\MSSQL\Log.
Sign in to post a comment.
Posted by pnwpablo on 1/24/2017 at 3:17 PM
Every once in a while I run into one of these situations where Microsoft is shipping a product with a MAJOR glaring hole, and treating it as if it's some edge case that nobody is interested in. Not being able to restore production databases into a local SQL environment is a complete show-stopper for local development. This is like Toyota designing a car without a windshield and then saying "oh wow that's surprising!". I mean seriously, does anyone on the SQL team actually...you know...develop applications? Did the SQL team go bowling during the "Restore backup to local instance" sprint? Are cats and dogs living together? What is this world coming to?
Posted by RpDeveloper on 10/28/2016 at 12:51 AM
I'm using LocalDb 2014. When trying to restore a backup from SQL Server the restore operation seems to work with SSMS in case the restore is carried out to the LocalDB database default location which can be set in "Database Settings" at the server level.

When I try to restore to a different location, e.g. by using the "restore all files to folder" option in the SSMS restore "Files" dialogue, the restore as such works, too and you can work with the database.

But, the problem is that after a restart of the "sqlservr.exe" process the restored database goes into "recovery pending" mode.

When I try to bring it online with "alter database TestDb set online", this fails with:

Msg 1853, Level 16, State 1, Line 1
The logical database file 'TestDb_Log' cannot be found. Specify the full path for the file.
Msg 5181, Level 16, State 5, Line 1
Could not restart database "TestDb". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

I can get around this when I restore to the default location set at the server level, create a backup again in LocalDB and then restore this file to the path I wanted to actually restore to.

This is very inconvenient. Can we get it working the direct way?
Posted by spaghettidba on 1/26/2016 at 7:17 AM
It is not fixed. I can confirm that it is still broken in SQL 2012 SP3 (11.0.6020.0).. Can you share with us the exact build where this has been fixed?
Posted by regisbsbittencourt on 9/17/2015 at 12:06 PM
RESTORE FILELISTONLY
--RESTORE DATABASE test
FROM DISK = 'C:\Users\Regis\Downloads\hosher_mac\hosher_mac.bak'

--WITH MOVE 'pa_1000ingressos.Data' TO 'C:\Users\RegisBenedito\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LocalSuite\pa_1000ingressos.mdf',
--MOVE 'pa_1000ingressos.Log' TO 'C:\Users\RegisBenedito\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LocalSuite\pa_1000ingressos.ldf',
--REPLACE;

them use the logical names to restore. it works
Posted by Chris Shouts (SafeNet Consulting) on 7/6/2015 at 8:54 AM
This bug is definitely not fixed.
Posted by Ryan _ FEI on 3/21/2014 at 6:38 AM
We are also not too happy this is marked as fixed. Investigating the workaround(s) but they sound like a hassle (although appreciated). Thank you.
Posted by diegohb on 2/10/2014 at 2:17 PM
any workarounds or update on this issue?
Posted by tyrius on 11/4/2013 at 8:45 AM
I guess it depends on how you define "fixed." Microsoft said this is a known design limitation that was not clearly documented, so their "fix" was to update the documentation.
Posted by TriSys Business Software on 11/2/2013 at 12:29 PM
How the hell is this marked as FIXED?
Posted by TriSys Business Software on 11/2/2013 at 12:28 PM
I agree that this is a big problem and prevents us testing localDB as we cannot restore our database.
Please fix otherwise localDB will not be successful.
Posted by Microsoft on 7/2/2013 at 2:21 PM
Hi Tyrius
Thanks for the feedback on LocalDB Restore issue. We will investigate this further to find the root cause of the issue and update back

SQL Server