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)

1 thought on “Handling SMO SqlBackup Timeout

  1. Pingback: automssqlbackup Update « devioblog

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.