Automated Filegroup Migration in SQL Server

Question

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!

Answer 1

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.