How to restore a database on a Microsoft SQL Server LocalDb instance, based on a *.bak file.


The backup file is located at [C:\Temp\Research.bak]

The folder [C:\Databases] will contain all LocalDb database data (*.mdf) and log (*.ldf) files.


Open Microsoft SQL Server 2012 Management Studio

Connect to the LocalDb instance [(localdb)\V11.0]



Create a empty database with the name [Research]

By default you will get the error:

[Create failed for Database ‘Research’. (Microsoft.SqlServer.Smo)]



Add the following registry keys:





HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer\BackupDirectory

With the values [C:\Databases]



If you don’t add the [BackupDirectory] key, then you will get the error:



Property BackupDirectory is not available for Settings ‘Microsoft.SqlServer.Management.Smo.Settings’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)


Double check

Control the values by right clicking on the [(localdb)\V11.0] instance name in the Microsoft SQL Server Management Studio and click on propeties. In the properties dialog click Database Settings:



Now you are able to create a new database with the name [Research]. The data and log files will be created in [C:\Databases].



Now you are able to restore the database:






Make sure you check the [Overwrite the existing database (WITH REPLACE)] option]:




else you get the error:

Restore of database ‘Research’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

Shifting to express products like Microsoft IIS Express 8 and Microsoft SQL Server 2012 LocalDb for development.

Most express products have some things in common:

– Contain the same features as the full product (except for some limitations like, no more then 4GB memory or no more then 10 users etc.)

– Free of charge

– Lightweight

– User profile specific (multiple users on one machine have independent product configuration and processes)

These products are created mainly for development purposes.



More and more I am shifting to these express products for the O in OTAP and for the build servers, because these products have the following advantages:

– Free of charge.

– No configuration.

– Fast and lightweight installation.

– Performance improvements for DEV en build server machines.

– Build servers can use clean instances to run integration tests.

– Easily to restore to new instance.
When a per user instance is corrupted it’s very easy to restore it.

Multiple users can work on one virtual machine, without interfering with each other.
So each developer can change all the things he or she wants to research some new code, without breaking a shared instance of IIS or SQL Server.


Of course for the T, the A and P in OTAP, I use the full products.