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?

Solution 1

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://docs.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://docs.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://docs.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.

Solution 2

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.

Solution 3

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.

Solution 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.

Solution 5

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

View more solutions

Comments

[Don Chambers]

I understand this. However, I can't seem to create an instance called v11.0 with version 12. It works will all other names.

[Diogo Gomes]

Have you tried stopping and deleting the v11.0 version first?

[shannon]

It seems to also pay attention to instance name in determining the version, if the name follows a default format. I cannot create "mssqllocaldb" as anything other than the latest installed version.

[firepol]

In Visual Studio 2015 I did exactly as you described. After proiceeding to upgrade my mdf file, as final step I deleted the new connection and replaced in web.config "(LocalDb)\v11.0" with "(LocalDB)\MSSQLLocalDB", then I could use my existing connection string.

[DeveTho]

Don't forget to reattach your databases after this.

[SandroRiz]

one of greatest and most useful answer that I was searching...thanks for sharing!!

[Laurent]

I wish I could add 10 likes! Thanks a lot!

[Andrii]

The name does not matter

[Vertigo]

Very helpful for cases where you have no GUI tools like VS or SSMS.

[G.Dimov]

I came looking for copper and found gold. Thanks, very descriptive and works fine.

[cytek04]

You saved my day! I am new to SQL Servers and that answer was understandable (that's saying a lot!).