Create File Group on different drive and move table into it
How can I create a File Group on a different drive with MSSQL 2005 and then move a table into it?
Ex: my database is on H:\Product.mdf and H:\Product.ldf
I want to create a new file group on F:\FileGroup\ and then move my table with a clustered index to this new file group.
One Solution collect form web for “Create File Group on different drive and move table into it”
This is not a simple task, and depending on the size of your tables may require a chunk of downtime.
First, you have to define the new file group:
ALTER DATABASE MyDatabase add filegroup NewGroup
Then, create an appropriate file for that file group, for example:
ALTER DATABASE MyDatabase add file ( name = NewFile ,filename = 'C:\temp\NewFile.ndf' ,size = 100MB ,maxsize = unlimited ,filegrowth = 100MB ) to filegroup NewGroup
To move a table onto the file group, you have to create a clustered index for that table on the file group. If you’ve got a clustered constraint (such as a unique or primary key), you’ll have to drop it first. Here’s one way to move such a table:
-- Set up sample table CREATE TABLE MyTable ( Data varchar(100) not null constraint PK_MyTable primary key clustered ) -- Can't "move" primary key constraint to a new file group ALTER TABLE MyTable drop constraint PK_MyTable -- This will move the data in the table to the new file group CREATE clustered index Move_MyTable on MyTable (Data) on NewGroup -- Still in the new file group, just no index DROP INDEX MyTable.Move_MyTable -- Recreate the primary key, keeping it on the new file group ALTER TABLE MyTable add constraint PK_MyTable primary key clustered (Data) on NewGroup
It is just a bit fussy, so be sure to test everything on copies of your databases first!