Problem restoring a database to a SQL Server 2012 LocalDB Instance

I encountered a bit of a weird one today, which took some creative thinking to sort out, involving restoring a database into a SQL Server 2012 LocalDB. I am still not entirely sure what the root cause of the problem really is, but I'm sharing my solution just in case it proves useful to anybody else.

Background

My company produces a solution based on SQL Server 2008 Express R2 which is used by 40-50 different customers. As a convenience to some of our customers who have remote/occasionally connected users, every night we upload a backup of their database to a secure site which users can then download the following morning. On the user's machines we pre-install SQL Server 2012 LocalDB and use some batch files to help automate the retrieval of the database and the restore of the .bak file into the LocalDB instance.

It's pretty lo-tech, but surprisingly robust - internet outages notwithstanding!

The Problem

A couple of days ago I was asked by one of our larger customers to provide the same functionality for their users. No problem, I thought, and set about getting their database to upload on a nightly basis. After monitoring this for a couple of days and proving it worked ok, today I set about attempting to set up a computer to download and restore the database.

Everything seemed to be going well, but for some reason the database absolutely refused to restore to the LocalDB instance, instead producing an error:

The logical database file 'db_name_log' cannot be found. Specify the full path for the file.

Very odd, especially given that I could see the log file had been moved by my script to exactly where it should be!

The Solution

Part 1 - The Sanity Check!

If nothing else, years of working in IT has taught me one valuable lesson - check, check and check again! In the case of my scripts, this meant double and triple checking I had things spelled correctly, paths set correctly, the order of execution set correctly etc. etc. After about 40 minutes of this I was confident everything was looking good.

Part 2 - Do you trust your colleagues?

The answer to which, of course, is usually: No! We have a preferred implementation procedure, part of which specifies how the logical and physical names in our databases are related to each other. Whoever had performed this implementation, however, had obviously thrown those away as a quick call to:

RESTORE FILELISTONLY FROM DISK=N'path\to\database.bak'

clearly showed that the logical file names were not named correctly. I adjusted my script to account for this, but still the database would not restore!

Part 3 - Creative Thinking Under Pressure...

Anybody who has worked in support in one capacity or another knows the pressure of dealing with customer expectations: They pay for your help and they expect results - and quickly! It's at times like these you start Googling like mad to see if anyone else has had a similar issue. On the entire internet I found precisely three posts that matched my problem. There were suggestions of things to try - which I did, but had no luck with - but no definitive answers.

I then had a flash of inspiration. Given that I could see that the MDF and LDF files were extracted exactly where I wanted them to be, perhaps I could try attaching them to the database by using a combination of SQLLocalDb.exe and SQLCMD.exe? Here's what I came up with...

1) Use SQLLocalDB to create and start a v11.0 instance.

2) Use SQLCMD to restore the database - essential in order to get the MDF and LDF files extracted.

3) Use SQLLocalDB to stop and then delete the v11.0 instance.

4) Use SQLLocalDB to recreate and start a v11.0 instance.

5) Finally, use SQLCMD to RESTORE WITH ATTACH the MDF and LDF files.

Success!

Conclusion
Now my solution is a bit Heath-Robinson and may well come back to bite me in the end, but for the time-being it's keeping people happy enough.

I am still not sure what the actual problem here was, given that I've never had to do this at any other company that wants to have remote copies of the database. I later learned that this customer uses a full version of SQL Server 2008 (not R2) - thanks for that, implementation team! - and that it might be due to the fact that when restoring a database SQL Server attempts to upgrade earlier versions when restoring and this might not be available for all previous versions in LocalDB. That's just a guess, though.

I hope this proves to be useful to somebody at some point - particularly if you've arrived here after some frantic Googling!