Move SQL DB to different drive on server

Cobra25
Cobra25 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
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.

Author

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.
yo_beeDirector of Information Technology

Commented:
@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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
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.

Author

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)?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial