Day 6 of 31 days of SQL Server Backup and Restore using PowerShell: Redirecting backups

A very useful feature of performing your backups with PowerShell is that you can redirect your backups easily on pretty much any condition you want, or even run multiple backups asynchronously to different network drives to save saturating a single NIC.

At the simplest level you can push each database backup into it’s own folder. However, just like with T-SQL backups, if the folder doesn’t exist the backup will fail, so we’ll introduce a quick check:

Import-Module "SQLPS" -DisableNameChecking

$ServerName = "Server1"
$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerName)

$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database

foreach ($db in $SQLSvr.Databases | Where-Object {$_.Name -ne "tempdb"}){
    $Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
    $Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
    $backup.BackupSetDescription = "Full Back of "+$Db.Name
    $Backup.Database = $db.Name
    $BackupFolder = "c:\psbackups\"+$Db.name+"\"
    if ((Test-Path $BackupFolder) -eq $False){
        New-Item $BackupFolder -type Directory
    }
    $BackupName = $BackupFolder+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"
    $DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
    $BackupDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($BackupName,$DeviceType)

    $Backup.Devices.Add($BackupDevice)
    $Backup.SqlBackup($SQLSvr)
    $Backup.Devices.Remove($BackupDevice)
}

Or we can monitor the space left on the backup drive, and if it falls below what we need (plus a margin for safety) the backups move to another area?:

Import-Module "SQLPS" -DisableNameChecking

$folders = @()
$folders += "c:\psbackups"
$folders += "\\server2\backups$"
$folders += "\\server3\panicspace$"

$FolderCount = 0
$CurrentDrive = New-Object -Com Scripting.FileSystemObject
$MarginForError = 200

$ServerName = "Server1"
$SQLSvr = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($ServerName)

$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database

foreach ($db in $SQLSvr.Databases | Where-Object {$_.Name -ne "tempdb"}){
    $Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
    $Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
    $backup.BackupSetDescription = "Full Back of "+$Db.Name
    $Backup.Database = $db.Name

    $CurrentDrive = $folders[$FolderCount]
    if (($Db.Size-$Db.SpaceUnused)+$MarginForError -gt ($CurrentDrive.AvailableSpace/1024/1024)){
        $FolderCount++
    }
    $BackupName = $Folders[$FolderCount]+"\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"
    $DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
    $BackupDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($BackupName,$DeviceType)

    $Backup.Devices.Add($BackupDevice)
    $Backup.SqlBackup($SQLSvr)
    $Backup.Devices.Remove($BackupDevice)
}

Here we build up an array of possible backup folders ($Folders), set a $MarginForError in Mb (in this case I’m setting 200MB) and creating a new Scripting object to hold our drive object. As we loop through the database we check that the AvailableSpace on the drive is greater that our requirements (AvailableSpace returns space in bytes, so we need to change that to MB), I check each time just in case something else is using the disk and don’t get caught out. If we are short of space we increase the $FolderCount variable by 1.

When building up the backup target we us $FolderCount to pick the currently used path from the $Folders array, and then continue with the backup as normal.

Using $Db.Size gives us a worst case scenario for the potential size of the backup. Though if you have a lot of free space in your DB it’ll be skewed, this could be corrected by using $Db.spaceavailable as well (correcting for the differences in return values (Size is in MB, SpaceAvilable in KB).

Tomorrow we’ll be looking at Asynchronous backups, allowing you to fire off multiple concurrently running backups from a single script

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.