Visual Server 数据库项目数据移动脚本在不同版本的数据库上运行
我们一直在使用 Visual Studio 数据库项目来维护项目的当前架构，该项目对于我们将数据库架构移植到新的开发计算机方面非常有效，但我们还没有使用它来升级环境。以前，我们使用迁移脚本将您从初始版本转移到下一个版本，依此类推，直到您使用当前版本，但现在我们希望利用数据库项目的强大功能。
我最近阅读了 Barclay Hill
在部署期间管理数据移动（第1部分）
在部署期间管理数据移动（第 2 部分）
其中描述了如何在从一个版本转到另一个版本时执行部署前和部署后脚本，我们已经使用了它，效果很好，但我现在陷入了无法解决的问题和感觉我错过了。我们有两个版本不同的数据库，但迁移脚本不适用于两个版本中较旧的版本。以下是我们场景的简化版本。
场景
版本 1
表1
列ABC CHAR(1)
版本 2
表 1
ColumnXYZ INT
从版本 1 到版本 2 的数据移动
预部署脚本检查数据库所在的版本，如果是版本 1，则将 ColumnABC 中的数据放入临时表中。
部署后脚本检查我们现在是否处于版本 2，并检查预部署脚本中创建的临时表是否存在，并在将 char 转换为 int 后将其放入新列 ColumnXYZ 中。
版本 3
表 1
Column123 INT
当我们将数据库从版本 1 升级到版本 2，然后升级到版本 3 时，一切正常。但是，如果我们有版本 1 的数据库并希望跳转到版本 3，则部署后脚本会失败，因为没有 ColumnXYZ，因为它现在是 Column123。
在旧的迁移方法中，这不会成为问题，因为部署会逐一经历每个版本，但这不是数据库项目的工作方式。还有其他人经历过这个吗？你是如何处理的？我是否错过了一些明显的事情？
We have been using Visual Studio Database projects to maintain the current schema of the project which has worked well for us in regards to getting the database schema onto new development machines but we have not used it to upgrade environments. Previously we have used migration scripts that take you from the initial version to the next version and so on until you are at the current release but now we want to leverage the power of the database projects.
I have recently read the two posts by Barclay Hill
Managing data motion during your deployments (Part 1)
Managing data motion during your deployments (Part 2)
Which describes how to do pre and post deployment scripts when going from one version to another which we have used to great effect however I am now stuck on something which I cannot solve and feel that I have missed. We have two databases that are on different versions but the migration scripts do not work on the older of the two. The following is a simplified version of our scenario.
Scenario
Version 1
Table1
ColumnABC CHAR(1)
Version 2
Table1
ColumnXYZ INT
Data motion from version 1 to version 2
Pre deployment script checks what version the database is at and if it is at version 1 it puts the data from ColumnABC into a temp table.
Post deployment script checks that we are now at version 2 and checks for the existence of the temp table created in the pre deployment script and puts it into the new column ColumnXYZ after converting the char to an int.
Version 3
Table1
Column123 INT
When we upgrade a database from version 1 to version 2 and then to version 3 everything works fine. However if we have a database at version 1 and want to jump to version 3 the post deployment script fails because there is no ColumnXYZ as it is now Column123.
In the old method of migration this would not have been a problem as the deployment goes through each of the versions one by one but that is not how the database projects work. Has anyone else gone through this? How did you deal with it and have I missed something obvious?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
很抱歉看到您在这里没有得到任何答复。你最后有办法解决一些问题吗？
我刚刚开始研究“数据家伙”，整个数据运动的事情看起来绝对是房间里的大象。
我刚刚读完您提到的两篇博客文章，我的理解是您需要结合检查版本和预期的架构条件。因此，我猜测这意味着您需要在版本 3 的部署后脚本中执行两个不同的子句。
（请让我知道。我仍在尝试决定是否投资于此或只是继续经典的DIY路线。）
Sorry to see you didn't get any answer here. Did you manage to work something out in the end?
I've just started researching 'data dude', and the whole data motions thing definitely seems to be the elephant in the room.
I've just come from reading the two blog posts you mention, and my understanding is that you would need to combine checking the version AND the expected schema conditions. So I'm guessing that would mean you'd need to do two different clauses in your post deployment script for Version 3.
(Please let me know. I'm still trying to decide whether to invest in this or just go the classic DIY route.)
在我看来，您至少有两个选择：
1）需要升级路径1->2->3。
2) 将 ColumnABC-ColumnXYZ 数据移动更改为以下内容：
As I see it, you have at least two options:
1) Require upgrade path 1->2->3.
2) Change your ColumnABC-ColumnXYZ datamotion to the following: