Post reply

Moving DB file

  • October 27, 2020 at 5:28 pm

    #3801778

    I have Always ON setup between Server_A and Server_B (Primary server is Server_A and DR server is Server_B)

    I have DB called ManagementDB which has 3 Data file 2 on D drive and 1 on C drive.

    I need to moved Data file from C to d Drive. Since it has Always on Setup I am not able to Detach the DB.

    So how can I can move the data file from C drive to D drive??

    The DB size is 500 GB.

     

    Regards,

    Nita

  • October 27, 2020 at 5:38 pm

    #3801792

    You wouldn't want to detach the db just to move a file.  Just ALTER the file location in SQL Server, take the db offline, copy the file, then bring the db back online.

    However, I don't think you can take a primary db offline that's in an AG either.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 27, 2020 at 6:29 pm

    #3801799

    1. Run the alter file location.  ALTER DATABASE XXXX MODIFY FILE( NAME = filename, FILENAME = 'D:\YourData.mdf')
    2. Fail it over to the secondary
    3. Stop SQL on the now secondary, formerly the primary.
    4. Copy and paste the file from the old to the new location
    5. Rename the file in the old location, just in case!
    6. Start SQL Server
    7. Make sure the database comes online and is syncing.
    8. Fail the AG back

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 3 posts - 1 through 2 (of 2 total)

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