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

Input

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]

image

 

Create a empty database with the name [Research]

By default you will get the error:

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

image

 

Add the following registry keys:

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

and

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

and

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

With the values [C:\Databases]

image

 

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

image

 

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:

image

 

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

image

 

Now you are able to restore the database:

 

image

 

image

 

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

 

image

 

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.

 

Advantages

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.