AutoMySQLBackup Warning

When I replaced my original Ubuntu server with the (then) more current Ubuntu 18.04, I also moved MySQL databases and wanted to have them backed up regularly.

I had used the script AutoMySQLBackup which did the job fine, and installed it on the new machine. (When I first found the script, I adapted for PowerShell on Windows it to backup my SQL Server databases)

On the new machine, however, the tool mysqldump issued the warning

[Warning] Using a password on the command line interface can be insecure.

The change seems to have been introduced in MySQL 5.6, and the solution to the warning is documented in the MySQL 5.6 Reference Manual. There are also answers on SO regarding the warning

mysql_config_editor set --login-path=local --host=localhost --user=username --password

mysql --login-path=local -e "statement"

 

I guess I tried to change the .sh script to call mysqldump with –login-path instead of –user/–password.

As the original script is not maintained anymore, I found this fork of AutoMySQLBackup which is still active, and also documents the use of –login-path.

automssqlbackup 0.31 supports MS SQL Server 2012

automssqlbackup has been idle for 3 years now, not because I discontinued the script, but simply because it worked! (Thanks everybody for their feedback!)

With the arrival of a new version of SQL Server it was time to adjust the assembly references in the code.

What you will now find is a set of paths to default SQL Server SMO assembly locations, of which you need to activate the one that applies to your system.

automssqlbackup is available for download here.

automssqlbackup Update 0.30

A user of automssqlbackup notified me of two problems with the program:

  • If you try to backup a database running on a named SQL Server instance (server\instance), creation of the log file caused an error as the “\” is handled like a directory separator, and the server directory could not be found.
  • If there is an exception during backup (executed by SMO’s SqlBackup method), the exception message is not displayed.

Both problems are fixed in the latest version 0.30 of automssqlbackup.

Note: By default, automssqlbackup performs a full backup on Sundays, and incremental backups on all other days. So if you run automssqlbackup during the week, and you never (fully) backed up your databases before, you will get an exception for the databases without full backups.

automssqlbackup is available for download here.

Handling SMO SqlBackup Timeout

I hope I have finally tracked down a bug in automssqlbackup which caused an exception when creating backups of huge databases (DB ~40GB, backup file ~4GB): The SqlBackup operation simply timed out as the default command timeout is set to 10 minutes.

I found that there is a property called StatementTimeout in the ServerConnection class, so that you can set the timeout to infinity like so:

$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($dbhost)
$conn.StatementTimeout = 0
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)

$bk = New-Object Microsoft.SqlServer.Management.Smo.Backup
$bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$bk.Database = $dbname
$bk.Initialize = $False
$bk.Incremental = -not $full
$bk.Devices.AddDevice($backupfile, Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$bk.SqlBackup($srv)

Sending PowerShell Transcript by Mail

PowerShell provides the Start-Transcript and Stop-Transcript commandlets to record logging information in a log file.

Creating the log file

The simplest way to create a log file based on the current date is like this:

$now = Get-Date
$logfile = "c:\path\to\log\dir\file-" + $now.ToString("yyyy-MM-dd") + ".log"
Start-Transcript -path $logfile -force

Any output in the PowerShell console will now also be copied to the logfile.

To end logging, simply use

Stop-Transcript

Sending plain text email

After the log file has been closed, we parse it line-by-line, and append each line to a StringBuilder. Finally, the string contents of the StringBuilder is passed to an SmtpClient object to be sent:

$log = Get-Content $logfile

$body = New-Object System.Text.StringBuilder
foreach($line in $log)
{
    [void] $body.AppendLine($line.ToString())
}

$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom, $emailTo, $subject, $body.ToString())

Sending HTML email

Depending on the email client you use, mails containing plain text log files need not necessarily be displayed with a fixed-width font. To force monospace fonts, we need to enclose the text inside a <pre> tag within HTML, and explicitly create an HTML message object:

$body = New-Object System.Text.StringBuilder
[void] $body.AppendLine("<pre>");
foreach($line in $log)
{
    [void] $body.AppendLine($line.ToString())
}
[void] $body.AppendLine("</pre>");

$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $body.ToString())
$msg.IsBodyHTML = $true
$smtp.Send($msg)

Of course, the variables $emailFrom, $emailTo, $subject, and $smtpServer have to be defined according to your needs.