Relocate AG Database Files.ps1

############## Setup ##############

$AGName = ''
$AGPrimary = ''
$AGSecondary = ''

# This allows you to process just a subset of databases using the name (wildcards are possible)
$DBNamePattern = 'AdventureW*'

$TargetFolder = 'K:\Data\'
$DBDataFolder = 'D:\Data\'
$DBLogFolder = 'L:\LOG\'
$Sourcefolder = 'L:\LOG\'
$LogFilePattern = $DBNamePattern + '.ldf'
$DataFilePattern = $DBNamePattern + '.mdf'

# Yes, this is in a console and doing console output stuff..... if this is made into a "real" function this would be removed
Clear-Host

############## Work actually begins here ##############

# We build a list of databases using the folder location, because we only want to move databases that are actually on the old storage. The filenames are also the names of the databases.
$dbs = (Get-ChildItem -Path $SourceFolder* -Include $LogFilePattern ).BaseName

# Tons of colourful output, because I like it when it runs for hours and I see something happening
Write-Host "Beginning move of files for '$DBNamePattern' databases on $AGSecondary ($($dbs.Count) databases) " -ForegroundColor Green

# Make sure the TargetFolder is there (using robocopy to copy folder permissions, becuase I don't know a concise way to do this in PoSh)
if (!(Test-Path -PathType Container -Path $TargetFolder)) {
    
	Write-Host "$Targetfolder missing, creating folder with correct permissions" -ForegroundColor Yellow
    robocopy "$DBDataFolder" "$TargetFolder" /e /copyall /xf *
}

# Make sure AGSecondary can be read from (normally turned off, but we need to read from it for the move)
Write-Host "Turning on readable secondary for AG '$AGName'" -ForegroundColor Blue
Set-DbaAgReplica -SqlInstance $AGPrimary -AvailabilityGroup $AGname -ConnectionModeInSecondaryRole Yes -Replica $AGSecondary | Out-Null

foreach ($DatabaseName in $dbs) {
    
	Write-Host "Begin Database File Relocation for $DatabaseName in AG '$AGName'" -ForegroundColor Blue
    
	# Read the locations of the Data and Log files on the secondary to see if we have to move anything
    $DatabaseFiles = Get-DbaDbFile -SqlInstance $AGSecondary -Database $DatabaseName
    $DatabaseLogFile = Get-DbaDbFile -SqlInstance $AGSecondary -Database $DatabaseName | Where-Object TypeDescription -EQ "LOG"
    $SourceFolder = $DatabaseLogFile.PhysicalName.Substring(0, ($DatabaseLogFile.PhysicalName.LastIndexOf('\') + 1))

    # Do we have work to do?
    if ($SourceFolder -ne $TargetFolder) {
        
		# Pause the AG for this DB 
        Write-Host "Pausing data movement in AG '$AGName' for $DatabaseName" -ForegroundColor Blue
        Suspend-DbaAgDbDataMovement -SqlInstance $AGSecondary -AvailabilityGroup $AGName -Database $DatabaseName -Confirm:$false | Out-Null

        # Remove the DB from the AG on the secondary so we can modify file paths
		# I know, "SET HADR OFF" scared me too, but it doesn't break the AG, I promise!
		# This might be replaced by Remove-DbaAgReplica, but not sure about that
        Write-Host "Removing database $DatabaseName from AG '$AGName'" -ForegroundColor Blue
        Invoke-DbaQuery -SqlInstance $AGSecondary -Database master -Query "ALTER DATABASE [$DatabaseName] SET HADR OFF;"

        # Update the metadata for the database file locations
        Write-Host "Updating Database Metadata for $DatabaseName" -ForegroundColor Blue
        foreach ($DatabaseFile in $DatabaseFiles) {
            $OldLocation = $DatabaseFile.PhysicalName
            
            $NewLocation = (Join-Path $TargetFolder ($DatabaseFile.PhysicalName -replace '.*\\'))
            
            if ($OldLocation -ne $NewLocation) {
                Invoke-DbaQuery -SqlInstance $AGSecondary -Database master -Query "ALTER DATABASE [$DatabaseName] MODIFY FILE (NAME = $($DatabaseFile.LogicalName), FILENAME = '$NewLocation');"
            }
        }
    }
}

# Offline the SQL Server (to release the file locks in the filesystem)
Write-Host "Taking $AGSecondary offline" -ForegroundColor Blue
Stop-DbaService $AGSecondary

# Move the data files to the new location
$dbdatafiles = Get-ChildItem -Path $DBDataFolder* -Include $DataFilePattern
foreach ($file in $dbdatafiles) {
    
	Write-Host "Moving $file" -ForegroundColor Blue

    $OldLocation = Join-Path $DBDataFolder $file.Name
    $NewLocation = Join-Path $TargetFolder $file.Name
        
    if ($OldLocation -ne $NewLocation) {
        Move-Item -Path $OldLocation -Destination $NewLocation
    }
}

# Move the log files to the new location
$dblogfiles = Get-ChildItem -Path $DBLogFolder* -Include $LogFilePattern
foreach ($file in $dblogfiles) {
    
    Write-Host "Moving $file" -ForegroundColor Blue

    $OldLocation = Join-Path $DBLogFolder $file.Name
    $NewLocation = Join-Path $TargetFolder $file.Name
        
    if ($OldLocation -ne $NewLocation) {
        Move-Item -Path $OldLocation -Destination $NewLocation
    }
}

# Start SQL Server again
Start-DbaService $AGSecondary
 
# Give SQL Server a moment to start up / recover the databases
Start-Sleep 10 
 
# Add the databases back into the availability group
foreach ($DatabaseName in $dbs) {
	
    Write-Host "Bringing $DatabaseName back into Availability Group $AGName" -ForegroundColor Blue
    Invoke-DbaQuery -SqlInstance $AGSecondary -Database master -Query  "ALTER DATABASE [$DatabaseName] SET HADR AVAILABILITY GROUP = [$AGName];"
}

Write-Host "Turning off readable secondary for AG '$AGName'" -ForegroundColor Blue
Set-DbaAgReplica -SqlInstance $AGPrimary -AvailabilityGroup $AGname -ConnectionModeInSecondaryRole No -Replica $AGSecondary | Out-Null

Write-Host "Completed move of database files for $DatabaseName on AG '$AGName'" -ForegroundColor Green