SQL Relocate / Move database files

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