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

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 DELETEor 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 thecurrent table. In other words, when temporal table is referencingtable in the foreign key relationship (corresponding toparent_object_id in sys.foreign_keys) CASCADE options are not allowed.To work around this limitation, use application logic or aftertriggers to maintain consistency on delete in primary key table(corresponding to referenced_object_id in sys.foreign_keys). Ifprimary 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 aresupported 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 mssqllocaldbsqllocaldb delete mssqllocaldbsqllocaldb 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.

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.

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.