I have a PowerShell script that is used in a fairly complex automated process. One thing it does is create a database backup, and for that we are using the
Backup-SqlDatabase cmdlet. I am also using the
-InputObject parameter for the
Backup-SqlDatabase cmdlet so I can control the timeout of the backup operation. I had to do this because many of the backup and restore operations we are performing run longer than 10 minutes and were timing out. Here is the relevant snippet of code:
### Backup database. Changed to using SQL Connection object so I can control the timeout. $SQLServerConn = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServer $SQLServerConn.ConnectionContext.StatementTimeout = $StatementTimeout Backup-SqlDatabase -InputObject $SQLServerConn -Database $DatabaseName -BackupFile $DatabaseBackup
and here is the error that is being thrown:
Backup-SqlDatabase : Cannot bind parameter 'InputObject'. Cannot convert the "[server\instance]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server". At C:\Users\Homer\Documents\testrestore.ps1:44 char:33 + Backup-SqlDatabase -InputObject $SQLServerConn -Database $DatabaseName -BackupFi ... + ~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [Backup-SqlDatabase], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand
(The square brackets are added in the error message. "server\instance" is just a string.)
The server where we are running this process used to only have SQL 2012 client tools installed on it, and it was working fine. This error started being thrown when we installed a SQL 2014 instance on the same server.
Based on the error I believe it is a compatibility issue between the SMO connection object and the Backup-SqlDatabase cmdlet InputObject parameter, but I am having a hard time trying to figure out exactly why. I've only found 1 other reference to this problem, but the thread kind of dead-ended with no real root cause.
Any thoughts or things to try to help me isolate it?