SQL backup automation and upload to S3 with email notification

powershell

 

Hi Guys,

From past some days i’m trying to find a way to automate SQL Server databases backup to AWS S3.

So i choose to use powershell script to automate this with email notification.

Prerequisites:- 1] Need to create a user who have programmatic access to AWS S3. (How to create ? see here..)

2] Install AWSCLI on computer from which you need to run / schedule a script.

3] Encrypt DB password, SMTP email password to use in script.

Open Powershell and run below commands one by one to create encrypted password

PS C:\Users\Administrator>$password = "Original password"
PS C:\Users\Administrator> $secureStringPwd = $password | ConvertTo-SecureString -AsPlainText -Force
PS C:\Users\Administrator> $secureStringText = $secureStringPwd | ConvertFrom-SecureString
PS C:\Users\Administrator> Set-Content C:\scripts\dbpass.txt $secureStringText

Now created txt files have your encrypted password ready to use with our script.

repeat same to create encrypted email password.

 

Copy below code and create file “Backup.ps1”

Change script variables and SMTP server, Port number, email recipient, DB name, Server, AWS S3 bucket path etc according to your requirement.

 
$usernameSQL = "sa"
$pwdTxt = Get-Content "C:\scripts\dbpass.txt"
$securePwd = $pwdTxt | ConvertTo-SecureString 
$credObject = New-Object System.Management.Automation.PSCredential -ArgumentList $usernameSQL, $securePwd
$Server = "SQL Server Name"
$DB = "DB Name"
$Directory = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\"
$date = get-date -f "yyyy-MM-dd hh-mm tt" 
$name = "MyBk-$date.bak"
$BKFile = "$Directory$name"
$eUsername = "SMTP Email ID";
$epass = Get-Content "C:\scripts\emailpass.txt"
$sepass = $epass | ConvertTo-SecureString
$ecredObject = New-Object System.Management.Automation.PSCredential -ArgumentList $eUsername, $sepass


try{ 
    $i = 1
    Backup-SqlDatabase -ServerInstance $Server -Database $DB -SqlCredential $credObject -BackupFile $BKFile -ErrorAction Stop
    aws s3 cp $BKFile s3://test-bucket-name/folder-name/
    rm $BKFile -ErrorAction Stop
    
}
catch{

    $i = 0
}

if($i -eq 1){

function Send-ToEmail([string]$email){

    $message = new-object Net.Mail.MailMessage;
    $message.From = "SMTP Email Id";
    $message.To.Add($email);
    $message.Subject = "Backup of DB $DB successfull !";
    $message.Body = "Hi ! Backup of DB $DB successfull on $date and file name is $name";
    
    $smtp = new-object Net.Mail.SmtpClient("smtp.your smtp server.com", "587");
    $smtp.EnableSSL = $true;
    $smtp.Credentials = $ecredObject;
    $smtp.send($message);
    write-host "Mail Sent" ; 
    
 }
Send-ToEmail  -email "User1@abc.com,User2@abc.com" ;

}
else {
        
        function Send-ToEmail([string]$email){

    $message = new-object Net.Mail.MailMessage;
    $message.From = "Your SMTP mail id here";
    $message.To.Add($email);
    $message.Subject = "Backup of DB $DB Failed !";
    $message.Body = "Hi !  Backup of DB $DB Failed on $date and file name is $name";
    
    $smtp = new-object Net.Mail.SmtpClient("smtp.your smtp server.com", "587");
    $smtp.EnableSSL = $true;
    $smtp.Credentials = $ecredObject;
    $smtp.send($message);
    write-host "Mail Sent" ; 
    
 }
Send-ToEmail  -email "User1@abc.com,User2@abc.com" ;
}

After this you can simply put this script in windows scheduler.

Note:- After installing awscli, If you face problem

'awscli' is not recognized as an internal or external command,
operable program or batch file.

Then try setting up path variable in windows environment variables.

If nothing works simply add  below line just before “aws s3 cp” command in try block.

 cd "C:\Program Files\Amazon\AWSCLI\bin"

This script is working flawlessly for me, If any of you guys facing any trouble, let me know in comments.

Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.