Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    24

    Unanswered: Moving Datafile to different filegroup

    Hi,
    In SQL Server 2000 how to move a secondary data file to different filegroup without removing and re-creating the secondary data file.

    Thanks in advance.
    -S

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by mohit_sharan
    Hi,
    In SQL Server 2000 how to move a secondary data file to different filegroup without removing and re-creating the secondary data file.

    Thanks in advance.
    -S
    I don't believe that this can be done. Objects may be created on a specific file group, but the dba cannot control the individual file within the file group on which the object is created.

    For example:

    Data base file groups and associated files:
    PRIMARY
    - E:\MSSQL\Data\Data1.mdf
    - E:\MSSQL\Data\Data2.ndf
    SECONDARY
    - E:\MSSQL\Data\Data3.ndf
    - E:\MSSQL\Data\Data4.ndf

    Table DDL:
    CREATE TABLE Foo (MyColumn varchar(20) NULL)
    ON [PRIMARY]
    GO

    CREATE TABLE Bar (MyColumn varchar(20) NULL)
    ON [SECONDARY]
    GO

    You cannot now move Data2.ndf to the SECONDARY file group. The object Foo is in the PRIMARY filegroup and may (or may not) reside partially on Data2.ndf.

    Out of curiousity, what do you expect to gain from attempting this?

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •