如何将 SQL Server localDB 升级到更新版本? [英] How do I upgrade SQL Server localDB to a newer version?
问题描述
SqlServer localDB从2012年升级到2014年有可能吗?
我们目前使用的是 SQL Server 2012 的版本 11.我需要从 SQL Server 2014 升级到版本 12.
我希望能够在不丢失表格和数据的情况下完成这项工作.
我安装了一个新的 localDB，但是我没有我的数据.它还有另一个名字，我无法真正更改配置文件，因为它是一个团队项目.
我尝试使用命令行 sqlLocalDB 工具创建一个名为 v11.0 的 2014 版本，但无论如何它都是在旧的 2012 版本中创建的.
C:\Program Files\Microsoft SQL Server\120\Tools\Binn>sqllocaldb create v11.0使用版本 11.0.3000.0 创建的 LocalDB 实例v11.0".C:\Program Files\Microsoft SQL Server\120\Tools\Binn>sqllocaldb create v12.0使用版本 12.0.2000.8 创建的 LocalDB 实例v12.0".C:\Program Files\Microsoft SQL Server\120\Tools\Binn>sqllocaldb 创建 aaaaa使用版本 12.0.2000.8 创建的 LocalDB 实例aaaaa".为什么将它命名为 v11.0 会改变使用的版本?
如何升级现有的 v11.0?
这是我所做的，因为 Visual Studio 2019 仍然附带 Microsoft SQL Server 2016 (13.1.4001.0) LocalDB.
我需要这样做，因为我尝试添加临时表，但级联删除失败.
您可以通过在 Powershell 中运行命令 sqllocaldb info 来获取有关当前本地数据库的信息.
这是一个很好的升级指南，但我选择做一些不同的事情.
本地数据库:
从下载的exe安装:
我的建议是在此之后重新启动您的计算机，但我不确定是否需要这样做.反正我做到了.
检查sqllocaldb 版本 导致异常Windows API 调用RegGetValueW"；返回错误代码:0. 对我来说.
使用这个答案解决了它:
更改文件夹名称后一切正常:
此后备份当前本地数据库中的数据库.这可能不需要，因为我们稍后会将您当前 localdb 中的所有数据库附加到新版本，但如果您有敏感数据，建议这样做.
我有 Visual Studio 的标准名称 MSSQLLocalDB，因此我的示例将使用它.如前所述，您可以使用 sqllocaldb info 命令查看当前版本.
从 powershell 运行这三个命令:
sqllocaldb 停止 mssqllocaldbsqllocaldb 删除 mssqllocaldbsqllocaldb 创建 MSSQLLocalDB如果一切正常，最后一个命令应该生成类似于 LocalDB 实例mssqllocaldb"的内容.使用版本 15.0.2000.5 创建.
然后通过 SSMS (localdb)\mssqllocaldb 或类似程序登录到您的新 LocalDB 并附加您的旧数据库.通常存储在 %UserProfile% 文件夹中.
LocalDB 现已更新，希望您的所有数据库都能正常工作.
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?
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.
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
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.
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.
这篇关于如何将 SQL Server localDB 升级到更新版本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
