Backup-SQL-Database

1.1

Backup-SQL-Database.ps1

                                
<#PSScriptInfo

.VERSION 1.1

.GUID f26d7743-1633-45c5-a8a4-eca6c44caf74

.AUTHOR a.kahl

.COMPANYNAME 

.COPYRIGHT 

.TAGS 

.LICENSEURI 

.PROJECTURI 

.ICONURI 

.EXTERNALMODULEDEPENDENCIES 

.REQUIREDSCRIPTS 

.EXTERNALSCRIPTDEPENDENCIES 

.RELEASENOTES

#>

<# 

.DESCRIPTION 

 Backup Microsoft SQL Server database 

#> 

param()

function Backup-SQL-Database {

param(

    [string]$SqlServer="localhost",

    [Parameter(Mandatory=$true)]

    [string]$SqlDatabase

)

    Write-Host "[INFO] Start Backup-SQL-Database"

    Write-Host "[INFO] Server: $SqlServer"

    Write-Host "[INFO] Database: $SqlDatabase"

    $currentLocation = Get-Location

    #Load SMO assemblies

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")| Out-Null

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")| Out-Null

    #Set backup configurations

    $server = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -Argumentlist $SqlServer

    $backupDate = Get-Date -Format yyyyMMdd-HHmmss

    $backupDir = $server.Settings.BackupDirectory

    $backupFile = "$($backupDir)\$($SqlDatabase)-$($backupDate).bak"

    $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup

    $smoBackup.Action = "Database"

    $smoBackup.BackupSetDescription = "Full backup of $($SqlDatabase)"

    $smoBackup.BackupSetName = "$($SqlDatabase) Backup"

    $smoBackup.Database = $SqlDatabase

    $smoBackup.MediaDescription = "Disk"

    $smoBackup.CompressionOption = "1"

    $smoBackup.PercentCompleteNotification = "10"

    $percentEventHandler = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] { Write-Host "[INFO] Executing $($_.Percent)%" }

    $completedEventHandler = [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] { Write-Host "[INFO] Backup of database $SqlDatabase to $backupFile completed" }

    $smoBackup.add_PercentComplete($percentEventHandler)

    $smoBackup.add_Complete($completedEventHandler)

    $smoBackup.Devices.AddDevice($backupFile,"File")

    #Backup

    Write-Host "[INFO] Starting backup of $SqlDatabase" 

    $smoBackup.SqlBackup($server)

    Write-Host "[DONE]" -ForegroundColor White -BackgroundColor Green

    Write-Host ""

    Set-Location -Path $currentLocation

    return $backupFile

}