contrived example

The Visual Studio integrated tools for SQL Server are getting better all the time. While working solely in VS might not always be optimal, it is very convenient to not have to switch over to SQL Server Management Studio to do simple queries. Although the VS SQL tools may not be full-featured, you might just be able to get by with them and not need to install SSMS at all.

One of the less intuitive things to do is to restore a backup file that was created on another server. In this post I will show you how to easily do this in the absence of a right-click restore shortcut. For this you must run the SQL for RESTORE.

You will need to open a query window, if you don’t know how, go to View -> Server Explorer. When that opens, add a new connection to (localdb)\v11.0. Then right-click the connection and select New Query

The first thing you need to find out are the names of the files in the .bak file. You need to reference these names to do a restore. In your query window, enter this SQL command:

RESTORE FILELISTONLY  FROM DISK = 'C:\pathToYourBackupFile\YourBackupFile.bak';

This will list the contents of the backup without doing a restore. Here is what it looks like with a database that recently had to restore:

Now that you know the logical file names, you can do the restore:

RESTORE DATABASE <NameOfYourDatabase>

FROM DISK = 'C:\pathToYourBackupFile\YourBackupFile.bak'

WITH REPLACE,

MOVE 'NameOfMdfFile' TO c:\PathToWhereYouWantTheDatabseFiles\MdfFileName.mdf',

MOVE 'NameOfLogFile' TO 'c:\PathToWhereYouWantTheDatabaseFiles\LdfFileName.ldf'

In my instance the command looks like this:

Be Careful with the REPLACE option

On your first restore you don’t need to use WITH REPLACE. But if you need to overwrite the datbase in the future this is what you will want to do. As developers, it can be common to restore a database backup multiple times as we test things, but be aware, this will OVERWRITE the database if it exists. This is exactly the behavior I usually want, but carefully consider if this is what you want to do.

This blog post is not DBA training. If you need to manage databases that aren’t just temporary “playgrounds” during development then you will want to use better tools than Visual Studio. But if you just want to get a database restored with the lightweight localdb for development purposes, then I hope this helps.