[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