Detach - reattach database on a new drive on same server

Question

Detach - reattach database on a new drive on same server

Stephen Rybacki

Hall of Fame

Points: 3402
More actions
August 24, 2021 at 5:31 pm
Go to Answer
#3923585
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.
- This topic was modified 3 years, 3 months ago by Stephen Rybacki.
Ant-Green

SSC Guru

Points: 113507
More actions
August 24, 2021 at 5:38 pm
Answer
#3923592

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.

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

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

Stephen Rybacki Hall of Fame Points: 3402 More actions · Answer 1

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 🙂

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 2

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)

Stephen Rybacki Hall of Fame Points: 3402 More actions · Answer 3

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 🙂