Remote Microsoft SQL Server Database Restore with Powershell and Script Block

Question

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!

Answer 1

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.