September 21, 2018 at 9:29 am
Hi, I am in a situation of refreshing a lower environment database with production backup. But prod db size is huge now (close to 3 TB). Our infra team saying they cant expand the sql drive(E:\) which has user databases .mdf files and also it has master, msdb, model mdf files. tempdb files in different drive.
we want to add new big drive(Z:\) with different letter. shutdown sql. and move sql data files from existing drive to new drive. rename old drive. rename new drive to use old drive letter.
my question - once sql is restarted all databases will come back online using same drive letter path? correct?
September 21, 2018 at 9:35 am
HBK_4700 - Friday, September 21, 2018 9:29 AMHi, I am in a situation of refreshing a lower environment database with production backup. But prod db size is huge now (close to 3 TB). Our infra team saying they cant expand the sql drive(E:\) which has user databases .mdf files and also it has master, msdb, model mdf files. tempdb files in different drive.we want to add new big drive(Z:\) with different letter. shutdown sql. and move sql data files from existing drive to new drive. rename old drive. rename new drive to use old drive letter.
my question - once sql is restarted all databases will come back online using same drive letter path? correct?
You can DETACH the database, move the files to any drives you like, then just ATTACH the database by selecting the files and everything should work.
For moving the tempdb file you need to alter the database to specify a new location then restart the SQL Sever service.
See also: https://www.ryanjadams.com/2011/07/how-to-move-msdb/
September 21, 2018 at 9:45 am
my question - once sql is restarted all databases will come back online using same drive letter path? correct?
Yes, the E:\ paths will be the same, if I've understood your q correctly. SQL won't know -- nor care -- that the logical "E" drive is now a different physical drive: as long as the drive and path names are the same, it should work fine.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 21, 2018 at 11:11 am
Thanks ScottPletche!! I am expecting the same.
September 21, 2018 at 12:37 pm
We are moving many servers to a new SAN. We simply added a set of drives to each server that match the existing drive.
So, if the data drive was D:, we created an E:
Shut down SQL, copied everything from D to E, renamed D to X, renamed E to D.
Re-started SQL, everything was happy.
Make sure you set the permissions to the new drive!
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply