53

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?

Don Chambers
  • 3,798
  • 9
  • 33
  • 74
  • You're telling it to create an 11.0 version database by using `v11.0`. Type `sqllocaldb /?` from the command line and read what it says about `create`. – Ken White Nov 12 '14 at 21:50
  • 2
    No. That v11.0 is the localDB name. I can create one called v12.0 in version 11. But I can't create a v11.0o in version 12. C:\Program Files\Microsoft SQL Server\120\Tools\Binn>sqllocaldb create v12.0 11. 0 LocalDB instance "v12.0" created with version 11.0.3000.0. C:\Program Files\Microsoft SQL Server\120\Tools\Binn>sqllocaldb create v11.0 12. 0 Creation of LocalDB instance "v11.0" failed because of the following error: The parameter for the LocalDB Instance API method is incorrect. Consult the API documentation. – Don Chambers Nov 12 '14 at 22:10
  • No. That `v11.0` is the version you're telling it to create, as you'll see if you do what I asked you to do. **Read the documentation**, which in this case is as simple as doing what I said from a command prompt. – Ken White Nov 12 '14 at 23:22
  • 1
    Of course, instead of debating whether I'm correct or not, you could try two simple things: 1) **Read the documentation**, which says a number after the `create` parameter indicates the desired version, and 2) Try again, changing the number to something else like 'v999.99' and see what happens. – Ken White Nov 12 '14 at 23:49
  • 1
    I have read the documentation. The first parameter is the instance name, then there is an optional version number. In this case, the v11.0 is the instance name. I created v999.99 and it created an instance called that which is version 12. – Don Chambers Nov 13 '14 at 14:22
  • C:\Program Files\Microsoft SQL Server\120\Tools\Binn>sqllocaldb create v999.99 LocalDB instance "v999.99" created with version 12.0.2000.8. – Don Chambers Nov 13 '14 at 14:23

6 Answers6

70

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

enter image description here

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:

enter image description here

LocalDB:

enter image description here

Install from the downloaded exe:

enter image description here

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.

enter image description here

Solved it using this answer:

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

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

enter image description here

After changing the folder name everything works:

enter image description here

enter image description here

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.

enter image description here

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.

enter image description here

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

Ogglas
  • 62,132
  • 37
  • 328
  • 418
17

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.

Vi100
  • 4,080
  • 1
  • 27
  • 42
  • 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. – firepol Jan 09 '17 at 19:21
9

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.

Ronald Mokens
  • 101
  • 1
  • 6
  • 1
    I understand this. However, I can't seem to create an instance called v11.0 with version 12. It works will all other names. – Don Chambers Nov 17 '14 at 16:09
  • Have you tried stopping and deleting the v11.0 version first? – Diogo Gomes Feb 18 '15 at 14:51
  • 1
    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. – shannon May 28 '16 at 21:47
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.

Gerke Geurts
  • 632
  • 4
  • 9
2

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

Vertigo
  • 1,847
  • 22
  • 19
1

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)
...
  1. As suggested in the orignal answer run (or with your custom db name)
sqllocaldb stop mssqllocaldb
sqllocaldb delete mssqllocaldb
sqllocaldb create MSSQLLocalDB
  1. 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;
  1. 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
Suciu Eus
  • 169
  • 1
  • 8