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)

Leave a Reply

Your email address will not be published. Required fields are marked *

4 comments

  1. Sam High says:

    Does not work at least on windows 8… Made the new reg keys pointing to F:\LocalDB\. Rebooted pulled up Prop Dialog for LocalDB but till showin $UserProfile% folder c:\users\myacct

    Does it only work on C: drive? Dang I am in Administrators and have full ntfs perms to folder in reg keys.

  2. Remco says:

    You are my hero! 🙂 Thanks

  3. Jim says:

    Thanks!

    Helped me to restore a WSUS DB installed on WID (Win2012R2).

    i.e. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSWIN8.SQLWID\MSSQLServer\BackupDirectory

    ^ Did not exist!

  4. Chad says:

    Awesome work. Thank you