Post reply

Creating new filegroups for existing db

  • 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

  • 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

     

  • 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.

     

  • September 18, 2007 at 9:17 am

    #734342

    Ok guys thx for the info

     

  • 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

  • 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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply