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.

Zipping Files with PowerShell

When I developed automssqlbackup, one of the key features was to optionally zip the generated database backup file.

I found solutions like this which essentially create an empty zip archive, then create a Shell.Application object to access this archive, and add files using the CopyHere or MoveHere methods of that object.

However, this approach has some severe drawbacks:

• Zip file creation and access via Shell.Application can collide occasionally, so that the COM object cannot access the zip file

• If such a collision occurs, the shell will display a message box which the user has to confirm/cancel, and the caller (the Powershell script, in this case) is not notified about the error situation

• The MoveHere method does not seem to *move* in the sense that the original file is deleted after being added to the zip archive

• CopyHere and MoveHere execute asynchronously. The caller does not receive any feedback when the operation is complete, and has to query the number of files in the archive to find out.

(short rant: this is automation the Microsoft way. Provide a programming interface, and if things go wrong, display a message box. Provide functionality as asynchronous operation and not tell the caller when it’s finished. If this was *real* automation, the caller would receive an error notification and a success notification from the callee, and the caller would decide how to react)

Since these effects showed up soon after I tested my script on different machines, it was clear that zipping had to be solved more reliably: using SharpZipLib.

First SharpZipLib is loaded into the Powershell script:

[System.Reflection.Assembly]::LoadFrom("C:\path-to\ICSharpCode.SharpZipLib.dll")

I found there are 2 simple ways to create a zip archive and add a file to it:

Using ZipFile.Create() and *Update()

$zip = [ICSharpCode.SharpZipLib.Zip.ZipFile]::Create($zipname)
$zip.BeginUpdate()
$zip.Add($filename)
$zip.CommitUpdate()
$zip.Close()

Quick and easy, but with a drawback: the filenames are stored including their original path information.

Using FastZip.CreateZip()

$zip = New-Object ICSharpCode.SharpZipLib.Zip.FastZip
$zip.CreateZip($zipname, $filenamedir, $false, "\.bak$")

$filenamedir is the base directory of all files to be included in the zip, followed by the recursive flag.

The final parameter is a bit tricky: it is named FileFilter, but it is not a DOS-like filter (“*.*”), but rather a regular expression being evaluated on each file. In the example above, all files with extension .bak are selected.