SQL Server Performance

Transfering existing objects to a new file group

Discussion in 'General Developer Questions' started by sqldev, Dec 5, 2005.

  1. sqldev New Member

    Hi,

    Is it possible to move the existing database objects like tables, views, indexes, etc., to a newly created file group? Will it be an advisable thing to do? From books online, I could not collect details regarding this.

    We have a database in a reasonable size, we find it difficult to restore it due to its size, say lack of resources. We decided to create new file groups and transfer the existing objects to them. Will the existing databse remain intact if we do so?

    Please provide me more details, thank you.

    Regards,
    Deva
    sqldev, Dec 5, 2005
    #2

  2. ghemant Moderator

    Hi,<br />yes it can be done with *Alter Object (table/index/views)* <br /><br />this is from BOL <br />alter table yrtable<br />ON {filegroup | DEFAULT}<br /><br />Specifies the storage location of the index created for the constraint. If filegroup is specified, the index is created in the named filegroup. If DEFAULT is specified, the index is created in the default filegroup. If ON is not specified, the index is created in the filegroup that contains the table. If ON is specified when adding a clustered index for a PRIMARY KEY or UNIQUE constraint, the entire table is moved to the specified filegroup when the clustered index is created.<br /><br />DEFAULT, in this context, is not a keyword. DEFAULT is an identifier for the default filegroup and must be delimited, as in ON "DEFAULT" or ON [DEFAULT].<br /><br />please search the forum for filegroup its already discussed earlier ,not easy to advice but *it depends* to move or not move / will increase performance or not .<br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami<br />
    ghemant, Dec 5, 2005
    #2

  3. vsnreddi New Member

    hi Hemantgiri S. Goswami

    Is it possible through DTS ?



    VSNREDDY

    Be pure to do good.[Vivekananda]
    vsnreddi, Dec 5, 2005
    #2

  4. sqldev New Member

    Hi Hemant,

    Thank you, what you have mentioned is applicable during column level or table level constraints creation for the existing tables. In my case, table and all its associated objects are already availble, now, I would like to move as it is to another file group.

    It can be done from Enterprise Manager, but it is a tedious task for the administrator, he has to do one by one. Instead of that, I would like to give him a script file which will keep his job simple and it will be error free as well.

    As you said, I will search in the existing posts about this, thanks again.

    Regards,
    Deva
    sqldev, Dec 6, 2005
    #2

  5. vsnreddi New Member

    quote:Originally posted by sqldev

    Hi,

    Is it possible to move the existing database objects like tables, views, indexes, etc., to a newly created file group? Will it be an advisable thing to do? From books online, I could not collect details regarding this.

    We have a database in a reasonable size, we find it difficult to restore it due to its size, say lack of resources. We decided to create new file groups and transfer the existing objects to them. Will the existing databse remain intact if we do so?

    Please provide me more details, thank you.

    Regards,
    Deva
    ----------------------------
    check this

    http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/managesql.mspx




    SURYA

    Be pure to do good.[Vivekananda]
    vsnreddi, Dec 6, 2005
    #2

  6. sqldev New Member

    Hi,

    Thank you surya, I have found out an alternative for my requirement, while creating clustered index, we can specify the file group where the index will be created, at this time, data will be moved to the mentioned file group along with the index creation. If a table does not contain any clustered index, we need to move the data to a new object, drop the old object and rename the newly created object.

    If any one has any other way of achieving it, please share it with me, thank you.

    Regards,
    Deva
    sqldev, Dec 6, 2005
    #2

  7. rohit2900 Member

    Hello All,
    I want to move a file from primary file group to secondary file group, Can I do it through SSMS or can anybody tell me the sql command to do this.

    I tried below but didn't worked
    ALTER DATABASE pubs
    MODIFY FILE
    (
    NAME = pubs_Data_1
    )
    TO FILEGROUP SECONDARY

    Rohit

    rohit2900, Apr 23, 2008
    #3

  8. satya Moderator

    satya, Apr 23, 2008
    #4

  9. rohit2900 Member

    The article is for 2000, how to do the same thing in 2005?
    Rohit

    rohit2900, Apr 23, 2008
    #5

Share This Page