Post reply

how to move table from .mdf to .ndf

  • February 9, 2011 at 3:38 pm

    #148348

    I have table of 150GB. I need to move it from E:\SQL\database.MDF location to k:\SQL\database.NDF.

    can help me. Thanks in advance.

  • February 9, 2011 at 3:48 pm

    #1284874

    You have to assign the new file to a different logical filegroup in the database, otherwise you can't control it.

    Once you have done that, you rebuild the clustered index in the new filegroup. You'll be making the table unavailable during this process.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • February 9, 2011 at 3:57 pm

    #1284880

    Can explain it a bit clear.. I am not get how to do it.

    step by step process please.

  • February 9, 2011 at 4:02 pm

    #1284884

    If there is any script belonging to it. please post it.

    Thanks in advance.

  • February 9, 2011 at 4:05 pm

    #1284888

    Which part do you need a script for?

    Do you have multiple files and filegroups already created for your database?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 9, 2011 at 7:40 pm

    #1284923

    Hello,

    First create the new filegroup

    USE master

    GO

    ALTER DATABASE Test1

    ADD FILEGROUP Test1FG1

    GO

    ALTER DATABASE Test1

    ADD FILE

    ( NAME = test1dat3,

    FILENAME = 'k:\SQL\database.NDF',

    SIZE = 200GB,

    FILEGROWTH = 10GB)

    TO FILEGROUP Test1FG1

    Then rebuild the clustered index into the new filegroup created

    CREATE CLUSTERED INDEX CIX_YourTable

    ON dbo.YourTable(YourClusteringKeyFields)

    WITH DROP_EXISTING

    ON [Test1FG1]

    And that's it. I hope this can clear things up.

  • February 10, 2011 at 11:16 am

    #1285364

    Thanks

  • July 25, 2013 at 8:39 am

    #1635747

    Hi All,

    how we can find one table is located in mdf file or ndf file ...

    Thanks in advance.

  • July 25, 2013 at 8:54 am

    #1635766

    Hi - Start a new thread to post your questions!

Viewing 9 posts - 1 through 8 (of 8 total)

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