## Script for backuping SQL databases to remote host
## Requirements: Task scheduler account must be sysadmin in SQL Server and have proper ACL priveleges on folders;
## Account under which run's SQL Server must have priveleges on local backup folder
## Author: Denis Goncharov 8/16/2017
## v.0.3

$BackupDate = get-date -format yyyyMMdd_HHmmss
$RemoteBackupDirectory = "\\tvebus10\Backups\RUTVEM0APP05\"
$LocalBackupDirectory = "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\"
$CurrentBackupDirectory = $LocalBackupDirectory + $BackupDate
$date_limit = (Get-Date).AddDays(-14);
$hostname = (get-childitem -path env:computername).value

if (!(Test-Path $CurrentBackupDirectory)) {New-Item -ItemType Directory $CurrentBackupDirectory}
if (!(Test-Path $RemoteBackupDirectory)) {New-Item -ItemType Directory $RemoteBackupDirectory}

$exceptions = "master|model|msdb|Report|tempdb"

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

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") 'localhost'
$server.ConnectionContext.StatementTimeout = 0
$DatabaseList = $server.Databases

foreach ($Database in $DatabaseList) {
    if ($Database.Name -notmatch $exceptions) {
        $dbName = $Database.Name
        $localSqlBackupPath = $CurrentBackupDirectory + "\" + $dbName + "_" + $BackupDate + ".bak"
        $backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
        $backup.Action = "Database"
        $backup.BackupSetDescription = "Full backup of " + $dbName
        $backup.BackupSetName = $dbName + " backup"
        $backup.Database = $dbName
        $backup.MediaDescription = "Disk"
        $backup.Devices.AddDevice("$localSqlBackupPath", "File")
        $backup.SqlBackup($server)
    }
}
Copy-Item -Recurse $CurrentBackupDirectory $RemoteBackupDirectory

function compare_size ($FirstDirectory,$SecondDirectory){
   if(!(New-Object -COM 'Scripting.FileSystemObject').GetFolder($FirstDirectory).Size -eq (New-Object -COM 'Scripting.FileSystemObject').GetFolder($SecondDirectory).Size){
        return $true
    }
return $false
}

function Send-Email () {
    $emailSmtpServer = "corimc04.corp.jabil.org"
    $emailSmtpServerPort = "25"
    $emailSmtpUser = "Backup_SQL_"+$hostname+"@jabil.com"
    $emailSmtpPass = ""
    $recipient = @("anastasia_ivanova@jabil.com","denis_goncharov@jabil.com","aleksandr_dobrov@jabil.com","mikhail_kiselev@Jabil.com")
    $encoding = [System.Text.Encoding]::UTF8
    $msgText = "Backup error on "+$hostname
    Send-mailmessage -from $emailSmtpUser -to $recipient -subject "Error backuping $hostname" -body $msgText -smtpServer $emailSmtpServer -Encoding $encoding -Attachments $diff_file_path

}

function get_backups($Root_path) {
    $array_backups = Get-ChildItem -Path $Root_path -Recurse -Force -Directory | Where-Object { $_.PSIsContainer -and $_.CreationTime -lt $date_limit } |
        ForEach-Object {[object] @{Name = $_.Name; Path = $_.FullName; Size = ((New-Object -COM 'Scripting.FileSystemObject').GetFolder($_.FullName).Size) }}
    return $array_backups
}
$tmp_var = $RemoteBackupDirectory+$BackupDate

if((compare_size $CurrentBackupDirectory $tmp_var)){
    Send-Email
}

$local_backups = get_backups $LocalBackupDirectory
$remote_backups = get_backups $RemoteBackupDirectory

$local_backups | ForEach-Object ($_) {  Remove-Item -Force -Recurse $_.Path } | Where-Object { $remote_backups -eq $_.Size }