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.

Standalone SMO 2008 download

The Microsoft SQL Server 2008 Feature Pack (link to April 2009 version) contains tools and libraries for SQL Server 2008, among them the latest version of SQL Server Management Objects (SMO) which support 2000, 2005, and 2008.

SMO is the technology used by my applications SMOscript and automssqlbackup to query and control SQL Server databases.

automssqlbackup Update

The latest version 0.29 of automssqlbackup manages to backup large databases which timed out in previous versions.

File sizes are handled as [long] values now, and exceptions during SMO backups are handled correctly, fixing two bugs in the notification email.

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.

Forum open now

I set up a forum for all of my software that I describe in this blog:

• dbscript
• graspx
• automssqlbackup
• SMOscript

Please feel free to register and post.

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

automssqlbackup is available for download here.

automssqlbackup sends notifications as HTML

As automssqlbackup works fine on the machines that I installed it, there’s always opportunity to fine-tune. This time it’s the notification emails being sent.

I modified the PowerShell script to send HTML emails.

The first part of the email contains a table with the list of selected databases, the status (“OK” or message of an exception), the name of the generated file or archive, and the sizes of the .bak and .zip files.

The second part contains the script’s transcript (i.e. the original mail contents).

automssqlbackup is available for download here.

Updating automssqlbackup

A little bit of fine-tuning automssqlbackup:

After changing the zip functionality to SharpZipLib I modified the mail notifications to include the backup schedule (daily, weekly, monthly) in the subject, and also added an indicator whether the databases were backup up successfully.

automssqlbackup 0.27 is available for download here.