Microsoft SQL Server 2014: Change Default Data, Log, and Backup Directories via T-SQL or PowerShell

If you need to change the default data, log, and/or backup directories on a SQL 2014 MS SQL Server default instance after installation, you can use these three SQL commands:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer'
	, N'DefaultData'
	, REG_SZ
	, N'K:\SQL\Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer'
	, N'DefaultLog'
	, REG_SZ
	, N'L:\SQL\Logs'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer'
	, N'BackupDirectory'
	, REG_SZ
	, N'Z:\SQL\Backups'
GO

If you are using a named instance, you will want to replace the MSSQLSERVER with the instance name. For newer MS SQL Server versions, you will need to change the 12 to the correct version.

For earlier versions of SQL, you would use the following (Note the change in registry location):

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\MSSQLServer\MSSQLServer'
	, N'DefaultData'
	, REG_SZ
	, N'K:\SQL\Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\MSSQLServer\MSSQLServer'
	, N'DefaultLog'
	, REG_SZ
	, N'L:\SQL\Logs'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\MSSQLServer\MSSQLServer'
	, N'BackupDirectory'
	, REG_SZ
	, N'Z:\SQL\Backups'
GO

You can also do this via PowerShell:

$DataRegKeyPath = "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer"
$DataRegKeyName = "DefaultData"
$DataDirectory = "K:\SQL\Data"
If ((Get-ItemProperty -Path $DataRegKeyPath -Name $DataRegKeyName -ErrorAction SilentlyContinue) -eq $null) {
  New-ItemProperty -Path $DataRegKeyPath -Name $DataRegKeyName -PropertyType String -Value $DataDirectory
} Else {
  Set-ItemProperty -Path $DataRegKeyPath -Name $DataRegKeyName -Value $DataDirectory
}
 
$LogRegKeyPath = "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer"
$LogRegKeyName = "DefaultLog"
$LogDirectory = "L:\SQL\Logs"
If ((Get-ItemProperty -Path $LogRegKeyPath -Name $LogRegKeyName -ErrorAction SilentlyContinue) -eq $null) {
  New-ItemProperty -Path $LogRegKeyPath -Name $LogRegKeyName -PropertyType String -Value $LogDirectory
} Else {
  Set-ItemProperty -Path $LogRegKeyPath -Name $LogRegKeyName -Value $LogDirectory
}
 
$BackupRegKeyPath = "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer"
$BackupRegKeyName = "BackupDirectory"
$BackupDirectory = "Z:\SQL\Backups"
If ((Get-ItemProperty -Path $BackupRegKeyPath -Name $BackupRegKeyName -ErrorAction SilentlyContinue) -eq $null) {
  New-ItemProperty -Path $BackupRegKeyPath -Name $BackupRegKeyName -PropertyType String -Value $BackupDirectory
} Else {
  Set-ItemProperty -Path $BackupRegKeyPath -Name $BackupRegKeyName -Value $BackupDirectory
}

Hope this helps!

This entry was posted in MS SQL Server, PowerShell, T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *