Wednesday, March 5, 2014

Changing the Default Installation Paths for SQL Server

There are often times where you need to move all the files and SQL Server objects from the default installation paths - the path where system databases were installed, default traces are stored, error logs reside, etc.  I found no single resource that addressed each and every item I needed to move, which led to this blogging opportunity.  I have to do this twice in the coming days (new SAN, new LUN's/drives) and this is part of the migration process to retire the old SAN.

I prefer to keep the default folder structure in place, which means the following exist in a common folder: DATA, FTData, JOBS, Log and repldata.

Note: Some require editing of the registry. Proceed with great caution!

Move the SQL Server Agent error log

USE msdb
GO

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'<common folder path>\Log\SQLAGENT.OUT'
GO


Move the SQL Server error log

Note: moving the SQL Server error log also changes the location of the default traces.
  1. Open SQL Server Configuration Manager on the server/node
  2. Click SQL Server Services
  3. Right click on SQL Server service and click Properties
  4. Click the Advanced tab (in SQL 2012/2014 click on the Startup Parameters tab)
  5. Change the path following the -e parameter
    • -d'<common folder path>\Log\ERRORLOG'
    • In SQL 2012/2014, be sure to click the Update button or the changes will not stick!
  6. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the Dump Directory Path

SQL Server creates dump files (files with a .mdmp extention) by default under the log folder.  These dump files are generally created when SQL Server encounters an exception or access violation.
  1. Open SQL Server Configuration Manager on the server/node
  2. Click SQL Server Services
  3. Right click on SQL Server service and click Properties
  4. Click the Advanced tab
  5. Type in the new path
    • '<common folder path>\Log'
  6. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the Data Path

Changing the data path requires editing a registry entry.  Note this is not the same path as the "Database default locations > Data" location found when reviewing the Properties of your instance.
  1. Go to the server and open the registry (Start > Run, and type in "regedit")
  2. Browse to HKLM\SOSFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
  3. Change the value of SQLDataRoot to the new location
    • '<common folder path>\DATA'
  4. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the SQL Agent Jobs Path

Changing the SQL Agent jobs path requires editing a registry entry.  This path is used by various features under the hood.
  1. Go to the server and open the registry (Start > Run, and type in "regedit")
  2. Browse to HKLM\SOSFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent
  3. Change the value of WorkingDirectory to the new location
    • '<common folder path>\JOBS'
  4. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the Full-text Default Path

Changing the full-text default path requires editing a registry entry.
  1. Go to the server and open the registry (Start > Run, and type in "regedit")
  2. Browse to HKLM\SOSFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
  3. Change the value of FullTextDefaultPath to the new location
    • '<common folder path>\FTData'
  4. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the Default Snapshot Path for Replication

Whether or not you are currently using replication, it's a good idea to make sure all resources are reconfigured accordingly.  I found the easy way to do this was by running the following script:

USE distribution
EXEC sp_changedistpublisher
    @publisher = 'Database Instance'
    @property = 'working_directory'
    @value = '<common folder path>\repldata'

Note that you may have to edit existing publications:

Move msdb and model databases

Run the following query to get the logical file names.
USE master
GO

SELECT  DB_NAME(database_id) AS "Database"
       ,name AS "Logical Name"
       ,physical_name AS "Physical Location"
       ,state_desc AS "State"
FROM    sys.master_files
WHERE   database_id IN ( DB_ID(N'msdb'),DB_ID(N'model') )
ORDER BY DB_NAME(database_id);
GO

Plug in the logical file names and new paths do the data and log files, respectively.  It's likely you will only need to adjust the common folder paths - the logical file names are probably as shown below.
USE master
GO

ALTER DATABASE model
MODIFY FILE (NAME = 'modeldev', FILENAME = '<common folder path>\DATA\model.mdf');
GO

ALTER DATABASE model
MODIFY FILE (NAME = 'modellog', FILENAME = '<common folder path>\DATA\modellog.ldf');
GO

ALTER DATABASE msdb
MODIFY FILE (NAME = 'MSDBData', FILENAME = '<common folder path>\DATA\MSDBData.mdf');
GO

ALTER DATABASE msdb
MODIFY FILE (NAME = 'MSDBLog', FILENAME = '<common folder path>\DATA\MSDBLog.ldf');
GO

After running the above script, stop the instance of SQL Server and move the physical files to their new location.  Once moved, restart the instance.  You can run the first query again to verify they have moved as intended.

Move master database

  1. Open SQL Server Configuration Manager on the server/node
  2. Click SQL Server Services
  3. Right click on SQL Server service and click Properties
  4. Click the Advanced tab (in SQL 2012/2014 click on the Startup Parameters tab)

  1. Change the path following the -d parameter for the new data file location
    • -d'<common folder path>\DATA\master.mdf'
    • In SQL 2012/2014, be sure to click the Update button or the changes will not stick!
  2. Change the path following the -l parameter for the new log file location
    • -l'<common folder path>\DATA\master.ldf'
    • In SQL 2012/2014, be sure to click the Update button or the changes will not stick!
  3. Stop the instance of SQL Server
  4. Move the physical files to their new location
  5. Restart the instance of SQL Server
  6. If you are working on a cluster, be sure to make this adjustment on all nodes
Run the following query to verify they have moved as intended.
USE master
GO

SELECT  DB_NAME(database_idAS "Database"
       ,name AS "Logical Name"
       ,physical_name AS "Physical Location"
       ,state_desc AS "State"
FROM    sys.master_files
WHERE   database_id DB_ID(N'master') )
ORDER BY DB_NAME(database_id);
GO