Rolling backup of one or more SQL Server databases to Dropbox.

dropbox-db-backup.ps1

param(
    [Parameter(Mandatory=$true)]
    [string[]]$DatabaseNames,
    [Parameter(Mandatory=$true)]
    [string]$AccessToken,
    [Parameter(Mandatory=$true)]
    [string]$BackupPath
)

function Upload-FileToDropbox { 
    param(
        [Parameter(Mandatory=$true)]
        [string]$SourcePath,
        [Parameter(Mandatory=$true)]
        [string]$TargetPath,
        [Parameter(Mandatory=$true)]
        [string]$AccessToken
    )

    $body = '{ "path": "' + $TargetPath + '", "mode": "overwrite" }'

    $authorization = "Bearer $AccessToken"

    $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"

    $headers.Add("Authorization", $authorization)
    $headers.Add("Dropbox-API-Arg", $body)
    $headers.Add("Content-Type", 'application/octet-stream')
     
    Invoke-RestMethod `
        -Uri https://content.dropboxapi.com/2/files/upload `
        -Method Post `
        -InFile $SourcePath `
        -Headers $headers
}

function Remove-FileFromDropbox { 
    param(
        [Parameter(Mandatory=$true)]
        [string]$TargetPath,
        [Parameter(Mandatory=$true)]
        [string]$AccessToken
    )

    $body = '{ "path": "' + $TargetPath + '" }'

    $authorization = "Bearer $AccessToken"

    $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"

    $headers.Add("Authorization", $authorization)
    $headers.Add("Content-Type", 'application/json')

    Invoke-RestMethod `
        -Uri https://api.dropboxapi.com/2/files/delete_v2 `
        -Method Post `
        -Headers $headers `
        -Body $body
}

$dateStamp = (Get-Date).ToString("yyyy-MM-dd-HHmm")

$DatabaseNames | ForEach-Object {
    $databaseName = $_
    $backupFilename = "$databaseName-$dateStamp.bak"
    $backupFilePath = "$BackupPath\$backupFilename"

    $command = "BACKUP DATABASE [$databaseName] TO DISK='$backupFilePath' WITH COMPRESSION, INIT, STATS = 10"

    # Perform the database backup to a local folder
    Invoke-Sqlcmd -HostName 'localhost' -Query $command

    # Send the backup file to Dropbox
    Upload-FileToDropbox `
        -SourcePath $backupFilePath `
        -TargetPath "/$backupFilename" `
        -AccessToken $AccessToken

    # Get all but the last three backups
    $oldFiles = 
        Get-ChildItem $BackupPath -Filter "$databaseName*.bak" | 
        Sort-Object CreationTime -Descending | 
        Select-Object -Skip 3
    
    # Delete the old backups from Dropbox
    $oldFiles | ForEach-Object { Remove-FileFromDropbox -TargetPath "/$_" -AccessToken $AccessToken }
    
    # Delete the old backups locally
    $oldFiles | Remove-Item -Force
}