Moving Data to a New Filegroup

Occasionally, you may need to move a table from one filegroup to another. Perhaps someone inadvertently created a table in the system filegroup, but company policy dictates never to do that. Perhaps you need to split data up between several filegroups to get better I/O performance. Either way, manually moving data from one filegroup to another can be a tedious, time-consuming, and error-prone process.

flip that table!

                                    How not to move a table!

This post provides an automated script that can handle many scenarios where you need to move data from one filegroup to another.

The script temporarily creates a stored procedure, executes the stored procedure several times, then drops the stored procedure. I recommend running the stored procedure with the @DebugOnly parameter set to 1, at least initially, to see what T-SQL statements will be executed. If you set the @DebugOnly parameter to 0, those T-SQL statements will be executed, and the selected indexes will be moved from the @OldFileGroupName to the @NewFileGroupName filegroup.

Example usage for the procedure:

It would be great if you could simply rebuild a table with a modified ON [...] clause to move all associated data and indexes into a new filegroup. Something like ALTER TABLE <sometable> REBUILD ON <newfilegroup>;. Unfortunately, the required syntax is not supported. Alternately, it might be nice if the ALTER TABLE ... SWITCH syntax supported specifying a different filegroup. Alas, that doesn’t work either.

Check out the rest of our SQL Server tools!