How-to Improve Your SQL Server LocalDb

The Problem

The Microsoft SQL Server LocalDb has become a staple tool for our development teams. A number of years ago they removed the version number from the connection string, which was a great move.

I recently needed to upgrade to 2016 in order to import a .bacpac file from an Azure database. So I installed SQL Server Express 2016 upgrading my SQL instance. However, it didn’t upgrade my LocalDb instance as well. I searched around, but didn’t find many complete answers.

You might also get this error: Internal Error. The internal target platform type sqlAzureV12DatabaseSchemaProvider does not support schema file version ‘3.5’. This problem happens when you do not have the latest SSMS. Sometimes you even need a pre-release version of SSMS to get the .bacpac files to load. Here is a link to the latest SSMS. Thanks to Derek Howard for pointing this one out.

Note that I could have just created a new instance, but I didn’t want to change connection strings in my existing applications. You can also have several instances of LocalDb set up at the same time will with different names.

The Process

There may be better ways, but this worked in my case. This process should work for just about any version of LocalDb, at least as of 2016. The basic process is as follows.

  1. Install the latest version of SQL Server (Full or Express)
  2. Stop LocalDb
  3. Remove the existing LocalDb instance
  4. Add a new LocalDb instance
  5. Start the new instance

The first step is pretty self explanatory, and a web search will take you to the Microsoft download site.

Warning!

The following approach will cause all your existing databases to disconnect from your instance. The instance name will be the same, but you will need to reconnect them if you are not connecting via a filename in your connection string. If you have your application set up to recreate the database, this will likely fail because the default filename for the database already exists, it just isn’t attached to your LocalDb instance.

The default location for databases is: C:\Users[account name]\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances[instance name]

You can just reattach the databases you need with SQL Server Management Studio.

The Commands

These command should be run from a command window (cmd.exe). The location doesn’t seem to matter. Replace MSSQLLocalDB with the name of your instance.

The following command will give you a list of instances.

C:\>sqllocaldb info

These are the commands to stop, delete, create, and start your instance. This will cause all your databases to detach, see note above.

C:\>sqllocaldb stop MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" stopped.

C:\>sqllocaldb delete MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" deleted.

C:\>sqllocaldb create MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" created with version 13.0.1601.5.

C:\>sqllocaldb start MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" started.

Note: the create command will create a new instance of LocalDb with the latest version. You can specify the version number if you want a specific version.

Want More?

Learn more about SQL and read about updating your SQL database here!

Join the Conversation

  1. Grant Erickson
  2. Grant Erickson

11 Comments

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

  1. I was annoyed that you were missing some obvious steps before I realized the problem was with my assumptions! As a newbie to SQLLocalDb, it never occurred to me that deleting an instance doesn’t delete the .mdf files of databases I create! For others in my shoes: Before you do the above, right-click on your databases to figure out where the main .mdf files are. After you do the above, in SQL Server Management Studio, you can right-click on “Databases” and choose “Attach…” to attach your MDF files, then execute an ALTER DATABASE statement ( https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level )

    1. John,
      Thanks for the added information. Hopefully, that will helps others in the same situation.
      Grant

  2. So glad I wrote this over three years ago. I just used it to upgrade from 2016 to 2019.

  3. I have installed 2016 LocalDB from msdn subscriptions but it did not work by itself.
    Deleting the old instance and recreating with the commands above, it worked. It created it with 2016 version.

    Thanks for the post, it was helpful.