Powershell backup and then send email. send email has issues

Dyan Marquez 0 Reputation points
2024-06-04T07:34:32.9666667+00:00

<#

Script: Backup database with email notification

Version 1.3 - May. 31, 2024

This script performs full and transaction log backups of the Mobile database and sends email notifications.

#>

Variables

$serverName = "TFS"

$databaseName = "Mobile"

$backupPath = "\10.0.0.228\Backup"

$timestamp = (Get-Date).ToString("yyyyMMddHHmmss")

$logFilePath = "$backupPath\MobileBackupLog_$timestamp.log"

Email configuration

$smtpServer = "ded4829.inmotionhosting.com"

$smtpPort = 587

$from = "donotreply@email.alliancewebpos.com"

$to = "dmarquez@asi-dev6.com"

$subject = "Mobile Backup Notification"

$mailUsername = "donotreply@email.alliancewebpos.com"

$mailPassword = "!D0n0tR3pLyp4zz82!x!"

Function to log messages to a file

function Log-Message {

param (

    [string]$message,

    [string]$logFile

)

Add-content -Path $logFile -Value "$([DateTime]::Now) - $message"

}

Function to run a SQL command using sqlcmd and log the result

function Run-SqlCommand {

param (

    [string]$server,

    [string]$query,

    [string]$backupType,

    [string]$filePath,

    [string]$logFile

)

try {

    $cmd = "sqlcmd -S $server -Q `"$query`""

    Invoke-Expression $cmd

    if (Test-Path $filePath) {

        Log-Message "Successfully created $backupType backup file: $filePath" -logFile $logFile

    } else {

        Log-Message "$backupType backup file not found: $filePath" -logFile $logFile

    }

}

catch {

    Log-Message "Error executing $backupType backup: $query. Error: $_" -logFile $logFile

}

}

Function to send email notification

function Send-EmailNotification {

param (

    [string]$smtpServer,

    [int]$smtpPort,

    [string]$from,

    [string]$to,

    [string]$subject,

    [string]$username,

    [string]$password,

    [string]$body,

    [string]$logFile

)

try {

    $smtpClient = New-Object Net.Mail.SmtpClient($smtpServer, $smtpPort)

    $smtpClient.EnableSsl = $true

    $smtpClient.Credentials = New-Object System.Net.NetworkCredential($username, $password)

    

    $mailMessage = New-Object Net.Mail.MailMessage

    $mailMessage.From = $from

    $to.Split(",") | ForEach-Object { $mailMessage.To.Add($_.Trim()) }

    $mailMessage.Subject = $subject

    $mailMessage.Body = $body

    

    $smtpClient.Send($mailMessage)

    Log-Message "Email notification sent successfully." -logFile $logFile

} catch {

    Log-Message "Failed to send email notification. Exception type: $($_.Exception.GetType().FullName)" -logFile $logFile

    Log-Message "Exception message: $($_.Exception.Message)" -logFile $logFile

    Log-Message "Stack trace: $($_.Exception.StackTrace)" -logFile $logFile

    

    if ($_.Exception.InnerException) {

        Log-Message "Inner exception type: $($_.Exception.InnerException.GetType().FullName)" -logFile $logFile

        Log-Message "Inner exception message: $($_.Exception.InnerException.Message)" -logFile $logFile

        Log-Message "Inner exception stack trace: $($_.Exception.InnerException.StackTrace)" -logFile $logFile

    }

}

}

Ensure backup directory exists

if (-not (Test-Path $backupPath)) {

New-Item -Path $backupPath -ItemType Directory

}

Create a timestamp for the backup filenames

$backupPathFull = "$backupPath\Mobile_Full_$timestamp.bak"

$backupPathTrn = "$backupPath\Mobile_Log_$timestamp.trn"

Full backup T-SQL command

$backupFull = "BACKUP DATABASE [$databaseName] TO DISK = N'$backupPathFull' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, STATS = 10"

Transaction log backup T-SQL command

$backupTrn = "BACKUP LOG [$databaseName] TO DISK = N'$backupPathTrn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, STATS = 10"

Perform the full backup

Log-Message "Starting full backup of database '$databaseName'..." -logFile $logFilePath

Run-SqlCommand -server $serverName -query $backupFull -backupType "full" -filePath $backupPathFull -logFile $logFilePath

Perform the transaction log backup

Log-Message "Starting transaction log backup of database '$databaseName'..." -logFile $logFilePath

Run-SqlCommand -server $serverName -query $backupTrn -backupType "transaction log" -filePath $backupPathTrn -logFile $logFilePath

Final log message

$backupStatus = "Success" # Assume success unless an error is encountered

Log-Message "Backup process completed with status: $backupStatus." -logFile $logFilePath

Read the content of the log file

$logFileContent = Get-Content $logFilePath -Raw

Send email notification

$emailBody = @"

Backup process for database '$databaseName' completed.

Backup files:

  • Full backup: $backupPathFull
  • Transaction log backup: $backupPathTrn

Log file content:

$logFileContent

"@

try {

Send-EmailNotification -smtpServer $smtpServer -smtpPort $smtpPort -from $from -to $to -subject $subject -username $mailUsername -password $mailPassword -body $emailBody -logFile $logFilePath

} catch {

$backupStatus = "Failed"

Log-Message "Failed to send email notification. Backup status: $backupStatus. Exception: $_" -logFile $logFilePath

}


Error below:

06/04/2024 15:16:50 - Failed to send email notification. Exception type: System.Management.Automation.MethodInvocationException

06/04/2024 15:16:50 - Exception message: Exception calling "Send" with "1" argument(s): "Failure sending mail."

06/04/2024 15:16:50 - Stack trace: at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)

at CallSite.Target(Closure , CallSite , Object , Object )

at System.Dynamic.UpdateDelegates.UpdateAndExecute2T0,T1,TRet

at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)

at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

06/04/2024 15:16:50 - Inner exception type: System.Net.Mail.SmtpException

06/04/2024 15:16:50 - Inner exception message: Failure sending mail.

06/04/2024 15:16:50 - Inner exception stack trace: at System.Net.Mail.SmtpClient.Send(MailMessage message)

at CallSite.Target(Closure , CallSite , Object , Object )

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,435 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,237 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 42,576 Reputation points
    2024-06-04T09:31:14.4433333+00:00

    $backupPath = "\10.0.0.228\Backup"

    If you want to backup to an UNC path then you have to use double-backslash; fix that first =>

    $backupPath = "\\10.0.0.228\Backup"
    
    

  2. Plamen Peev 80 Reputation points
    2024-06-04T23:37:48.7833333+00:00

    Hi @Dyan Marquez ,

    I tested your Send-EmailNotification function with active email via smtp.outlook.com and it works fine. It looks like a problem with the internal configuration of the SMTP server that you have provided.

    If this is indeed the case, please mark my answer as "Accepted" and perhaps come back and share the solution.