Tag Archives: PowerShell

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

5 Replies

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")
$backup.SqlBackup($server)

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: ,