Search code examples
sqlsql-serverpowershellsmosqlps

Unable to use SQLPS CmdLet Backup-SQLDatabase with -InputObject parameter

Having run into the same timeout issue found here and trying the workaround, I'm finding that I get the error:

Backup-SqlDatabase : Cannot bind parameter 'InputObject'. Cannot convert the "[instDW_QA0]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type "Microsoft.SqlServer.Management.Smo.Server".
At line:21 char:50
+   Backup-SQLDatabase -Database msdb -InputObject $server -BackupAction Database  ...
+                                                  ~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Backup-SqlDatabase], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

when running the simple script:

$ServerName = "instDW_QA0"
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $ServerName
$server.ConnectionContext.StatementTimeout = 0
$db = $server.Databases["msdb"]
Backup-SQLDatabase -Database msdb -InputObject $server -BackupAction Database -CompressionOption ON -CopyOnly -Initialize

This occurs on the three Win7 machines I've tried it on, but the script runs fine on all the servers (Win2012) I've tried it on. The three Win7 machines have a mix of installed software. One has SQL 2008R2, 2012 and 2014 installed, another had only 2008R2 and 2012. One had PS 2.0 installed, two others had PS 3.0. The servers are again a mix. All have PS 3.0 installed, but some have SQL2008R2 and SQL2012, some have only SQL2012 and one I tried it on had only SQL2014, but they all worked. The only consistent difference is the OS, Win7 vs Win2012, but that doesn't make a lot of sense to me as a potential cause.

The error itself is confusing, the two types it references are identical. I've tried loading specific SMO assembly versions (11 and 12) prior to running the script and that doesn't seem to help.

Any ideas? thanks in advance.

Solution

  • Offhand, I'd guess that the objects might be being created using different versions of the same library that Backup-SQLDatabase uses, and that's why some servers complain and others work just fine.

    Since you're only using this method as a workaround to the timeout issue of Backup-SqlDatabase, it may be useful to know that the same issue is fixed for Restore-SQLDatabase in Cumulative update package 8 for SQL Server 2012 SP1. I would think that would also affect Backup-SQLDatabase. That might work around the timeout issue if you can't fix the error you're getting with Backup-SQLDatabase.