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

  • SQL - Insert if the number of rows is greater than
  • Selecting SUM of TOP 2 values within a table with multiple GROUP in SQL
  • Passing array to a SQL Server Stored Procedure
  • how do i generate scripts for all tables with single stroke in sql server 2000
  • How to make a case when better in sql
  • EF6 Model First: schema specified is not valid, error 0040
  • 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:

     add filegroup NewGroup

    Then, create an appropriate file for that file group, for example:

     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
       Data varchar(100) not null
       constraint PK_MyTable
        primary key clustered
    --  Can't "move" primary key constraint to a new file group
     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
     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!

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.