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

  • How to pass in parameters to a SQL Server script called with sqlcmd?
  • How do I get the Identity field that results from an insert?
  • find duplicate records sql server
  • What is exact use of cursor in sql server
  • Using TimeZones on website using ASP.Net 3.5 / SQL Server 2005
  • Return an array after querying database in Java
  • 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!

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