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 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.

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.

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.

‘Backup failed for Server’ raised in SMO Backup

Since I developed the first version of my script automssqlbackup, I kept getting a ‘Backup failed for Server’ exception on one specific database. All other databases created their backups without error. The only visible difference between the failing database and all other is its size: some 40 GB which generate a differential backup of about 500MB per day.

Some research brought, among numerous questions in forums, this MS KB entry: FIX: You receive an error message when you try to create a differential database backup in SQL Server 2005. It provides a hotfix and indicates that the problem might be fixed in a post-SP1 service pack.

I applied SP3 to SQL Server 2005, and the script can now backup all databases on the server successfully.