Move SQL DB to different drive on server

Hi, i have a vmware server with C:\ and D:\ Drive, we installed SQL Server 2014 SP2 on the server, and the database files are on the D: drive. Now, i just added SSD drives to the ESX host and a new datastore, so im attching a new E:\ drive to the server. I want to move the SQL database from D: to E: for performance. What is involved with moving this to the other drive, would it affect an application (We use solidworks CAD) Thanks!
LVL 4
Cobra25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jax TanCommented:
To do this, a short downtime is necessary.
First, stop your database.
Next, right click to detach your database.
The, read the properties of the database and find out where your database files are stored.
Copy the database files to your preferred drive.
Now, attach your database again.
Start it up.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cobra25Author Commented:
I don't mind downtime, but doesn't answer my questions if just moving it breaks the application generally. Also I don't see any steps on how to move it.
0
yo_beeDirector of Information TechnologyCommented:
@Cobra
Jax did give you steps accept you

  1. Detach
  2. Copy
  3. Change Path
  4. Attach
Are you moving all DB's, Just system or Just users.
Here are the steps in the links
This is for system DB's (the 4)
https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017

User DB's
https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jax TanCommented:
This comment is not really related, so you can ignore if not useful.

If you are using an ESX Server, i assume your SQL Database is running as a VM on ESX. That being said, its actually worthwhile to migrate the entire VM to the SSD datastore. Although SQL database's storage on SSD makes things faster, it would give more significant improvement the entire OS is moved over to the SSD.
0
Cobra25Author Commented:
Jax - i agree, unfortunately i cannot go that route since i did not get approval, i think the main with SQL (outside of cpu + ram, would be storage speed right)?
0
Scott PletcherSenior DBACommented:
First, do not detach!  That is an old way (SQL 2000 and prior) of moving files to new drives.  Be aware: if the db has certain errors, it may not re-attach; that could be a disaster for you.

Here's the more-current ALTER method:

1) Alter all file paths to their new locations.  For example:
--you can get the logical file names from running the EXEC <db_name>.sys.sp_helpfile command, the first column ("name")
ALTER DATABASE <db_name> MODIFY FILE ( NAME = <logical_file_name>,
    FILENAME = 'E:\new\path\filename.mdf' )
ALTER DATABASE <db_name> MODIFY FILE ( NAME = <logical_file_name2>,
    FILENAME = 'E:\new\path\filename2.ndf' )
ALTER DATABASE <db_name> MODIFY FILE ( NAME = <logical_file_name_log>,
    FILENAME = 'E:\new\path\filename_log.ldf' )

2) Take <db_name> offline.
ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK AFTER 300 SECONDS; --or less if you don't want to wait up to 5 mins.

3) *Copy* the files from the old location to the new location.

4) Bring <db_name> online.
ALTER DATABASE <db_name> SET ONLINE;

After you've verified the new file sizes, and the db is up and running for a while, you can delete the old files.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.