August 2008 - Posts - Andrew Calvett

August 2008 - Posts

Changing the Data Files Location after Installation
10 August 2008 10:28

The other day i wanted to change the "Data Files" location for a 2005 database engine installation and a 2005 Analysis Services installation which you can specify under the advanced options during installation. I quickly found out that there appears to be no documented ways to do this other than uninstall SQL Server and install again specifying a new location for data files. It's also not as simple as moving your system databases as "Data files" covers things like server errors logs, sql agent logs, replication default directory etc. So, as the uninstall route was not one i was prepared to go down i sat down and worked out how to do it and below are the results.

Analysis Services

  • At the root level, copy the existing data folders to the new location.
  • Open the registry editor and navigate to HKLM\SYSTEM\CurrentControlSet\Services\MSSqlServerOLAPService and edit the imagepath value. You will see a switch –s and after that a path, change that path to the new path.
  • Locate the configuration file msmdsrv.ini in the config directory and open it. Update all references to the old location with the new location.

If you have not copied any cube data then there is nothing more to do. If you have copied cube data and you know the cubes are using all default storage locations then you do not need to do anything else but if you are not using default locations then they must exist otherwise the cube will error when starting. To update the locations you must find all the relevant xml files and update the paths.

Database Engine

  • Setup the directory structure in the new location.
  • Follow knowledge base article KB224071 to move all your system databases and any user databases (don't forget to do the steps for the resource db).
  • Stop the fulltext service.
  • Fire up the registry editor and go to HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\MSSEARCH\LANGUAGE. Now, work through each key and change the paths for NoiseFile & TsaurusFIle to the new location
  • Go to HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\MSSQLServer and change the value for "FullTextDefaultPath" to the new location.
  • Move the contents of the FTDATA directory to its new location
  • Using the SQL Server configuration manager update the path for Dump Directory and the error log (part of the startup parameters, screenshot below)

  • In HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\MSSQLServer change the value for BackupDirectory and defaultlog to new location
  • In HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\Setup change the value for SQLDataRoot to the new path. (This updates the Data Path value that is greyed out in the SQL Server configuration manager).
  • In HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\Replication change the value for WorkingDirectory to the new path.
  • In HKLM\SOFTWARE\MICROSOFT\MICROSOFT SQL Server\MSSQL.1\SQLServerAgent change the value for ErrorLogFile and WorkingDirectory to the new path.
  • Finally move the remaining data in the original location to the new location and rename the root directory. Once you have verified everything is ok (check SQL error logs etc) you can delete it.

Notes

  1. Some of the paths will vary when instances are installed. For example the directory path part MSSQL.1 could be MSSQL.2 or MSSQL.3 depending on install order and number of instances.
  2. Some of the registry paths may contain an instance name if you have a named instance.
  3. When you finish you may need to enable the service broker in the msdb database again as i found this necessary on 1 of my installs. The SQL error log will reveal if this is necessary.

 

by ACALVETT | with no comments
Filed under: , , ,