SQL Server Performance

Moving a File (NDF) to a new drive

Discussion in 'Performance Tuning for DBAs' started by airjrdn, Mar 13, 2003.

  1. airjrdn New Member

    I've got a database with 2 FileGroups and 3 Files. It's laid out like so:
    ============================================
    Primary
    DBName_Data - E:pathFilename.MDF

    Index_Data
    DBName_Index - E:pathFilename.NDF
    ============================================
    The Log files are:
    ------------------
    D:DBName_Log.LDF
    E:DBName_Log2.LDF
    ============================================
    We just added a new drive (raid array) to the server and I need to move the .NDF file to the new drive for performance reasons.

    So, my current file/paths are:
    E:pathFilename.MDF
    E:pathFilename.NDF
    D:DBName_Log.LDF
    E:DBName_Log2.LDF

    I need to detach the DB, and move the NDF and reattach, but I'm not sure how to do this having more than one MDF/LDF.

    What I want is:
    E:pathFilename.MDF
    G:pathFilename.NDF
    D:DBName_Log.LDF
    E:DBName_Log2.LDF

    I know to detach, all I need is:
    EXEC sp_detach_db 'DBName', 'true'

    But how do I reattach (after moving the NDF to drive G)?

    Thanks for any help!
    airjrdn, Mar 13, 2003
    #2

  2. dtipton New Member

    dtipton, Mar 13, 2003
    #2

  3. airjrdn New Member

    But when I reattach, how do I make the .NDF go back into the Index_Data filegroup?
    airjrdn, Mar 13, 2003
    #2

  4. airjrdn New Member

    I'm assuming what I need to do is:

    Detach the database.
    Copy the .NDF to drive G
    Reattach the database (using the .NDF on drive G
    Delete the old .NDF
    airjrdn, Mar 13, 2003
    #2

  5. airjrdn New Member

    I've been looking in BOL, and this is what I've come up w/so far.

    CREATE DATABASE DBName
    ON PRIMARY
    ( NAME = DBName_Data,
    FILENAME = 'E:pathDBName_Data.MDF'),
    FILEGROUP PRIMARY
    ( NAME = DBName_Index,
    FILENAME = 'G:pathDBName_Index_Data.NDF'),
    FILEGROUP INDEX_DATA
    LOG ON
    ( NAME = 'DBName_Log',
    FILENAME = 'D:pathDBName_log.LDF'),
    ( NAME = 'DBName_1_Log',
    FILENAME = 'E:pathDBName_Log2.LDF')
    GO

    Does this look correct?
    airjrdn, Mar 13, 2003
    #2

  6. satya Moderator

    In any manner .NDF doesn't deals with SP_ATTACH_DB or SP_DETACH_DB.

    Once you attach .MDF and .LDF you can use that .NDF by the way of ALTER DATABASE.
    BOL refers ALTER DATABASE Test1
    ADD FILE
    (
    NAME = Test1dat2,
    FILENAME = 'c:program FilesMicrosoft SQL ServerMSSQLData 1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
    )
    GO


    _________
    Satya SKJ
    satya, Mar 13, 2003
    #2

  7. airjrdn New Member

    Thanks for the info.

    I created a database on my local machine that mirrors the file structures of our production database and got it to reattach using the following code:

    CREATE DATABASE DBName
    ON PRIMARY
    ( NAME = DBName_Data,
    FILENAME = 'E:pathDBName_Data.MDF'),
    FILEGROUP INDEX_DATA
    ( NAME = DBName_Index,
    FILENAME = 'G:pathDBName_Index_Data.NDF')
    LOG ON
    ( NAME = 'DBName_Log',
    FILENAME = 'D:pathDBName_log.LDF'),
    ( NAME = 'DBName_1_Log',
    FILENAME = 'E:pathDBName_Log2')
    For Attach
    GO
    airjrdn, Mar 14, 2003
    #2

Share This Page