Restore a SQL Server Backup to LocalDb

I'm experimenting with taking a 3-tier client/server/database application and creating a lightweight client application that uses an embedded database. We use stored procedures and our server application uses SQL Server, so SQL Express LocalDB has potential. The only problem I ran into was restoring a backup taken from SQL Server to LocalDB and it appears that I am not the only one that had problems.

In the end though, it was pretty easy, it just required a few steps that most DB admins are already familiar with, but developers may stumble on. Here are the developer-centric instructions using Visual Studio.

Restoring the DB Backup

In Visual Studio, open SQL Server Object Explorer (Ctrl+\, Ctrl+S) or under the View menu. Open the SQL Server node and you will likely have several LocalDB instances. If not, click the Add SQL Server button and from the dropdown, select (localdb)\MSSQLLocalDB.

Open the node for the version of LocalDB you want to use and expand Databases | System Databases. Right click on master and select New Query...

New Query

Execute the following query using the path to your database backup.

RESTORE FILELISTONLY
FROM DISK = 'D:\tmp\ivaraapplicationtesting75.bak'

In the results, take note of the LogicalName.

LogicalName

Modify your query to do the restore, using your database name, backup and the LogicalName from the previous query. You can restore the database to any existing path, in this case, I am restoring to my home directory.

RESTORE DATABASE IvaraApplicationTesting
FROM DISK = 'D:\tmp\ivaraapplicationtesting75.bak'

WITH MOVE 'ivara_data' TO 'C:\Users\rob.prouse\IvaraApplicationTesting.mdf',
MOVE 'ivara_log' TO 'C:\Users\rob.prouse\IvaraApplicationTesting.ldf',
REPLACE;

First time I did this, I thought it didn't work because it happened so quickly and because the database did not show up in SQL Server Object Explorer, but all you need to do is click refresh.

Results

If you expand your new database, you will see that all of your tables are there and you can execute queries against it.