We have a SQL Server 2005 holding all the scanned e-files. Right now it is almost out of space. So we added another big disk array. But we need to move some big tables over to the big disk array. I tried to search a simple and easy way to do it online instead of detaching database and copying. I found a way to move the regular table data with “ALTER TABLE” statement’s “MOVE TO” option by dropping and recreating the clustered index . something like following:

ALTER TABLE myefile DROP CONSTRAINT PK_UID WITH (MOVE TO bigfilegroup)

GO

ALTER TABLE myefile DROP CONSTRAINT PK_UID PRIMARY KEY(UID)

GO

But this way didn’t move my Big Text/Image columns. In table’s properties, there are two options for filegroup: Filegroup for regular data, and Text/Image Filegroup for Big Text/Image columns. I tried to change it in the table designer directly. But unfortunately it came as timeout expired. So I generated the script from table designer. What the script does is to create a temp table which has the same structure as the original one, insert records from the old one, drop the old one, and rename the temp table back to original one. And of cz it has to use transactions. I ran the script in SQL Management Studio. After a couple hours, my big tables moved to the big array successfully without taking offline.