Command for adding new drives?

Question

Command for adding new drives?

JaybeeSQL

Hall of Fame

Points: 3501
More actions
August 26, 2015 at 4:16 am

#307216

Hi all,
Currently we're running everything on C: but I've convinced management to split out, what's the command to add drives ?
Thanks guys

Viewing 15 posts - 1 through 15 (of 16 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you mean 'add drives'?

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

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 2

GilaMonster (8/26/2015)
What do you mean 'add drives'?

We're adding drives to the database server. 🙂

A better question is, "What is the best procedure to move database system and user databases to new drives?"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That would be a far better question, because there's no SQL command to add drives to a server. 😉

Firstly, you don't want to move the binaries, as that would require a reinstall of SQL. To move database files, ALTER DATABASE ... MODIFY FILE, then take the DB offline, move the files (via Windows Explorer), 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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Google and Books Online are your friends here. Please see the following.

Google or Books Online is your friend here. Please see the following.

https://msdn.microsoft.com/en-us/library/ms345408(v=sql.100).aspx

--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)

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 5

Any drawbacks to detach, move and reattach DB?

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Want a cool Sig (8/26/2015)
Any drawbacks to detach, move and reattach DB?

Read the documentation in the link I provided because the answer is "It Depends".

--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)

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 7

GilaMonster (8/26/2015)
That would be a far better question, because there's no SQL command to add drives to a server. 😉
Firstly, you don't want to move the binaries, as that would require a reinstall of SQL. To move database files, ALTER DATABASE ... MODIFY FILE, then take the DB offline, move the files (via Windows Explorer), bring the DB back online

Was always going to let the binaries stay put on C:\, I think MS still recommend they reside on the same drive as the OS.

Couple of further questions please....

Should the system databases be best moved in any particular order?

Any objection however to scripting out the file move part?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

JaybeeSQL (8/27/2015)
Should the system databases be best moved in any particular order?

To be honest, other than TempDB, I'd probably leave the others alone for now. Moving master is not the same as moving other databases, model and MSDB are also slightly more involved

Any objection however to scripting out the file move part?

Scripting with what? SQL can't do the file moves.

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

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 9

GilaMonster (8/27/2015)
JaybeeSQL (8/27/2015)
Should the system databases be best moved in any particular order?
To be honest, other than TempDB, I'd probably leave the others alone for now. Moving master is not the same as moving other databases, model and MSDB are also slightly more involved
I hear you. According to MS it's straightforward 🙂
Any objection however to scripting out the file move part?
Scripting with what? SQL can't do the file moves.

DOS, there are gonna be quite a few destination folders.

--edit - dumb question, disregard 🙂

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

JaybeeSQL (8/27/2015)
GilaMonster (8/27/2015)
JaybeeSQL (8/27/2015)
Should the system databases be best moved in any particular order?
To be honest, other than TempDB, I'd probably leave the others alone for now. Moving master is not the same as moving other databases, model and MSDB are also slightly more involved
I hear you. According to MS it's straightforward 🙂
Any objection however to scripting out the file move part?
Scripting with what? SQL can't do the file moves.
CMDShell, there are gonna be quite a few destination folders.

That won't work because the server will be active to use it, which means that the files will be locked by SQL Server and won't be able to be moved.

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

GilaMonster (8/27/2015)
JaybeeSQL (8/27/2015)
Should the system databases be best moved in any particular order?
To be honest, other than TempDB, I'd probably leave the others alone for now. Moving master is not the same as moving other databases, model and MSDB are also slightly more involved

+1000 to that.

--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)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

JaybeeSQL (8/27/2015)
GilaMonster (8/26/2015)
That would be a far better question, because there's no SQL command to add drives to a server. 😉
Firstly, you don't want to move the binaries, as that would require a reinstall of SQL. To move database files, ALTER DATABASE ... MODIFY FILE, then take the DB offline, move the files (via Windows Explorer), bring the DB back online
Was always going to let the binaries stay put on C:\, I think MS still recommend they reside on the same drive as the OS.
Couple of further questions please....
Should the system databases be best moved in any particular order?
Any objection however to scripting out the file move part?

Again, see the link I provided on how to move system databases for details.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Jeff Moden (8/27/2015)
JaybeeSQL (8/27/2015)
GilaMonster (8/27/2015)
JaybeeSQL (8/27/2015)
Should the system databases be best moved in any particular order?
To be honest, other than TempDB, I'd probably leave the others alone for now. Moving master is not the same as moving other databases, model and MSDB are also slightly more involved
I hear you. According to MS it's straightforward 🙂
Any objection however to scripting out the file move part?
Scripting with what? SQL can't do the file moves.
CMDShell, there are gonna be quite a few destination folders.
That won't work because the server will be active to use it, which means that the files will be locked by SQL Server and won't be able to be moved.

Not if he's taken the user databases offline, SQL does not need to be shut down to move user databases around.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

GilaMonster (8/27/2015)
Jeff Moden (8/27/2015)
JaybeeSQL (8/27/2015)
GilaMonster (8/27/2015)
JaybeeSQL (8/27/2015)
Should the system databases be best moved in any particular order?
To be honest, other than TempDB, I'd probably leave the others alone for now. Moving master is not the same as moving other databases, model and MSDB are also slightly more involved
I hear you. According to MS it's straightforward 🙂
Any objection however to scripting out the file move part?
Scripting with what? SQL can't do the file moves.
CMDShell, there are gonna be quite a few destination folders.
That won't work because the server will be active to use it, which means that the files will be locked by SQL Server and won't be able to be moved.
Not if he's taken the user databases offline, SQL does not need to be shut down to move user databases around.

Understood and absolutely agreed. I was talking about the system databases, which seemed to be the concern for moving.

--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)