Detach - reattach database on a new drive on same server

  • Will detaching a database on one drive (say d:drive) and relocating it to a new drive on the same server (say g: drive) and reattaching it cause any issues that anyone is aware of.  I'm of the mind it wouldn't, but since this is a production server I need to be sure.

    Growing the current drive is not an option since it was formatted using MBR and is at the 2tb limit.

  • I’d do it as an alter database modify file, offline database, move the file, online the database.

    This is just to get around the permission changes which happen with a detach and it locking the file to who detached it so have been burned by that with standard and admin accounts for various systems in the past.

     

     

  • Ant-Green wrote:

    I’d do it as an alter database modify file, offline database, move the file, online the database.

    This is just to get around the permission changes which happen with a detach and it locking the file to who detached it so have been burned by that with standard and admin accounts for various systems in the past.

    Hmm, I'll look into that.  Thanks for that 🙂

  • I do the same as what Anthony posted.

    There might be at least one caveat to be aware of.  My disclaimer is that I've not had to try it and I'm proposing it as something that needs to be checked.  If you RESTORE a database from another system, I DO know that it will disable a SET TRUSTWORTHY ON if you needed such a setting for some reason.  You will need to set that setting again once the database has been restored.

    I suspect that might also be the case for when you do the offline move thing but I don't know for sure.  I'm just saying it out loud because you should check it if you need the setting to be on.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    I do the same as what Anthony posted.

    There might be at least one caveat to be aware of.  My disclaimer is that I've not had to try it and I'm proposing it as something that needs to be checked.  If you RESTORE a database from another system, I DO know that it will disable a SET TRUSTWORTHY ON if you needed such a setting for some reason.  You will need to set that setting again once the database has been restored.

    I suspect that might also be the case for when you do the offline move thing but I don't know for sure.  I'm just saying it out loud because you should check it if you need the setting to be on.

     

    Hey, that's a great tip.  I plan to test this process to a fare thee well before unleashing on a system that if it goes bad -- could be a resume generating event lol

     

    Thanks folks 🙂

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

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