Hello,
This is not the most beautiful script I wrote, but functional it is J
The script is tested on SQL 2014. So I’m not sure if it will work on other SQL versions…
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement') $computer = 'localhost' $sqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $computer #new data (mdf) and log (ldf) file location $NewDataLoc = 'D:\SQLData' $NewLogLoc = 'D:\SQLLog' #check if the path is typed correctly, without a \ at the end if ($NewDataLoc.EndsWith('\') -eq $true){$NewDataLoc = $NewDataLoc.Substring(0,$NewDataLoc.Length-1)} if ($NewLogLoc.EndsWith('\') -eq $true){$NewLogLoc = $NewLogLoc.Substring(0,$NewLogLoc.Length-1)} #In some cases a DBA wants to locate the log file on an other disk. So that is why you have to add the path here as well $NewTempDataLoc = "D:\SQLData" $NewTempLogLoc = "D:\SQLLog" #check if the path is typed correctly, without a \ at the end if ($NewTempDataLoc.EndsWith('\') -eq $true){$NewTempDataLoc = $NewTempDataLoc.Substring(0,$NewTempDataLoc.Length-1)} if ($NewTempLogLoc.EndsWith('\') -eq $true){$NewTempLogLoc = $NewTempLogLoc.Substring(0,$NewTempLogLoc.Length-1)} #region Master DB $masterdb = $sqlserver.Databases.Item('master') $mastermdf = ($masterdb.FileGroups).files.FileName $masterldf = ($masterdb.LogFiles).filename $masterMDFname = $mastermdf.Split('\')[-1] $masterMDFDest = $NewDataLoc + '\' + $masterMDFname $masterLDFname = $masterldf.Split('\')[-1] $masterLDFDest = $NewLogLoc + '\' + $masterLDFname #Source: http://www.mikefal.net/2015/12/09/moving-your-master-database-with-powershell/ $server = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $startup = $server.Services | ? {$_.name -like 'MSSQL*'} $parameters = $startup.StartupParameters.Split(';') $Errorlog = $parameters | ? {$_ -match '-e'} $set = @("-d$masterMDFDest","$Errorlog","-l$masterLDFDest") $startup.startupparameters = $set -join ';' $startup.alter() $startup.Stop() sleep 10 #if ($startup.ServiceState -eq 'running'){$startup.Stop(); sleep 10} Move-Item -Path $mastermdf -Destination $NewDataLoc -Force Move-Item -Path $masterldf -Destination $NewLogLoc -Force $startup.Start() $sqlserver.ConnectionContext.Disconnect() #endregion #region sysdatabases $sysDBs = @('msdb','model') foreach ($sysDB in $sysDBs){ $db = $sqlserver.Databases.Item("$sysDB") $dbmdf = ($db.FileGroups).files.FileName $dbldf = ($db.LogFiles).filename $dbMDFname = $dbmdf.Split('\')[-1] $dbMDFDest = $NewDataLoc + '\' + $dbMDFname $dbLDFname = $dbldf.Split('\')[-1] $dbLDFDest = $NewLogLoc + '\' + $dbLDFname $db.LogFiles[0].FileName = $dbLDFDest ($db.FileGroups).files.FileName = $dbMDFDest $db.Alter() $server = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $startup = $server.Services | ? {$_.name -like 'MSSQL*'} $startup.Stop() sleep 10 #if ($startup.ServiceState -eq 'running'){$startup.Stop(); sleep 10} Move-Item -Path $dbmdf -Destination $NewDataLoc -Force Move-Item -Path $dbldf -Destination $NewLogLoc -Force $startup.Start() } #endregion #region tempDB $tempdb = $sqlserver.Databases.Item('tempdb') $tempdbmdf = ($tempdb.FileGroups).files.FileName $tempdbldf = ($tempdb.LogFiles).filename $tempdbMDFname = $tempdbmdf.Split('\')[-1] $tempdbMDFDest = $NewTempDataLoc + '\' + $tempdbMDFname $tempdbLDFname = $tempdbldf.Split('\')[-1] $tempdbLDFDest = $NewTempLogLoc + '\' + $tempdbLDFname $tempdb.LogFiles[0].FileName = $tempdbLDFDest ($tempdb.FileGroups).files.FileName = $tempdbMDFDest $tempdb.Alter() $server = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $startup = $server.Services | ? {$_.name -like 'MSSQL*'} $startup.Stop() sleep 10 #if ($startup.ServiceState -eq 'running'){$startup.Stop(); sleep 10} Move-Item -Path $tempdbmdf -Destination $NewTempDataLoc -Force Move-Item -Path $tempdbldf -Destination $NewTempLogLoc -Force $startup.Start() #endregion #region User Databases $Databases = $sqlserver.Databases | ? {$_.name -ne "tempdb" -and $_.name -ne "msdb" -and $_.name -ne "model" -and $_.name -ne "master"} #$Databases | select name foreach ($DB in $Databases){ $DBname = $db.Name $DBowner = $db.Owner $dbmdf = ($db.FileGroups).files.FileName $dbldf = ($db.LogFiles).filename $dbMDFname = $dbmdf.Split('\')[-1] $dbMDFDest = $NewDataLoc + '\' + $dbMDFname $dbLDFname = $dbldf.Split('\')[-1] $dbLDFDest = $NewLogLoc + '\' + $dbLDFname $sqlserver.DetachDatabase("$DBname", $FALSE, $FALSE) $datastr = $NewDataLoc + '\' + $dbMDFname $logstr = $NewLogLoc + '\' + $dbLDFname $sc = $null $sc = new-object system.collections.specialized.stringcollection $sc.Add($datastr) $sc.Add($logstr) Move-Item -Path $dbmdf -Destination $NewDataLoc -Force Move-Item -Path $dbldf -Destination $NewLogLoc -Force $sqlserver.AttachDatabase("$DBname", $sc, $DBowner, [Microsoft.SqlServer.Management.Smo.AttachOptions]::None) } #endregion