Backup SQL to a remote location with PowerShell and BITS

Mon, Oct 10, 2011

Recently I had to make sure I was getting backups of my many little SQL servers. In this case, I wanted to replace an old batch file I was using with something involving powershell.

As always, it proved to be an extreme improvement in functionality.

This script will loop through databases and start a transfer of whatever backup file is created, and then delete the backup once it’s been completed. It will also make sure the backup hasn’t been running too long, and inform you if it has.

Please let me know if you have any questions or comments.

#Load Assemblies and Modules
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
Import-Module BitsTransfer

#Define some variables
$smtpServer = "mx.server.local"
$smtpTo = "[email protected]"
$remoteDest = "\\backupserver\SQLBackup"
$backupDest = "C:\SQLBackups"
$scriptTimeOut = (Get-Date).AddHours(1) #You may have to modify this depending on backup times.
$sqlServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') #'server\instance'

#Internal only function
Function DB-Backup([string]$name,[string]$type)
{
if ($type -eq "Database") {$fileExt = ".bak"}
if ($type -eq "Log") {$fileExt = ".trn"}
$fileName = $backupDest + "\" + $name + (get-date -format yyyyMMddHHmmss) + $fileExt
$backupObj = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$backupObj.Action = $type
$backupObj.BackupSetDescription = "Full backup of " + $name
$backupObj.BackupSetName = $name + " Backup"
$backupObj.Database = $name
$backupObj.Devices.AddDevice($fileName, 'File')
$backupObj.MediaDescription = "Disk"
$backupObj.SqlBackup($sqlServer)
return $filename
}

#Kick off database backups and associated file transfers
$sqlServer.Databases | %{
#Do full backups on user type databases
if ($_.IsSystemObject -eq $False -and $_.IsMirroringEnabled -eq $False)
{
Start-BitsTransfer -Source (DB-Backup $_.Name "Database") -Destination $remoteDest -TransferType Upload -Asynchronous
#... and if the database isn't in simple recovery mode, lets do a log backup!
if ($_.DatabaseOptions.RecoveryModel -notmatch 'Simple')
{
Start-BitsTransfer -Source (DB-Backup $_.Name "Log") -Destination $remoteDest -TransferType Upload -Asynchronous
}
}
}

#Some fail safe on waiting for all transfers to have started... seemed like a good idea
Sleep 20

#While BITS transfer jobs exist, list the transfered ones and delete local copy
While (Get-BitsTransfer)
{
Get-BitsTransfer | ?{$_.JobState -eq "Transferred"} | %{
$_.FileList | % {Remove-Item $_.LocalName}
Complete-BitsTransfer $_
}
#Loop throttle
Sleep 10
#Better check to be sure we don't run too long
if ((Get-Date) -gt $scriptTimeOut)
{
Send-MailMessage -SmtpServer $smtpServer -From ("SQLBackup@" + ($env:computername).ToLower() + ".local") -To $smtpTo -Subject "SQL Backup" -Body "There is an issue with the backup job for SQL. You'd better check it out."
exit
}
}

Send4Help

Backup SQL to a remote location with PowerShell and BITS