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)

One Response to Handling SMO SqlBackup Timeout

  1. […] The latest version 0.29 of automssqlbackup manages to backup large databases which timed out in previous […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: