Sql-server – Backup-SqlDatabase: Cannot bind parameter ‘InputObject’ Error Being Thrown

powershellsql server

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?

Best Answer

Please check out my blog for a detailed explanation of the problem.
I was having the same issue, blogged after I couldn't find a lot of information online.

The solution comes down to importing only the required module, in stead of the modules for several versions of SQL Server.

First check what modules are being loaded:

# Get loaded assemblies
([appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like "*smo*"}).Location

Next make sure your session is closed if you already loaded the modules, then use this modified script to load only the modules you need:

$TempArray = @()
$TempArray = $env:PSModulePath -split ';'
# 110 for SQL 2012, 120 for SQL 2014, 130 for SQL 2016
$env:PSModulePath = ($TempArray -notmatch '110') -join ';'

Now, load SQLPS module and run commands.

Related Question