Post reply

Moving Secondary file in user database.

  • May 16, 2011 at 11:24 pm

    #237423

    Hi Folks,

    I've a test database which is having the .mdf and .ldf files in D:\Testdatabase\ and now the case is i've added a secondary file called test_1.ndf to test database. At that time unfortunately i've given the path of .ndf file in D:\. So, now i want to move that .ndf file to D:\Testdatabase\.

    so kindly anyone help me with this process.

    TIA.

  • May 16, 2011 at 11:37 pm

    #1325592

    That requires downtime.

    1.Detach database

    2.Move the file

    3.Attach database, make sure to specify the new path for the secondary file.

    Can do it from SSMS or through scripts.

    Check BOL for sp_attach_db, sp_detach_db

  • May 16, 2011 at 11:46 pm

    #1325594

    Use Alter database Modify file to change the file name

    Take the DB offline

    Move the file

    Bring the DB back online

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 17, 2011 at 12:08 am

    #1325605

    Hi Gail,

    Any thoughts about pro et contra for both solutions?

    Detach/attach seemed more ‘natural’ to me, but would like to understand why you advised different?

    Believe that there was a reason.

    Respect your posts (and not only posts) very much and taking every opportunity to learn something.

    Thanks

    Vera

  • May 17, 2011 at 12:19 am

    #1325609

    Thanks allot for your kind co-operation,

    I tried to move the secondary file through ALTER DATABASE MODIFY command, after copying the .ndf file to the destination folder am not able to bring back the database online.

    It is throwing an error Access Denied.Alter database statement failed.

  • May 17, 2011 at 2:09 am

    #1325640

    srinath.vanama (5/17/2011)

    Thanks allot for your kind co-operation,

    I tried to move the secondary file through ALTER DATABASE MODIFY command, after copying the .ndf file to the destination folder am not able to bring back the database online.

    It is throwing an error Access Denied.Alter database statement failed.

    Most likely the SQL Server service account does not have full permission on the new folder.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 17, 2011 at 2:10 am

    #1325641

    ModestyBlaise84 (5/17/2011)

    Detach/attach seemed more ‘natural’ to me, but would like to understand why you advised different?

    Doesn't change the DB id, doesn't potentially mess up service broker, CDC or any of the other server-level tools. Can be done on a database involved in replication.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 17, 2011 at 2:40 am

    #1325643

    ModestyBlaise84 (5/17/2011)

    Hi Gail,

    Any thoughts about pro et contra for both solutions?

    Detach/attach seemed more ‘natural’ to me, but would like to understand why you advised different?

    Believe that there was a reason.

    Respect your posts (and not only posts) very much and taking every opportunity to learn something.

    Thanks

    Vera

    Pls refer this link.

    http://www.sqlservercentral.com/articles/Administration/65896/

    M&M

  • May 17, 2011 at 3:20 am

    #1325649

    Thank you 🙂

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

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