2

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.

Community
  • 1
  • 1
Ken Stuber
  • 23
  • 4
  • Does `Backup-SQLDatabase` really *require* that the object those types? I would expect it would take plain strings, not instances of more complex objects. What does `Backup-SQLDatabase -?` say the command wants? 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. – Bacon Bits Apr 29 '15 at 13:30
  • It does not require it, no, but if I just pass the instance name and database name instead of a smo server object, I cannot set the timeout and any backups lasting longer than 10 minutes will timeout and fail. My fall backup is to continue to use our internally developed entirely smo-based cmdlet, but I'd really like to have less code to maintain :) The version conflict was one of my guesses too, and why I tried explicitly pre-loading specific versions of the smo assemblies beforehand. – Ken Stuber Apr 29 '15 at 13:35
  • I've edited the original post to include setting the Timeout just to better illustrate the issue with Backup-SQLDatabase that we're trying to work around. – Ken Stuber Apr 29 '15 at 13:38
  • 1
    [This](https://connect.microsoft.com/SQLServer/feedback/details/728027) claims 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`, but I trust Connect moderately less than I trust Yahoo! Answers. Obviously, that's not a full fix for you, but it might help out. – Bacon Bits Apr 29 '15 at 14:28
  • @Bacon Bits, I am already on that trail as it turns out and am testing that as I type. I was on SP1, just updated to SP2 and am trying again using the -Serverinstance and -Database parameters. – Ken Stuber Apr 29 '15 at 14:57
  • Seems to be working. While kind of a work-around, it does solve my problem. @Bacon Bits If you want to type that up as an answer, I'll mark it as such. Thanks! – Ken Stuber Apr 29 '15 at 15:02

1 Answers1

0

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.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66