Skip to content

Instantly share code, notes, and snippets.

@markashleybell
Last active February 16, 2021 17:43
Show Gist options
  • Save markashleybell/328fe4c40c279808253a78cef9f6ea62 to your computer and use it in GitHub Desktop.
Save markashleybell/328fe4c40c279808253a78cef9f6ea62 to your computer and use it in GitHub Desktop.
Rolling backup of one or more SQL Server databases to Dropbox.
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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment