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.

Improve this question
6
  • 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. Commented Apr 29, 2015 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. Commented Apr 29, 2015 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. Commented Apr 29, 2015 at 13:38
  • 1
    This 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. Commented Apr 29, 2015 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. Commented Apr 29, 2015 at 14:57

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.