I recently tried to rebuild an old database that was not created with filegroups (only by default PRIMARY) and, among other things, move a bunch of tables to a new filegroup Datalocated in the SAN. I know how to transfer 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 is not the most tiring job that I have ever had to do. And he is error prone. At some point, I was halfway (I guess there is no progress indicator), moving the table 30 GB before I realized that I accidentally turned on one of the value columns in the PC. So I had to start all over again.
This is even worse when a table has many dependencies. Then I cannot just delete the primary key; I need to reset and recreate every foreign key that refers to it. This results in hundreds of lines of pattern; multiply by 100 tables and it will become completely asinine. My wrists hurt.
Has anyone come up with a shortcut for this? Could there be any tools out there (at a price with the concept of one-time use) that can do this? Perhaps someone here had to go through this process earlier and write their own tool / script, that they are not against sharing?
SSMS , - ( , UNIQUE ), , , , , , UNIQUE).
, , gen . , drop-and-rereate-the-primary-key. drop/recreate , , , , , .
, : ? , ?
!