How to change entire SQL server location from C:\ program file to another drive?

Question

scenario:

one of my client was asked me to move entire program files folder from C:\ drive E:\ drive and change the all databases location to E:\ drive. They have copied entire program file folder through third party tool and pasted it on E:\ drive and they asked me to change path of databases and SQL installation to E:\ drive. Now how to change all databases paths and how to do this kind of activity? i know how to move system and user databases manually from one drive to another but i haven't face this king of scenario. Please suggest me how to do this task without any errors.

Answer 1

Trying to move a SQL Server installation in this manner is very dangerous, time consuming, and unsupported. If someone did this to my server I would not sleep well.

A better approach would be to:

1. Take note of exactly what service pack and CU level is running, and any non-default configuration items in SQL Server Configuration Manager
2. Backup all system and user databases
3. If you need to move user database files, detach the user databases and move the files
4. Uninstall SQL Server
5. Install SQL Server with the new drive configuration
6. Install service packs and/or cumulative updates to get to the version noted in step 1
7. Restore the master and msdb databases
8. Apply non-default configurations in SQL Server Configuration Manager
9. If the user database files have been moved, attach them

Please note that these are general steps and don't account for things specific to your environment, such as shutting down application servers, etc.

Answer 2

This is unnecessary. The C drive is the drive that Windows is installed on, and all programs’ binary files are stored here. Please do not confuse the SQL Server Instance binaries with the software for SQL Server. Most of the storage is related for the Instance and anyways, changing the software installation location can cause problems downstream. You are not an OS architect and your clients may only be concerned with the storage?

Instead, fulfill the concerns of your stakeholders by moving the SQL Server Instance to a new drive and leave OS operating system configurations to Microsoft.

The process will require downtime with some steps, but you can limit it if you have everything prepared (use a test environment! Doesn’t have to be too fancy just to mimic the steps). The following links/steps should be helpful:

1. system database

   • Move System Databases - Microsoft Docs

2. user databases and all log files

   • See Dave Pinal’s How to move SQL Server mdf and ldf files -blog.sqlauthority
   • or the Docs: Move User Databases - Microsoft Docs

3. Error log locations

   • See How to change SQL Server ERRORLOG location - Sqlandme

4. Agent log locaton

   • see How to change the sql Server Agent Log file path - mssqltips

Please note that primary log files and all master data files (mdf) require downtime to move.