1

Long time lurker, first time poster. I'm hoping somebody here can give me a hand trying to figure out an issue that's got me stumped. Essentially I am trying to write a Powershell script that will restore a multi-part SQL Server database to a remote server. I have messed around with the invoke-command and script-block features to a point where I understand how to use variables and parameters... However I am noticing some very interesting behaviour.

Here is my code right now:

cls
$strbaktest = "S:\Landing\testdb1.bak,S:\Landing\testdb2.bak"
echo "The backup files are $strbaktest"
write-host
$StrScrBlk = {param($StrBaktest);  &Restore-SqlDatabase -ServerInstance "mydbserver" -Database     testdb -BackupFile $strbaktest -ReplaceDatabase}
invoke-command -ComputerName mydbserver -ScriptBlock $StrScrBlk -argumentlist $strbaktest

This yields an error like this:

System.Data.SqlClient.SqlError: Cannot open backup device 'S:\Landing\testdb1.bak,S:\Landing\testdb2.bak'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).

However, if I hardcode the same path into my script block, it works:

cls
$StrScrBlk = {Restore-SqlDatabase -ServerInstance "mydbserver" -Database testdb -BackupFile S:\Landing\testdb1.bak,S:\Landing\testdb2.bak -ReplaceDatabase}
invoke-command -ComputerName mydbserver -ScriptBlock $StrScrBlk

The other interesting thing I noticed was that if I only have a single file to restore, using the first method with a variable works:

cls
$strbaktest = "S:\Landing\testdb.bak"
echo "The backup files are $strbaktest"
write-host
$StrScrBlk = {param($StrBaktest);  &Restore-SqlDatabase -ServerInstance "mydbserver" -Database     testdb -BackupFile $strbaktest -ReplaceDatabase}
invoke-command -ComputerName mydbserver -ScriptBlock $StrScrBlk -argumentlist $strbaktest

Hopefully this makes sense to somebody. I've spent a couple hours trying a bunch of different ways to get this to work - however I can't seem to figure it out. Any help or advice is appreciated.

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jason Lee
  • 11
  • 2

1 Answers1

0

The comma is an operator or identifier that separates elements of an array. Compare:

PS C:\> 1,2
1
2
PS C:\> "1,2"
1,2
PS C:\>

Try this:

$strbaktest = "S:\Landing\testdb1.bak","S:\Landing\testdb2.bak"

It's working with "x","c" . The comma is not required, as it is waiting for an array.

Leon Bambrick
  • 26,009
  • 9
  • 51
  • 75
Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Hey, thanks for getting back to me - I appreciate it. I tried your suggestion, however it removes the comma from the file path entirely... I need the comma to be in there. – Jason Lee Nov 18 '14 at 19:45