SQL Server 中的自动文件组迁移
最近，我一直在尝试重组一个未使用文件组设计的旧数据库（只是默认的PRIMARY），并且除其他外，将一堆表移动到新的Data代码> 驻留在 SAN 上的文件组。我知道如何迁移数据：
ALTER TABLE MyTable
DROP CONSTRAINT PK_MyTable WITH (MOVE TO [MyDB_Data])
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY CLUSTERED (MyID)
ON [MyDB_Data]
但这不是我做过的最乏味的工作。而且很容易出错。有一次，我在移动 30 GB 表的过程中（我猜是这样，因为没有进度指示器），才意识到我不小心在 PK 中包含了其中一个值列。所以我不得不重新开始。
当表有很多依赖性时，情况会更糟。那么我就不能直接删除主键；我必须删除并重新创建引用它的每个外键。这导致了数百行样板文件；乘以 100 个表，它就变得彻头彻尾的愚蠢了。我的手腕受伤了。
有人为此想出了捷径吗？是否有任何工具（考虑到一次性使用的概念定价）可以做到这一点？也许这里有人以前必须经历这个过程并编写他们自己的工具/脚本，他们不介意分享？
显然，SSMS 不会这样做 - 它只能为非聚集索引生成迁移脚本（并且它们必须是索引，而不是 UNIQUE 约束 - 至少在几个表上，为了更好或为了更糟糕的是，聚集索引实际上并不是主键，它是一个不同的 UNIQUE 约束）。
并不是语法太复杂以至于我无法为其编写代码生成。至少对于基本的删除并重新创建主键部分来说是这样。但是，加上计算出所有依赖关系并为所有外键生成删除/重新创建脚本的开销，这开始让人感觉刚刚超过了阈值，自动化和全面测试的工作量比只执行每个表的工作量要多与上面的示例一样手动。
所以，问题是：这个过程可以以任何相当简单的方式自动化吗？除了我上面写的内容之外，还有其他选择吗？
谢谢！
Recently I've been trying to restructure an old database that was not designed with filegroups (just the default PRIMARY) and, among other things, move a bunch of tables to a new Data filegroup residing on a SAN. I know how to migrate the data:
ALTER TABLE MyTable
DROP CONSTRAINT PK_MyTable WITH (MOVE TO [MyDB_Data])
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY CLUSTERED (MyID)
ON [MyDB_Data]
But damned if this isn't the most tedious work I've ever had to. And it's error-prone. At one point I was halfway (I assume, since there's no progress indicator) through moving a 30 GB table before I realized that I had accidentally included one of the value columns in the PK. So I had to start all over again.
It's even worse when the table has a lot of dependencies. Then I can't just drop the primary key; I have to drop and recreate every foreign key that references it. This leads to hundreds of lines of boilerplate; multiply by 100 tables and it becomes downright asinine. My wrists hurt.
Has anybody come up with a shortcut for this? Are there maybe any tools out there (priced with the notion of one-time-use in mind) that can do it? Perhaps somebody here has had to go through this process before and wrote their own tool/script that they wouldn't mind sharing?
SSMS won't do it, obviously - it can only generate migration scripts for non-clustered indexes (and they have to be indexes, not UNIQUE constraints - on at least a few tables, for better or for worse, the clustered index is not actually the primary key, it's a different UNIQUE constraint).
It's not that the syntax is so complicated that I can't write a code gen for it. At least for the basic drop-and-recreate-the-primary-key part. But add in the overhead of figuring out all the dependencies and generating drop/recreate scripts for all the foreign keys and this starts to feel like it's just over that threshold where it's more work to automate and fully test than it is to just do every table manually as with the example above.
So, the question is: Can this process be automated in any reasonably straightforward way? Are there any alternatives to what I've written above?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
IMO，最简单的方法是使用模式比较工具之一（我的工具，red gateway的SQL Compare ，Apex SQL Diff 作为几个示例）来创建架构脚本。然后，编辑该脚本以在正确的文件组中创建所有空对象。完成此操作后，您可以使用相同的工具将新数据库与正确的文件组进行比较，它们将生成脚本来为您迁移数据。值得测试多个，以找到最适合您的。
The simplest way to do it, IMO, would be to use one of the schema comparison tools (My tool, red gate's SQL Compare, Apex SQL Diff as a couple of examples) to create a script of your schema. Then, edit that script to create all the objects, empty, in the right file groups. Having done that, you can then use the same tools to compare your new DB with correct filegroups, and they will generate the scripts to migrate the data for you. It's worth testing with multiple ones to find which is the most appropriate for you.