backup_s3_full.ps1

#Requires -Version 7
#Requires -Modules SqlServer
#Install-Module SqlServer -Force

Clear-Host

Import-Module SqlServer

### Статус Debug
$IsDebug = $false

### Какие БД пропускаем
$DBSkipNames = @(
    ''
)

### Какие БД не бекапить Diff
$DBSkipDiffBackup = @(
    'master',
    'model',
    'msdb'
)

### Настройки Invoke-SqlCmd
$ServerInstance = 'localhost'

### Часы для полного бекапа
$FullBackupHours = 0..5

### Пути для бекапов
$DiffBackupPath = 'E:\backups\diff'
$FullBackupPath = 'E:\backups\full'

### Путь к утилите AWS
$AWSFullPath = 'C:\Program Files\Amazon\AWSCLIV2\aws.exe'

### Путь к логу
$LogPath = Join-Path 'C:\Scripts\log_backup' ((Get-Date -Format 'yyyy-MM-dd_HH-mm-ss') + '.log')

### Были ли ошибки при бекапе
$IsBackupErrors = $false

### Были ли ошибки SQL
$IsBackupSQLErrors = $false

### Были ли ошибки AWS
$IsAWSErrors = $false

### Были ли ошибки удаления файла бекапа
$IsDeleteBackupError = $false

### /START Functions
### Логирование
function Log ([string]$Message, $LogLevel, $Exception)
{
    if ($Exception -notlike '')
    {
        (Get-Date -Format 'dd.MM.yyyy HH:mm:ss.fff') + ' | ERR | ' + $Message + ' | ' + $Exception + ' строка: ' + $Error[0].InvocationInfo.ScriptLineNumber | `
                Out-File -FilePath $LogPath -Encoding utf8NoBOM -Append
    }
    else
    {
        if ($LogLevel -like '')
        {
            (Get-Date -Format 'dd.MM.yyyy HH:mm:ss.fff') + ' | INF | ' + $Message | `
                    Out-File -FilePath $LogPath -Encoding utf8NoBOM -Append
        }
        else
        {
            if (($LogLevel -eq 'DBG') -and (!($IsDebug)))
            {
                ### Не логировать Debug если Debug выключен
            }
            else
            {
                (Get-Date -Format 'dd.MM.yyyy HH:mm:ss.fff') + ' | ' + $LogLevel + ' | ' + $Message | `
                        Out-File -FilePath $LogPath -Encoding utf8NoBOM -Append
            }
        }
        

    }
}

function Invoke-SqlBackup
{
    [CmdletBinding()]

    Param (
        [Parameter(Mandatory = $true,
            Position = 0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [ValidateSet("Full", "Diff")]
        $BackupType,

        [Parameter(Mandatory = $true,
            Position = 0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        $DBName
    )
    
    Begin
    {
        
    }
    Process
    {
        if ($BackupType -eq 'Full')
        {
            $DBBackupName = $DBName + '_Full_' + (Get-Date -Format 'yyyy-MM-dd_HH-mm-ss')
            $DBBackupFullPath = Join-Path $FullBackupPath ($DBBackupName + '.bak')
        
            $QueryBackup = "BACKUP DATABASE [$DBName] TO DISK = N'$DBBackupFullPath' WITH NOFORMAT, INIT, NAME = N'$DBBackupName', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"
        }
        else
        {
            $DBBackupName = $DBName + '_Diff_' + (Get-Date -Format 'yyyy-MM-dd_HH-mm-ss')
            $DBBackupFullPath = Join-Path $DiffBackupPath ($DBBackupName + '.bak')

            $QueryBackup = "BACKUP DATABASE [$DBName] TO DISK = N'$DBBackupFullPath' WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N'$DBBackupName', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"
        }
        
        Log -LogLevel DBG -Message ('Имя бекапа: ' + $DBBackupName)
        Log -LogLevel DBG -Message ('Путь бекапа: ' + $DBBackupFullPath)
        Log -LogLevel DBG -Message ('TSQL для бекапа: ' + $QueryBackup)

        ### TSQL Full
        try
        {
            Log -Message ($DBName + ': Приступаем к бекапу')
            $BackupStartTime = Get-Date
            Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $QueryBackup -ErrorAction Stop
            [timespan]$BackupTimeSpan = (Get-Date) - $BackupStartTime
            Log -Message ($DBName + ': Бекап успешно завершён. Приступаем к копированию')
            Log -LogLevel DBG -Message ('Бекап прошёл за: ' + $BackupTimeSpan)

            $IsBackupSQLErrors = $false
        }
        catch
        {
            Log -Message ('Ошибка Invoke-SqlCmd') -Exception $Error[0].Exception.Message
            $IsBackupSQLErrors = $true
            $IsBackupErrors = $true
        }
    }
    End
    {
        $HashTable = [ordered]@{
            'DBName'            = $DBName
            'DBBackupFullPath'  = $DBBackupFullPath
            'IsBackupSQLErrors' = $IsBackupSQLErrors
            'IsBackupErrors'    = $IsBackupErrors
        }

        return New-Object -TypeName psobject -Property $HashTable
    }
}

function Copy-S3
{
    [CmdletBinding()]

    Param (
        [Parameter(Mandatory = $true,
            Position = 0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [ValidateSet("Full", "Diff")]
        $BackupType,

        [Parameter(Mandatory = $true,
            Position = 0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        $DBBackupFullPath,

        [Parameter(Mandatory = $true,
            Position = 0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [bool]$IsBackupSQLErrors
    )
    
    Begin
    {
        $DBBackupFileName = Split-Path $DBBackupFullPath -Leaf
        if ($BackupType -eq 'Full')
        {
            $AWSArgumentList = "--endpoint-url=https://storage.твой_s3 s3 cp $DBBackupFullPath s3://куда_то-s3/backups/full/$DBBackupFileName"
        }
        else
        {
            $AWSArgumentList = "--endpoint-url=https://storage.твой_s3 s3 cp $DBBackupFullPath s3://куда_то-s3/backups/diff/$DBBackupFileName"
        }
    }
    Process
    {
        if ($IsBackupSQLErrors)
        {
            Log -LogLevel WRN -Message ($DBName + ': Пропускаем копирование в облако, ошибки предыдущего шага')
        }
        else
        {
            $AWSStartTime = Get-Date
            $AWSExitCode = (Start-Process -FilePath $AWSFullPath -ArgumentList $AWSArgumentList -NoNewWindow -PassThru -Wait).ExitCode
            if ($AWSExitCode -eq 0)
            {
                [timespan]$AWSTimeSpan = (Get-Date) - $AWSStartTime
                Log -Message ($DBName + ': Копирование в облако успешно завершено')
                Log -LogLevel DBG -Message ('Копирование заняло: ' + $AWSTimeSpan)
            }
            else
            {
                Log -LogLevel ERR -Message ('Ошибка AWSCLIv2. Exit code: ' + $AWSExitCode.ToString())
                $IsAWSErrors = $true
                $IsBackupErrors = $true
            }
        }
    }
    End
    {
        $HashTable = [ordered]@{
            'DBName'           = $DBName
            'DBBackupFullPath' = $DBBackupFullPath
            'IsAWSErrors'      = $IsAWSErrors
            'AWSExitCode'      = $AWSExitCode
            'IsBackupErrors'   = $IsBackupErrors
        }

        return New-Object -TypeName psobject -Property $HashTable
    }
}

function Remove-AfterBackup
{
    [CmdletBinding()]

    Param (
        [Parameter(Mandatory = $true,
            Position = 0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        $DBBackupFullPath,

        [Parameter(Mandatory = $true,
            Position = 0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [bool]$IsBackupSQLErrors,

        [Parameter(Mandatory = $true,
            Position = 0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [bool]$IsAWSErrors
    )
    
    Begin
    {
    }
    Process
    {
        if (($IsBackupSQLErrors) -or ($IsAWSErrors))
        {
            Log -LogLevel WRN -Message ($DBName + ': Пропускаем удаление файла бекапа, ошибки предыдущего шага')
        }
        else
        {
            try
            {
                Remove-Item -Path $DBBackupFullPath -Force -ErrorAction Stop
                Log -Message 'Успешно удалили файл после загрузки в облако'
            }
            catch
            {
                Log -LogLevel ERR -Message ('Ошибка удаления файла бекапа') -Exception $Error[0].Exception.Message
                $IsDeleteBackupError = $true
                $IsBackupErrors = $true
            }
        }
    }
    End
    {
        $HashTable = [ordered]@{
            'IsDeleteBackupError' = $IsDeleteBackupError
            'IsBackupErrors'      = $IsBackupErrors
        }

        return New-Object -TypeName psobject -Property $HashTable
    }
}

### Получить текущий час
function Get-CurrentHour
{
    $CurrentHour = (Get-Date).Hour

    return $CurrentHour
}
### /END Functions

### Время запуска скрипта
$ScriptStartTime = Get-Date
Log -LogLevel DBG -Message ('Запуск скрипта в {0}' -f $ScriptStartTime)

$Query = "SELECT db.name, db.database_id, db.state, db.state_desc FROM sys.databases AS db ORDER BY db.name"

$RawDBNames = Invoke-Sqlcmd -ServerInstance localhost -Query $Query | Where-Object { $_.name -notin $DBSkipNames }
if ((Get-CurrentHour) -notin $FullBackupHours) { $RawDBNames = $RawDBNames | Where-Object { $_.name -notin $DBSkipDiffBackup } }

foreach ($RawDBName in $RawDBNames)
{
    $DBName = $RawDBName.name
    $DBStatus = $RawDBName.state
    Log -LogLevel DBG -Message ('Проверяем возможность бекапа БД: {0}' -f $DBName)
    
    if ($DBStatus -eq 0)
    {
        if ((Get-CurrentHour) -in $FullBackupHours)
        {
            Log -Message 'Режим бекапа: Full'

            ### SQL Full
            $SQLBackupResults = Invoke-SqlBackup -BackupType Full -DBName $DBName
            if ($SQLBackupResults.IsBackupSQLErrors) {$IsBackupErrors = $true}

            ### AWS Full
            $AWSResult = Copy-S3 -BackupType Full -DBBackupFullPath $SQLBackupResults.DBBackupFullPath -IsBackupSQLErrors $SQLBackupResults.IsBackupSQLErrors
            if ($AWSResult.IsAWSErrors) {$IsBackupErrors = $true}

            ### Удалить после успешной заливки
            $RemoveBackupResult = Remove-AfterBackup -DBBackupFullPath $AWSResult.DBBackupFullPath -IsBackupSQLErrors $SQLBackupResults.IsBackupSQLErrors -IsAWSErrors $AWSResult.IsAWSErrors
            if ($RemoveBackupResult.IsDeleteBackupError) {$IsBackupErrors = $true}
        }
        else
        {
            Log -Message 'Режим бекапа: Diff'

            ### SQL Diff
            $SQLBackupResults = Invoke-SqlBackup -BackupType Diff -DBName $DBName
            if ($SQLBackupResults.IsBackupSQLErrors) {$IsBackupErrors = $true}

            ### AWS Diff
            $AWSResult = Copy-S3 -BackupType Diff -DBBackupFullPath $SQLBackupResults.DBBackupFullPath -IsBackupSQLErrors $SQLBackupResults.IsBackupSQLErrors
            if ($AWSResult.IsAWSErrors) {$IsBackupErrors = $true}

            ### Удалить после успешной заливки
            $RemoveBackupResult = Remove-AfterBackup -DBBackupFullPath $AWSResult.DBBackupFullPath -IsBackupSQLErrors $SQLBackupResults.IsBackupSQLErrors -IsAWSErrors $AWSResult.IsAWSErrors
            if ($RemoveBackupResult.IsDeleteBackupError) {$IsBackupErrors = $true}
        }
        
    }
    else
    {
        Log -LogLevel WRN -Message ($DBName + ' не в сети, статус: ' + $DBStatus + '. Пропускаем')
    }
}

if ($IsBackupErrors)
{
    [timespan]$StopScriptTimeSpan = (Get-Date) - $ScriptStartTime
    Log -Message ('Скрипт отработал с ошибками за: ' + $StopScriptTimeSpan)
    Log -LogLevel DBG -Message ('Последняя ошибка: {0} ({1})' -f $Error[0].Exception.Message, $Error[0].InvocationInfo.PositionMessage)
    Exit 1
}
else
{
    [timespan]$StopScriptTimeSpan = (Get-Date) - $ScriptStartTime
    Log -Message ('Скрипт успешно отработал за: ' + $StopScriptTimeSpan)
    Log -LogLevel DBG -Message ('Последняя ошибка: {0} ({1})' -f $Error[0].Exception.Message, $Error[0].InvocationInfo.PositionMessage)
}