change the data, temp and log directories (SQL 2016) from C drive to say F drive (data disks) via Powershell or DSC.
Question
Saturday, October 15, 2016 1:12 PM
Hi there,
how to change the data, temp and log directories from C drive to say F drive (data disks) via Powershell or DSC. I am spinning a SQL VM in Azure but I notice that these directories are on C drive. Ideally a VM extension (DSC or powershell script) is what I am looking for.
thank you
All replies (7)
Wednesday, October 19, 2016 5:15 AM ✅Answered
Hi Ariaz10,
Firstly, you can use the following PowerShell Script to change the data, log and backup directories of SQL Server 2016 default instance. To change data, log and backup directories of SQL Server 2014 default instance, just change RegKeyPath from "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer"to "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer". If you are using named instance, replace MSSQLSERVER with instance name in RegKeyPath.
$DataRegKeyPath = "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer"
$DataRegKeyName = "DefaultData"
$DataDirectory = " F:\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\MSSQL13.MSSQLSERVER\MSSQLServer"
$LogRegKeyName = "DefaultLog"
$LogDirectory = " F:\Log"
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\MSSQL13.MSSQLSERVER\MSSQLServer"
$BackupRegKeyName = "BackupDirectory"
$BackupDirectory = "F:\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
}
Secondly, to move SQL Temp database and log files using PowerShell, please review the steps in this blog: https://superwidgets.wordpress.com/2014/06/05/moving-sql-temp-database-and-log-files-using-powershell/.
If you don’t have SQLPS module of SQL Server 2014 or SQL Server 2016, you can download the following components from this SQL Server 2014 feature pack and SQL Server 2016 feature pack and install them.
1.Microsoft System CLR Types for Microsoft SQL Server (SQLSysClrTypes.msi)
2.Microsoft SQL Server Shared Management Objects (SharedManagementObjects.msi)
3.Microsoft Windows PowerShell Extensions for Microsoft SQL Server(PowerShellTools.msi)
After performing the above steps, please restart your SQL Server Service in SQL Server Configuration Manager.
Thanks,
Lydia Zhang
Lydia Zhang
TechNet Community Support
Sunday, October 16, 2016 1:26 AM | 1 vote
Hi Ariaz,
refer link for you.
Please click Mark As Answer if my post helped.
Sunday, October 16, 2016 4:05 PM
Thank dinesh. appreciate it. I want to make sure, basically we need to change the directory path (by default SQL puts in the C drive) but we need to change that to make sure Data, Log and Temp directory path going forward is in new location (F:\Data, F:\Temp and F:\Log) etc.,
Monday, October 17, 2016 12:29 AM
Hi Ariaz,
You can change path using alter database command.
Next creation log and <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="b05739c5-0a40-4b87-8857-8c36873ffd89" id="eddef066-f23b-46ba-9cb7-bb295772d932">mdf</gs> will change by Server-->Database settings--><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="b05739c5-0a40-4b87-8857-8c36873ffd89" id="e8b8d1a5-6543-4975-9320-4c9fbfe99098">mdf</gs><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="b05739c5-0a40-4b87-8857-8c36873ffd89" id="051d8adb-1b42-4df7-848a-ef6e8a7c7dfd">,</gs>log
Please click Mark As Answer if my post helped.
Monday, October 17, 2016 1:01 AM
follow this-
https://sqlandme.com/2013/03/18/sql-server-changing-default-database-location-for-server/
http://logicalread.solarwinds.com/change-default-sql-server-db-locations-tl01/#.WAQhPfl97IU
https://msdn.microsoft.com/en-us/library/dd206993.aspx
for powershell-follow similar way for other data.log files too.
#PSTip Change SQL Server default backup folder location
http://www.powershellmagazine.com/2013/03/14/pstip-change-sql-server-default-backup-folder-location/
but note that there are some installations files still requires and that is always on the C drive,so dont do any actions and let it be.
the above question you raised by like when ever if any new database creation the default path for MDF,LDF will go to the same path where it is poinintg(in your case it is C).
simiarly when you take backup through the SSMS-by default it picks to the backup location until you explicitly defined so..fyi...
Regards, S_NO "_"
Monday, October 17, 2016 1:25 PM
Hi Lydia, thank you. I wonder what would be the SMO for SQL 2014 and SQL 2016. where could I find it. the example in the link above is for SQL 208R2
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($env:ComputerName)
$server.Properties["BackupDirectory"].Value = "K:\Backup"
$server.Alter()
Monday, October 17, 2016 1:25 PM
thanks but I am looking it do this in powershell for SQL 2016 or SQL 2014.