How do I upgrade SQL Server localDB to a newer version?

Question

Is it possible to upgrade SqlServer localDB from 2012 to 2014?

We currently use version 11 from SQL Server 2012. I need to upgrade to version 12 from SQL Server 2014.

I would like to be able to do it without losing my tables and data.

I installed a new localDB but I then I don't have my data. It also has another name and I can't really change the config files since it's a team project.

I tried using the command line sqlLocalDB tool to create a 2014 version called v11.0 but it created it in the old 2012 version any way.

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>sqllocaldb create v11.0
LocalDB instance "v11.0" created with version 11.0.3000.0.

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>sqllocaldb create v12.0
LocalDB instance "v12.0" created with version 12.0.2000.8.

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>sqllocaldb create aaaaa
LocalDB instance "aaaaa" created with version 12.0.2000.8.

Why would naming it v11.0 change which version was used?

How can I upgrade the existing v11.0?

Answer 1

To target a specific version of LocalDb, use 12.0 or 11.0 as a parameter. For instance:

sqllocaldb create "mydb" 12.0

The reason your command failed was because you were referencing the version incorrectly. This isn't all too clear in the -? info.

Using "v12.0" as the first parameter merely sets the instance name. If no second version parameter is given, the default version is used.

Answer 2

Update:

Visual Studio 2022 ships with Microsoft SQL Server 2019 15.0.4153.1 LocalDB. If you have Visual Studio 2022 installed but have previously used an earlier version of Visual Studio you can jump to the command sqllocaldb versions below to upgrade.

https://developercommunity.visualstudio.com/t/visual-studio-2022-installs-old-version-of-sql-ser/1466986

Original:

This is what I did since Visual Studio 2019 still ships with Microsoft SQL Server 2016 (13.1.4001.0) LocalDB.

I needed to do it because I tried to add Temporal tables with cascading delete that failed.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

Failed executing DbCommand (31ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] ALTER TABLE Text SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.Text));

Setting SYSTEM_VERSIONING to ON failed because table 'Project.Repository.dbo.Text' has a FOREIGN KEY with cascading DELETE or UPDATE.

Reading up on it it turns out this error only affects SQL Server 2016 and not 2017 and later.

ON DELETE CASCADE and ON UPDATE CASCADE are not permitted on the current table. In other words, when temporal table is referencing table in the foreign key relationship (corresponding to parent_object_id in sys.foreign_keys) CASCADE options are not allowed. To work around this limitation, use application logic or after triggers to maintain consistency on delete in primary key table (corresponding to referenced_object_id in sys.foreign_keys). If primary key table is temporal and referencing table is non-temporal, there's no such limitation.

This limitation applies to SQL Server 2016 only. CASCADE options are supported in SQL Database and SQL Server 2017 starting from CTP 2.0.

https://stackoverflow.com/a/54591579/3850405

https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations?view=sql-server-2017

You can get information about your current localDBs by running the command sqllocaldb info in Powershell.

This is quite a good upgrading guide but I choose to do some things a bit differently.

https://medium.com/cloudnimble/upgrade-visual-studio-2019s-localdb-to-sql-2019-da9da71c8ed6

Download SQL Server Express 2019 LocalDB or newer and run the exe.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver15

Select Download Media:

LocalDB:

Install from the downloaded exe:

My recommendation is to restart your computer after this but I'm not sure it is needed. I did it anyway.

Checking sqllocaldb versions caused the exception Windows API call "RegGetValueW" returned error code: 0. for me.

Solved it using this answer:

https://stackoverflow.com/a/58275480/3850405

This is how Registry Editor (regedit) looks like in the non working example:

After changing the folder name everything works:

After this back up databases in your current localdb. This will probably not be needed since we will attach all databases from your current localdb to the new version later but if you have sensitive data this is recommended.

I have the standard name MSSQLLocalDB from Visual Studio so my example will use this. As mentioned before you can use sqllocaldb info command to view your current versions.

Run these three commands from powershell:

sqllocaldb stop mssqllocaldb
sqllocaldb delete mssqllocaldb
sqllocaldb create MSSQLLocalDB

If everything works the last command should generate something like LocalDB instance "mssqllocaldb" created with version 15.0.2000.5.

Then log into your new LocalDB via SSMS (localdb)\mssqllocaldb or a similar program and attach your old databases. Usually stored in the %UserProfile% folder.

LocalDB is now updated and hopefully all your databases works normally.

Answer 3

I would also add to @Ogglas's answer that if you want to migrate it automatically in your software, you can follow this steps.

Do these steps when upgrading (upgrade step or upgrade code):

1. Read the current physical path of your DB: use {dbName}; SELECT physical_name FROM sys.database_files WHERE name = '{dbName}'
2. Check the current SQL version of your DB - via SQLLocalApi utility class or by running sqllocaldb i your_db in a separate process or by something similar to:

DECLARE @dbversion nvarchar(128)

SET @dbversion = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @dbversion = SUBSTRING(@dbversion, 1, CHARINDEX('.', @dbversion) - 1)

SELECT @dbversion -- this will give you the major version (eg. 12 or whatever is your old version)
...

3. As suggested in the orignal answer run (or with your custom db name)

sqllocaldb stop mssqllocaldb
sqllocaldb delete mssqllocaldb
sqllocaldb create MSSQLLocalDB

4. If @dbversion from step 2 was an old one (eg. 12 for SQL LocalDB 2014) reattach the DB using the physical path recorded at step 1.

CREATE DATABASE [{dbName}]
    ON PRIMARY (NAME={dbName}, FILENAME = '{dbPath}')
    for attach;

5. Optional: upgrade the compatibility level of your DB (as reattaching the MDF will preserve the old compatibility level).

EXEC('ALTER DATABASE {db_name} SET COMPATIBILITY_LEVEL = 150'); -- or whatever version you are upgrading to

Answer 4

A simple way to upgrade a SQL Server LocalDb instance is to delete the instance data directory from the %LocalAppData%\Microsoft\Microsoft SQL Server Local DB\Instances\ directory. On next use of the instance it will be recreated with the latest installed version.

Answer 5

As stated in this link from MSDN (Not too well chosen title...) the conversion should be triggered somewhat just double clicking on the .mdf file... This didn't happened in my case (maybe because it's necessary to have the Sql Server Management Studio software or something similar for this to be truth).

So, here is an alternative procedure:

• Open the "Server explorer" in Visual Studio

• Click on the "Connect to database" icon on the upper left corner

• Chose "Microsoft SQL Server" in the list and press Next

• Write this as the Server Name: (LocalDB)\MSSQLLocalDB

• Select "Attach a database file" in the options below and browser to your .mdf file

• Proceed and this should show a dialog asking you to trigger the conversion or not

And that's it. Not very intuitive at all, but effective. I haven't found any documentation on it other than te link mentioned before, all the other steps were pure intuition but worked for me, I hope for you too.

Answer 6

It seems that v11.0 is a special name for the LocalDB 2012 automatic instance. And it is strictly tied to this version. While 2014 and 2016 versions share the same MSSQLLocalDB name for that purpose.

More about LocalDB instances