SQL Server backup script

backup.ps1

# Set these variables to the desired values
$SqlServer="SqlHost"			# Server Hostname
$InstanceName="DbInst"			# Database Instance
$Username="MyUser"			# Username with backup permissions
$Database="EpicDB"			# Name of database to backup
$LocalDir="C:\DB"			# Local directory to use whilst creating backup
$Location="\\storage\EpicDB"    	# Location to move backup file to. Can be UNC or file path

#############################################
# Don't touch anything beyond this point... #
#############################################
Write-Verbose "Verbose output enabled"

Import-Module BitsTransfer
Add-Type -ErrorAction Stop -AssemblyName “Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
Add-Type -ErrorAction Stop -AssemblyName “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”
Add-Type -ErrorAction Stop -AssemblyName “Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”

function Format-Timespan
{
    param (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [TimeSpan]$Timespan
    )

    $output = New-Object System.Collections.Generic.List[System.Object]

    if ($Timespan.Days -gt 1) {
        $output.Add("{0} days" -f $Timespan.Days)
    } elseif ($Timespan.Days -eq 1) {
        $output.Add("1 day")`
    }

    if ($Timespan.Hours -gt 1) {
        $output.Add("{0} hours" -f $Timespan.Hours)
    } elseif ($Timespan.Hours -eq 1) {
        $output.Add("1 hour")
    }

    if ($Timespan.Minutes -gt 1) {
        $output.Add("{0} minutes" -f $Timespan.Minutes)
    } elseif ($Timespan.Minutes -eq 1) {
        $output.Add("1 minute")
    }

    # If we are talking about days and hours, let's not worry about seconds...
    if ($Timespan.TotalHours -lt 1) {
        if ($Timespan.Seconds -gt 1) {
            $output.Add("{0} seconds" -f $Timespan.Seconds)
        } elseif ($Timespan.Seconds -eq 1) {
            $output.add("1 second")
        }
    }

    if ($output.Count -eq 0) {
        $output.Add("0 seconds")
    }

    [string]$outStr = ""
    if ($output.Count -gt 1) {
        $end = $output[-1]
        $output.Remove($end) | Out-Null

        $outStr = $output -join ', '
        $outStr = "$outStr and $end"
    } else {
        $outStr = "$output"
    }

    return $outStr
}

#
# Prompt user for password
#
Write-Host "Connecting to `"$SqlServer\$InstanceName`" database `"$Database`" as `"$Username`""
$Password = Read-Host "Enter password for $Username" -AsSecureString
$Password = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password)
$Password = [Runtime.InteropServices.Marshal]::PtrToStringAuto($Password)

#
# Start the timer
#
Write-Verbose "Starting the timer"
$timer = [System.Diagnostics.Stopwatch]::StartNew()

#
# Connect to server
#
Write-Verbose "Creating server connection"
$Conn = New-Object( 'Microsoft.SqlServer.Management.Common.ServerConnection' )
$Conn.ServerInstance = "$SqlServer\$InstanceName"
$Conn.LoginSecure = $false
$Conn.Login = $Username
$Conn.Password = $Password
$Conn.StatementTimeout = (60 * 24 * 24 * 30) # 30 days

$Svr = New-Object( 'Microsoft.SqlServer.Management.Smo.Server' )( $Conn )

#
# Test connectivity
#
try {
    $Svr.ConnectionContext.Connect()
} catch {
    Write-Warning "Unable to connect to database!"
    Write-Host
    Write-Host "Press any key to continue..."
    [void][System.Console]::ReadKey($true)
    exit
}

#
# Determine timing before backup commences
#
$DateName = (Get-Date).toString("dd-MMM-yyyy HH.mm")

#
# Perform backup
#
Write-Host
Write-Host "Backing up database to: $LocalDir"
Write-Verbose "Opening connection to Database"

$bk = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$bk.BackupSetName = '$Database-Full Database Backup'
$bk.Database = $Database
$bk.Initialize = $true
$bk.Incremental = $false
$bk.MediaDescription = "Disk"
$bk.Devices.AddDevice("$LocalDir\backup.bak", "File")
$bk.PercentCompleteNotification = 1

# Register backup events
Write-Verbose "Registering Event Listeners"
Register-ObjectEvent -InputObject $bk -EventName "PercentComplete" -SourceIdentifier "sqlbackup-pc" | Out-Null
Register-ObjectEvent -InputObject $bk -EventName "Complete" -SourceIdentifier "sqlbackup-cm" | Out-Null

try {
    Write-Verbose "Beginning Backup"
    $bk.SqlBackupAsync($Svr)
} catch [Exception] {
    Write-Host ( $_.Exception | Format-List -Force | Out-String )
    Write-Host
    Write-Host "Press any key to continue..."
    [void][System.Console]::ReadKey($true)
    exit
}

#
# Monitor the Async backup progress and provide updates
#
try {
    $percent = 0
    $elapsed_last = 0
    Write-Verbose "Awaiting Events..."

    while ($true) {
        $event = Get-Event

        $elapsed = ($timer.elapsedmilliseconds.ToString()) / 1000

        if ($event -ne $null) {
            Write-Verbose ("Backup event captured: {0}" -f $event.SourceIdentifier)
            $event | Remove-Event

            if ($event.SourceIdentifier -eq "sqlbackup-cm") {
                Write-Progress "Backing up $Database" -Completed
                break
            } elseif ($event.SourceIdentifier -eq "sqlbackup-pc") {
                $percent = $event.SourceArgs.Percent
                $elapsed_last = $elapsed
            }
        }

        if ($percent -le 0) {
            $status = "Progress: 0%  (unknown remaining)"
        } else {
            $secsleft = ((($elapsed_last / $percent) * 100) - $elapsed)
            $secsleft = New-TimeSpan -Seconds $secsleft
            $status = "Progress: {0}%  ({1} remaining)" -f $percent, (Format-Timespan -Timespan $secsleft)
        }

        Write-Progress -Activity "Backing up $Database" `
            -status $status `
            -PercentComplete $percent
        Start-Sleep -Milliseconds 500
    }
} catch [Exception] {
    $_ | Format-List -Force
    Write-Host "SQL Backup Failed!"
    Write-Host
    Write-Host "Press any key to continue..."
    [void][System.Console]::ReadKey($true)
    exit
} finally {
    Unregister-Event -SourceIdentifier "sqlbackup-pc"
    Unregister-Event -SourceIdentifier "sqlbackup-cm"
    $Svr.ConnectionContext.Disconnect()
}

#
# Ensure the backup file was created
#
if (( Test-Path "$LocalDir\backup.bak" ) -eq $false) {
    Write-Error "Something went wrong!"
    Write-Host
    Write-Host "Press any key to continue..."
    [void][System.Console]::ReadKey($true)
    exit
}

#
# Move file to location
#
$PathName = "$Database backup $DateName\Full\Full Backup $DateName.bak"
if (( Test-Path( Split-Path "$Location\$PathName" ) ) -eq $false) {
    New-Item ( Split-Path "$Location\$PathName" ) -ItemType Directory | Out-Null
}

try {
    $job = Start-BitsTransfer -Source "$LocalDir\backup.bak" -Destination "$Location\$PathName" `
        -Description "Moving: $LocalDir\backup.bak => $Location\$PathName" -DisplayName "Backup" -Asynchronous

    # Start stopwatch
    $sw = [System.Diagnostics.Stopwatch]::StartNew()
    Write-Progress -Activity "Connecting..."

    while ($job.JobState.ToString() -ne "Transferred") {
        switch ($job.JobState.ToString()) {
            "Connecting" {
                break
            }
            "Transferring" {
                $pctcomp = ($job.BytesTransferred / $job.BytesTotal) * 100
                $elapsed = ($sw.elapsedmilliseconds.ToString()) / 1000

                if ($elapsed -eq 0) {
                    $xferrate = 0.0
                } else {
                    $xferrate = (($job.BytesTransferred / $elapsed) / 1mb);
                }

                if ($job.BytesTransferred % 1mb -eq 0) {
                    if ($pctcomp -gt 0) {
                        $secsleft = ((($elapsed / $pctcomp) * 100) - $elapsed)
                        $secsleft = New-Timespan -Seconds $secsleft
                        $status = "Progress: {0}%  ({1} remaining)" -f [Int32]$pctcomp, (Format-Timespan -Timespan $secsleft)
                    } else {
                        $status = "Progress: 0%  (unknown remaining)"
                    }

                    Write-Progress -Activity ("Copying file `"" + ($PathName.Split("\") | Select -last 1) + "`" @ " + "{0:n2}" -f $xferrate + "MB/s") `
                                   -Status $status `
                                   -PercentComplete $pctcomp `
                }
                break
            }
            "Transferred" {
                break
            }
            Default {
                throw $job.JobState.ToString() + " unexpected BITS state."
            }
        }
    }

    $sw.Stop()
    $sw.Reset()
} catch [Exception] {
    $_ | Format-List -Force
    Write-Host "BITS File Transfer failed!"
    Write-Host
    Write-Host "Press any key to continue..."
    [void][System.Console]::ReadKey($true)
    exit
} finally {
    Complete-BitsTransfer -BitsJob $job
    Write-Progress -Activity "Completed" -Completed
}

Remove-Item -Path "$LocalDir\backup.bak"

#
# Output Summary
#
$timer.Stop()
Write-Host ("Backup Completed in {0}." -f (Format-Timespan $timer.Elapsed))
Write-Host ("Backup Size: {0:n2} GB" -f [float]((Get-Item "$Location\$PathName").Length / 1GB))
Write-Host
Write-Host "Press any key to continue..."
[void][System.Console]::ReadKey($true)

Tested with SQL Server 2008 64-bit