Backing Up a WordPress Site from Windows with PowerShell

<#
.SYNOPSIS
    Backs up a WordPress site: a zip of the files and a SQL dump of the database.
.DESCRIPTION
    Creates a dated folder under BackupRoot, dumps the database with mysqldump, and zips the
    WordPress folder next to it. The database name and host are read from wp-config.php unless
    you pass them. The database password comes from a PSCredential and reaches mysqldump through
    a temporary option file, so it never appears on a command line. Supports -WhatIf.
.PARAMETER SitePath
    The WordPress root folder, the one with wp-config.php in it.
.PARAMETER BackupRoot
    The folder the dated backup folders go into. Created if it doesn't exist.
.PARAMETER Credential
    Database user name and password.
.PARAMETER DatabaseName
    Overrides DB_NAME from wp-config.php.
.PARAMETER DatabaseHost
    Overrides DB_HOST from wp-config.php. A host:port value is split for you.
.PARAMETER MysqldumpPath
    Path to mysqldump.exe if it isn't on your PATH.
.PARAMETER KeepLast
    After a successful backup, remove older backup folders for this site so only this many remain. 0 keeps everything.
.PARAMETER SkipFiles
    Dump the database only.
.EXAMPLE
    .\Backup-WordPressSite.ps1 -SitePath C:\Sites\example -BackupRoot \\NAS01\Backups\example -Credential (Get-Credential) -WhatIf
.EXAMPLE
    .\Backup-WordPressSite.ps1 -SitePath C:\Sites\example -BackupRoot \\NAS01\Backups\example -Credential $dbCred -KeepLast 14
#>
[CmdletBinding(SupportsShouldProcess)]
param(
    [Parameter(Mandatory)]
    [ValidateScript({ Test-Path -LiteralPath (Join-Path $_ 'wp-config.php') -PathType Leaf })]
    [string]$SitePath,

    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$BackupRoot,

    [Parameter(Mandatory)]
    [System.Management.Automation.PSCredential]$Credential,

    [ValidatePattern('^[A-Za-z0-9_$-]+$')]
    [string]$DatabaseName,

    [ValidateNotNullOrEmpty()]
    [string]$DatabaseHost,

    [ValidateNotNullOrEmpty()]
    [string]$MysqldumpPath = 'mysqldump',

    [ValidateRange(0, 365)]
    [int]$KeepLast = 0,

    [switch]$SkipFiles
)

$ErrorActionPreference = 'Stop'

function Get-WpConfigValue {
    param([string]$Text, [string]$Name)
    $pattern = "define\(\s*['""]$Name['""]\s*,\s*['""]([^'""]*)['""]\s*\)"
    $match = [regex]::Match($Text, $pattern)
    if ($match.Success) { $match.Groups[1].Value }
}

$SitePath = (Resolve-Path -LiteralPath $SitePath).ProviderPath
$config = Get-Content -LiteralPath (Join-Path $SitePath 'wp-config.php') -Raw
if (-not $DatabaseName) { $DatabaseName = Get-WpConfigValue -Text $config -Name 'DB_NAME' }
if (-not $DatabaseHost) { $DatabaseHost = Get-WpConfigValue -Text $config -Name 'DB_HOST' }
if (-not $DatabaseName) { throw 'No DB_NAME found in wp-config.php. Pass -DatabaseName.' }
if (-not $DatabaseHost) { $DatabaseHost = 'localhost' }

$port = 3306
if ($DatabaseHost -match '^(?<h>[^:]+):(?<p>\d+)$') {
    $DatabaseHost = $Matches.h
    $port = [int]$Matches.p
}

$siteName = Split-Path -Path $SitePath -Leaf
$stamp = Get-Date -Format 'yyyyMMdd-HHmmss'
$target = Join-Path $BackupRoot "$siteName-$stamp"

$result = [pscustomobject]@{
    Site         = $siteName
    Database     = $DatabaseName
    BackupFolder = $target
    DatabaseDump = $null
    FilesZip     = $null
    SizeMB       = 0
    Removed      = 0
    Error        = $null
}

if (-not $PSCmdlet.ShouldProcess($target, "Back up $siteName (database $DatabaseName on $DatabaseHost)")) {
    return $result
}

if (-not (Get-Command -Name $MysqldumpPath -ErrorAction SilentlyContinue)) {
    throw "Can't find mysqldump at '$MysqldumpPath'. Install the MySQL or MariaDB client tools, or pass -MysqldumpPath."
}

$optionFile = $null
try {
    New-Item -ItemType Directory -Path $target -Force | Out-Null

    # The password goes in a temporary option file instead of on the command line,
    # where anyone who can list processes could read it.
    $optionFile = [System.IO.Path]::GetTempFileName()
    $plain = $Credential.GetNetworkCredential().Password
    if ($plain.Contains('"')) { throw 'The database password contains a double quote, which the option file can''t hold. Change the password or use a login path.' }
    $plain = $plain.Replace('\', '\\')
    [System.IO.File]::WriteAllText($optionFile, "[client]`nuser=$($Credential.UserName)`npassword=""$plain""`n")
    $plain = $null

    $dumpFile = Join-Path $target "$DatabaseName.sql"
    $dumpArgs = @(
        "--defaults-extra-file=$optionFile"
        "--host=$DatabaseHost"
        "--port=$port"
        '--single-transaction'
        '--quick'
        '--default-character-set=utf8mb4'
        "--result-file=$dumpFile"
        $DatabaseName
    )
    Write-Verbose "Dumping $DatabaseName from ${DatabaseHost}:$port"
    & $MysqldumpPath @dumpArgs
    if ($LASTEXITCODE -ne 0) { throw "mysqldump exited with code $LASTEXITCODE." }
    $result.DatabaseDump = $dumpFile

    if (-not $SkipFiles) {
        Add-Type -AssemblyName System.IO.Compression.FileSystem
        $zipFile = Join-Path $target "$siteName-files.zip"
        Write-Verbose "Zipping $SitePath"
        [System.IO.Compression.ZipFile]::CreateFromDirectory($SitePath, $zipFile, [System.IO.Compression.CompressionLevel]::Optimal, $false)
        $result.FilesZip = $zipFile
    }

    $bytes = (Get-ChildItem -LiteralPath $target -File | Measure-Object -Property Length -Sum).Sum
    $result.SizeMB = [math]::Round($bytes / 1MB, 1)
}
catch {
    $result.Error = $_.Exception.Message
}
finally {
    if ($optionFile -and (Test-Path -LiteralPath $optionFile)) { Remove-Item -LiteralPath $optionFile -Force -Confirm:$false }
}

if (-not $result.Error -and $KeepLast -gt 0) {
    $pattern = '^' + [regex]::Escape($siteName) + '-\d{8}-\d{6}$'
    $old = @(Get-ChildItem -LiteralPath $BackupRoot -Directory | Where-Object Name -match $pattern | Sort-Object Name -Descending | Select-Object -Skip $KeepLast)
    foreach ($folder in $old) {
        if ($PSCmdlet.ShouldProcess($folder.FullName, 'Remove old backup')) {
            try {
                Remove-Item -LiteralPath $folder.FullName -Recurse -Force -Confirm:$false
                $result.Removed++
            }
            catch {
                Write-Warning "Couldn't remove $($folder.Name): $($_.Exception.Message)"
            }
        }
    }
}

$result