2

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.

  • 4
    Moving the SQL Server program files like that is both completely unsupported and pretty pointless. – David Browne - Microsoft Dec 20 '18 at 16:04
  • 2
    Backup all databases, uninstall, reinstall into a new location and restore the databases into a new drive. – McNets Dec 20 '18 at 16:59
5

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.

|improve this answer|||||
  • 1
    This would be the equivalent of a server migration – clifton_h Dec 21 '18 at 21:20
1

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

  2. user databases and all log files

  3. Error log locations

  4. Agent log locaton

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

|improve this answer|||||
  • I am asking seriously. Can you really read from that sql"authority" site? I'd have a headache after a few minutes by all those ads and the fancy fonts. – ypercubeᵀᴹ Dec 21 '18 at 21:29
  • Looks fine on my pc and mobile phone. I would guess your pc has a lot of ads that are shoving themselves on your page. Knowing the site, He uses content in the center and ads are to the right of content. And really, I have the doc link too – clifton_h Dec 21 '18 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.