将数据从一个 SQL 表布局传输到“新的”表布局改进了一
我从事的项目在数据库层面进行了改造。为了更好，大约 40% 的 SQL 布局已经改变。一些专栏被取消，另一些则被移动。我现在的任务是制定数据迁移策略。
有哪些迁移方法甚至工具可用，这样我就不必弄清楚每个单独的依赖项，并在 ID（例如）更改时手动编写关键更改的脚本。
我意识到这个问题有点迟钝和开放式，但我认为其他人以前也必须这样做，我将不胜感激任何建议。
我使用的是 MS SQL Server 2008
@OMG Ponies 不是迟钝而是模糊：
很好。我想这有助于我重新考虑我的要求，至少使其更加具体。如何从多个表插入多个表以保持外键建立的关系完好无损？我现在意识到我可以在插入期间删除 ID 键约束并在之后重新启用它，但我想我必须弄清楚什么取决于我自己并确保它顺利进行。 我将从这里开始，但如果其他人有其他建议，我将保持开放状态。
The project I work on has undergone a transformation at the database level. For the better, about 40% of the SQL layout has been changed. Some columns were eliminated, others moved. I am now tasked with developing a data migration strategy.
What migration methods, even tools are available so that I don't have to figure out each every individual dependency and manually script a key change when their IDs (for instance) change.
I realize this question is a bit obtuse and open ended, but I assume others have had to do this before and I would appreciate any advice.
I'm on MS SQL Server 2008
@OMG Ponies Not obtuse but vague:
Great point. I guess this helps me reconsider what I am asking, at least make it more specific. How do you insert from multiple tables to multiple tables keeping the relationships established by the foreign keys intact? I now realize I could drop the ID key constraint during the insert and re-enable it after, but I guess I have to figure out what depends on what myself and make sure it goes smoothly.
I'll start there, but will leave this open in case anyone else has other recommendation.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您应该创建一个升级脚本，将当前模式转变为下一个模式，并应用适当的操作（更改表、选择、更新、删除等）。虽然这可能看起来很乏味，但这是唯一可测试的过程：从当前数据库的备份开始，应用升级脚本，测试结果数据库是否符合所需的架构。您可以测试和调试升级脚本，直到确定正确性。您可以在实际数据大小上对其进行测试，以便正确估计由于数据大小操作而导致的停机时间。
虽然有一些工具可以复制数据或转换模式（例如 SQL Compare）我相信将其作为一个开发项目来处理，并提供可重复测试和验证的脚本交付物，是一种更明智的方法。
将来，您可以在开发中考虑此升级步骤，并且< em>从它开始，而不是试图把它挤到最后。
You should create an upgrade script that morphs the current schema into the v. next schema, applying appropriate operations (alter table, select into, update, delete etc). While this may seem tedious, is the only process that will be testable: start from a backup of the current db, apply the upgrade script, test the result db for conformance with the desired schema. You can test and debug your upgrade script until is hammered into correctness. You can test it on a real data size so that you get a correct estimate of downtime due to size-of-data operations.
While there are out there tools that can copy data or transforms schema(s) (like SQL Compare) I believe approaching this as a development project, with a script deliverable that can be tested repeatedly and validated, is a much saner approach.
In future you can account for this upgrade step in your development and start with it, rather than try to squeeze it in at the end.
有大量的商业工具声称可以解决这个问题 ->我不会买那个......
我认为你最好的选择是对代表你的数据的域类进行建模，并编写读取/序列化到旧/新模式的适配器。
如果您还没有您的领域的模型，您应该立即构建一个。
ID 会发生变化，因此理想情况下它们不应该对数据库的用户具有任何意义。
there are tons of commercial tools around that claim to solve this -> i wouldn't buy that...
I think your best bet is to model domain classes that represent your data and write adapters that read in/serialize to the old/new schemas.
If you haven't got a model of your domain, you should build one now.
ID's will change, so ideally they should not carry any meaning to user's of your database.