asked on

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!

ASKER CERTIFIED SOLUTION

Jax Tan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill H

ASKER

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_bee

@Cobra
Jax did give you steps accept you

Detach
Copy
Change Path
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

Jax Tan

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.

Bill H

ASKER

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 Pletcher

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.