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:

  1. 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

  2. Stop all SQL Services and MSSearch Service and backup the registry.

  3. Copy the E:\MSSQL$TEST2 folder and all its subfolders and contents to D:\MSSQL$TEST2

  4. 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.

  5. 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\

  6. 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

  7. 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.


Related Articles

Advanced SQL Server 2008 Extended Events with Examples (25 May 2009)

Introduction to SQL Server 2008 Extended Events (19 May 2009)

Monitoring SQL Server Agent with Powershell (24 March 2009)

SQL Server Version (29 January 2009)

Scheduling Jobs in SQL Server Express - Part 2 (1 December 2008)

Alerts for when Login Failures Strike (14 July 2008)

Using xp_ReadErrorLog in SQL Server 2005 (12 May 2008)

Moving the tempdb database (5 November 2007)

Other Recent Forum Posts

T-sql - we created Message from app1 and trying to disable from app2 (46m)

SQL select Top 10 records for unique combination of two columns (14h)

SSRS Report Sorting with Grouping Issue (1d)

ORA-01476: divisor is equal to zero (1d)

Create new columns based on min and max values of a record with multiple rows (1d)

Memory Required for Reporting Services 2022 (1d)

Backup sql server large db on cloud (2d)

Selecting x columns but only displaying y columns (2d)

- Advertisement -