Creating new filegroups for existing db

• Denby

  Hall of Fame

  Points: 3120

  More actions

  September 18, 2007 at 8:22 am

  #69968

  Hi everyone,

  I recently created a new Default filegroup for an existing production database so that the System objects would be stored seperately from the User database.

  My question is how do i get the existing user database files to now be transferred to the new filegroup i created.

  Thx for all the assistance

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  September 18, 2007 at 9:00 am

  #734331

  -- quote BOL --

  Filegroups can be created when the database is first created or later when more files are added to the database. However, it is not possible to move files to a different filegroup after the files have been added to the database.

  -- end quote --

  It's not possible to move existing files between filegroups.

  You can however move the tables by creating a clustered index on the new group.

  /Kenneth

   

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  September 18, 2007 at 9:04 am

  #734334

  You have to move them object by object.

  If there is a clustered index you do this by modifying the index.

  -- Drop existing primary key constraint.

  ALTER TABLE dbo.Mytable DROP CONSTRAINT MytableID_cl

  GO

  ALTER TABLE dbo.Mytable ADD CONSTRAINT MytableID_cl PRIMARY KEY CLUSTERED (MytableID) ON [NewFileGroup]

  If no Clustered index.

  then you need to create a temptable on the new filegroup, copy the rows from the original Table.

  Drop Original table.

  Rename new table to original name.

   

• Denby

  Hall of Fame

  Points: 3120

  More actions

  September 18, 2007 at 9:17 am

  #734342

  Ok guys thx for the info

   

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  September 19, 2007 at 3:11 am

  #734500

  Ray...?

  You don't need to create/drop tables if there is no clustered index on the table you want to move.

  It's enough to just create a clustered index on the table on the filgroup. (then drop the ci if necessary)

  The 'original' table will then silently relocate itself (the data) to the new filegroup automagically.

  /Kenneth

• jon-168559

  SSC Enthusiast

  Points: 130

  More actions

  September 21, 2007 at 2:32 am

  #735147

  -- Create your new filegroup with a data file on your free drive ---

  ALTER DATABASE test

  ADD FILEGROUP myNewFileGroup

  GO

  ALTER DATABASE test

  ADD FILE

  (

   NAME = myNewFile1,

   FILENAME = 'E:\Data\myNewFile1.mdf',

   SIZE = 1MB,

   MAXSIZE = 100MB,

   FILEGROWTH = 5MB

  )

  TO FILEGROUP myNewFileGroup

  GO

  -- Then move the heavy tables to that filegroup --------------------------

  To place an existing table on a different filegroup

  1. Expand a server group, and then expand a server.
  2. Expand Databases, expand the database in which the table belongs, and then click Tables.
  3. In the details pane, right-click the table, and then click Design Table.
  4. Right-click any column, and then click Properties.
  5. On the Tables tab, in the Table Filegroup list, select the filegroup on which to place the table.
  6. Optionally, in the Text Filegroup list, select a filegroup on which to place any text, image, and ntext columns.