Backup SQL to a remote location with PowerShell and BITS
Mon, Oct 10, 2011Recently 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 } }