I was recently approached by my manager to free up a Direct Attached Storage (DAS) device from a test server so that it could be leveraged in a higher environment.
To my amazement, both the system databases and binary executables were installed on the drive I needed to move.
Today, we are going to concentrate on moving the binary executables from Drives D: and H:. The solution to this technical problem is to trick the database engine in thinking nothing has changed. In reality, two new drives will be carved out of SAN to replace the DAS drives.
The actual task list is below.
1 – Turn off all services via the SQL Server Configuration manager.
2 – Use disk manager to reassign drives D: and H: to X: and Y:.
3 – Allocate two new SAN drives, initalize them as D: and H:.
4 – Use a disk tool to copy data from old drives to new drives.
5 – Turn on all services via the SQL Server Configuration manager and test the system.
6 – Delete volumes X: and Y: via disk manager. Turn off server and remove the DAS device.
If you are not familiar with the disk manager, you should be since it allows you to add/delete volumes, format new volumes or change drive letters of existing volumes. Below is a screen shot of the disk manager snap-in.
There are many different ways to copy whole volumes of information from one drive to another.
1 – The XCOPY command line utility is superior compared to COPY since the /Z option allows for network restartable operations.
2 – The ROBOCOPY command line utility is superior compared to XCOPY since the /SEC option preserves NTFS permissions.
3 – The SYNCTOY windows application is superior compared to XCOPY since it has a nice graphical interface and logs file copy operations. However, the software is made for end user files and does not handle NTFS permissions.
4 – The ROBOCOPY GUI windows application is the best of both worlds. It has a nice graphical interface, logs file copy operations and handles NTFS permissions. However, the application is only single threaded, one file copy at a time.
5 – The RICHCOPY windows application is the best program out there. It provides all the functions of ROBOCOPY GUI as well as being a multi-threaded application.
I ended up using the RICHCOPY application to move the data from one drive to another.
To recap, if you ever find yourself in a situation in which the SQL Server binary files are install on a hard disk that needs to be moved, you have two options.
The first solution is uninstall and reinstall SQL Server. All system objects like jobs, linked servers, security, etc., will need to be scripted out and re-installed. All user database will need to be reattached. In short, a very lengthy process.
The second solution is to use auxiliary storage in place of the storage you want to move. This process is a little shorter than the first one and does not involve any TSQL scripting.