Backup-SQL-Database.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

<#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
}