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?

  • 1
    Have you tried skipping the SMO and just adding -ConnectionTimeout to Backup-SQLDatabase "Backup-SqlDatabase -Database $DatabaseName -ServerInstance $SQLServer -BackupAction Database -BackupFile $BackupFile -ConnectionTimeout 0" – Spörri May 28 '15 at 14:24
  • 1
    Since you've installed SQL Server 2014 on this instance, powershell is likely trying to load the incorrect version of the assembly Microsoft.SqlServer.Management.Smo.Server. If you place the correct copy of that assembly in the same folder as the script itself, powershell will load that assembly, and not the default one from the system cache. – Max Vernon May 28 '15 at 15:18
  • It's a bug the Backup-SQLDatabase cmdlet will not accept the timeout parameter - there should be a hotfix for that: connect.microsoft.com/SQLServer/feedback/details/805087/… – Spörri May 28 '15 at 15:28
  • Max, those are my thoughts as well. I guess I don't understand the compatibility issue when I am loading everything using Import-Module SQLPS. – cjsommer May 28 '15 at 16:49
  • Sporri, yeah, that was why I went down the road I did using InputObject instead of ServerInstance parameter. I have a couple fixes in mind, but am just curious as to why it is behaving this way. – cjsommer May 28 '15 at 16:50

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.