Moving ALL SQL Server 2000 Data Structures

By Jasper Smith on 7 July 2002 | Tags: Administration

This article expands on the following two Microsoft Knowledge Base Articles: INF: Moving SQL Server Databases to a New Location with Detach/Attach (Q224071) and INF: How to Move, Copy, and Back Up Full-Text Catalog Folders and Files (Q240867). Whilst these articles focus on moving the actual database files and full text catalogs, what happens if you want to move ALL the disk structures associated with a SQL Server installation from one drive to another on the same server? On a 24/7 production server where minimising downtime is imperative how can we move the folder structures, SQL Error log and SQL Agent log to a new drive?

Our server called TEST has the SQL program files installed on its C: drive, user databases on an internal RAID array D: and the system databases and folder structures on an external array E: We need to remove the external array E: and thus need to move all our SQL Data Structures from the E: drive to the D: drive. For this example we are using a named instance TEST\TEST2. Assuming we have already moved all our databases and Full Text Catalogs as per the KB articles we now need to remove the remaing disk infrastucture. Replication is NOT enabled. SQL Agent is stopped.

For our instance TEST\TEST2 we have a folder on E: called MSSQL$TEST2 which contains the following subdirectories:

BACKUP - default backup location
DATA - default database file location
FTDATA - default FT Catalog location and FTS system config files
JOBS - SQL Agent working area
LOG - SQL Server and SQL Agent Logs
REPLDATA - Replication working area

These are the standard on-disk folder structures installed by SQL Server.

Follow these steps to complete the move from E:\MSSQL$TEST2 to D:\MSSQL$TEST2:

Amend the SQL Error Log startup parameter to reflect the new location. In EM right click on the instance and choose Properties and Click on Startup Parameters Remove the -e parameter and add the new one to reflect the new location, in our case -eD:\MSSQL$TEST2\LOG\ERRORLOG
Stop all SQL Services and MSSearch Service and backup the registry.
Copy the E:\MSSQL$TEST2 folder and all its subfolders and contents to D:\MSSQL$TEST2
Now comes the "fun" part - the Registry! The following keys are changed :- (N.B. the actual key names vary depending on the instance name)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\TEST2\MSSQLServer
BackupDirectory = D:\MSSQL$TEST2\BACKUP
FullTextDefaultPath =D:\MSSQL$TEST2\FTDATA

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\TEST2\Replication
WorkingDirectory = D:\MSSQL$TEST2\REPLDATA

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\TEST2\Setup
SQLDataRoot = D:\MSSQL$TEST2

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\TEST2\SQLServerAgent
ErrorLogFile = D:\MSSQL$TEST2\Log\SQLAGENT.OUT
WorkingDirectory = D:\MSSQL$TEST2\JOBS

You can ignore the rest if you have not installed Full Text Search, however if it is installed, even if you do not use it then Step 5 changes must be done.
The following keys are changed :-(N.B. the actual key names will vary)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Applications\SQLServer$TEST2
SearchDirectory = D:\MSSQL$TEST2\FTDATA\SQLServer$TEST2\Projects

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer$TEST2
ApplicationPath = D:\MSSQL$TEST2\FTDATA\SQLServer$TEST2\
DefaultProjectPath = D:\MSSQL$TEST2\FTDATA\SQLServer$TEST2\Projects

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer$TEST2
ApplicationPath = D:\MSSQL$TEST2\FTDATA\SQLServer$TEST2\
If you have Full Text catalogs then they each contain a couple of keys that need changing NB On our server we use only one small Full Text Catalog that takes 2 minutes to build so I opted to drop it and recreate it in the new location to avoid the following changes however with much larger catalogs that can take hours (or days) to build this would not be an option.
For each Full Text Catalog defined, change the following Keys (in this example our catalog is SQL0000500005)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gather\SQLServer$TEST2\SQL0000500005
StreamLogsDirectory = D:\MSSQL$TEST2\FTData\SQLServer$TEST2\GatherLogs

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\ApplicationsSQLServer$TEST2\Projects\SQL0000500005
WorkingDirectory = D:\MSSQL$TEST2\FTData\SQL0000500005

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer$TEST2\SQL0000500005
ProjectPath = D:\MSSQL$TEST2\FTData\SQL0000500005
Start All SQL Services and MSSearch and check that everything works !!

So with a little bit of registry surgery we have our SQL Server up and running again with no reference to E: These registry changes can be scripted beforehand and you can use numerous methods to apply them e.g. .reg file which you merge, vbs script using WScript Shell Regwrite method, REGFIND.EXE that is part of the NT4 and 2000 Resource Kits can be used to search and replace registry entries. In practice I choose to do this by hand as its still a very quick process and can be completed in a couple of minutes.

DISCLAIMER : This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, check the following MS Knowledge Base article Description of the Microsoft Windows Registry. The techniques described above are NOT supported by Microsoft or the author, however they have been tested on Windows NT4 Server SP6,Windows 2000 Server SP2,Windows 2000 Profesional SP2 with SQL 2000 Standard, Developer,Enterprise and Desktop versions on SP2 and with no Service Pack. It has NOT been tested on other configurations or with Replication enabled however it has been tested in a MSX environment.