Exception calling "SqlBackup" with "1" argument(s)

We use the PowerShell script below to backup our SQL Server databases:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $dbInstance
$backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$backup.Action = "Database"
$backup.BackupSetDescription = "Full backup of " + $dbName
$backup.BackupSetName = $dbName + " backup"
$backup.Database = $dbName
$backup.MediaDescription = "Disk"
$backup.Devices.AddDevice("$localSqlBackupPath", "File")

This script runs fine for years, however recently it started to fail. It successfully backed up most databases, however the backup occasionally failed on some other databases which were previously backed up successfully. I found this error in the log:

Exception calling "SqlBackup" with "1" argument(s): 
"Backup failed for Server 'MyServer\MySqlInstance'. " At D:\Backups\BackupSite.ps1:151 char:22 + $backup.SqlBackup <<<< ($server) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

After a long investigation it turned out, that the error has nothing to do with how we call the SqlBackup function, instead the real issue is that it timeouts after 10 minutes. I turned off the timeout monitoring via the StatementTimeout property of the ServerConnection object and error is gone:

$server.ConnectionContext.StatementTimeout = 0


Technorati-címkék: ,

2 thoughts on “Exception calling "SqlBackup" with "1" argument(s)

  1. Carson organic seo

    Thanks for your personal marvelous posting! I definitely enjoyed reading it, you’re a great author.I will
    remember to bookmark your blog and will come back from now on.
    I want to encourage that you continue your great posts, have a nice holiday weekend!


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