Post reply

BAG - how to change disk drive letters for database files

  • August 6, 2019 at 7:27 am

    #3669063

    I've set up a few BAGs on the sql cluster instance. When defining the default locations for data, tlogs and temp database files I use another drive for each e.g. D: for data, E: for tlog and F: for tempdb. The problem is that I used the wrong drive letters, meaning I messed up the consistency in compare to other clusters. Tlog drive letter is OK, but drive letters for data and temp should be opposite than in the current situation.

    How can I (as painless as possible) change the designated drive letters? I was thinking to first change the location for database files to "future" location and then change the drive letters in disk management? Does anyone has some experience with this situation? I am expecting a lot of problems with the process, additionally I need to do it on primary and secondary replica what complicates it even more for me since I am new to working with any kind of HA in SQL Server.

  • August 6, 2019 at 9:20 am

    #3669085

    Simplest way is the following:

    1. remove a db from BAG
    2. alter database and set proper location of files
    3. take database offline and move files accordingly
    4. take database online and verify that it's back and files are ok
    5. add db to BAG  (you know how to do it)

     

  • August 6, 2019 at 1:24 pm

    #3669152

    Thank you for the steps. I will follow them first on secondary location, then after failover I will repeat them on primary location.

    The only thing missing here is when do I actually change the drive letters? After step 2 or after step 3?

  • August 6, 2019 at 3:04 pm

    #3669182

    I doubt BAG will allow you to take db offline for moving files.

    That's why there is Step 1 in the list.

    "when do I actually change the drive letters?"

    It's Step 2 using command alter database.

    Have a look :

    https://www.sqlshack.com/move-sql-database-files-mdf-ldf-another-location/

     

     

     

  • August 6, 2019 at 6:03 pm

    #3669227

    Slight modification...

    1. Remove DB from BAG
    2. Modify locations on primary (ALTER DATABASE)
    3. Take database on primary offline
    4. Move MDF/LDF files to appropriate locations as set in item 2
    5. Bring database on primary online
    6. Backup database on primary - back transaction log, copy to secondary
    7. Restore database on secondary - restore transaction log
    8. Add DB to BAG

    The process of restoring the database to the secondary will place the data/log files in the appropriate locations as defined by the location of the files on the primary.  Since you have to reset the BAG anyways - modifying the locations on the secondary is just extra work...you have to modify the locations, move the files, bring the database online - then restore the database/log which overwrites anything you just did on the secondary.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 5 posts - 1 through 4 (of 4 total)

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