concatene variable and join with comma

Hello

I search to build $FROMCLAUSE DISK = N'\\nas\backup\Gdete.20160330_124543.1_3.dmp', DISK= N'\\nas\backup\Gdete.20160330_124543.2_3.dmp', DISK= N'\\nas\backup\Gdete.20160330_124543.3_3.dmp'

$DumpDir="\\nas\backup"
$BackupFileList = @(Invoke-SQLCmd -query "set nocount on; SELECT physical_device_name FROM msdb.dbo.backupmediafamily CROSS JOIN (SELECT max_value = MAX(media_set_id) FROM msdb.dbo.backupset b where b.type = 'D' and database_name = 'gdete') m WHERE media_set_id = max_value" -Server test\srv)
Foreach($file in $BackupFileList){$DumpfileList += @([System.IO.Path]::GetFileName($file.physical_device_name))}
The result is
Gdete.20160330_124543.1_3.dmp
Gdete.20160330_124543.2_3.dmp
Gdete.20160330_124543.3_3.dmp

I try this :
$FROMCLAUSE=($DumpfileList  |Select-object @{name="file.physical_device_name";expression={"DISK = N'"+$dumpdir+$file.physical_device_name +"'"}} | Select -ExpandProperty file.physical_device_name) -join ","

But the result is not correct

DISK = N'\\nas\backupN:\MSSQL\backup\Gdat.20160330_124543.3_3.dmp',DISK = N'\\nas\backupN:\MSSQL\backup\Gdat.20160330_124543.3_3.dmp', DISK = N'\\nas\backupN:\MSSQL\backup\Gdat.20160330_124543.3_3.dmp'

How can I resolve ?

Thanks

Regards
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeremy WeisingerSenior Network Consultant / EngineerCommented:
I'm trying to figure out what you're going for here:
$FROMCLAUSE DISK = N'\\nas\backup\Gdete.20160330_124543.1_3.dmp', DISK= N'\\nas\backup\Gdete.20160330_124543.2_3.dmp', DISK= N'\\nas\backup\Gdete.20160330_124543.3_3.dmp'
Should $FROMCLAUSE be a string that contains the following in it?
DISK = N'\\nas\backup\Gdete.20160330_124543.1_3.dmp', DISK= N'\\nas\backup\Gdete.20160330_124543.2_3.dmp', DISK= N'\\nas\backup\Gdete.20160330_124543.3_3.dmp'
bibi92Author Commented:
Yes thanks
bibi92Author Commented:
I try this :
$DumpfileList  -join ","

But it does not work.

Thanks

Regards
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Jeremy WeisingerSenior Network Consultant / EngineerCommented:
Try this:
$FROMCLAUSE=($DumpfileList  |select @{n='path';e={$_.physical_device_name.Replace('N:\MSSQL','DISK = N''\\nas\backup') + "'"}} | select -ExpandProperty path) -join ","

Open in new window

bibi92Author Commented:
Sorry it is not the result, the goal is only to concatene and join with a comma DISK = N'\\nas\backup\Gdete.20160330_124543.1_3.dmp', DISK= N'\\nas\backup\Gdete.20160330_124543.2_3.dmp', DISK= N'\\nas\backup\Gdete.20160330_124543.3_3.dmp'

Thanks

Regards
Jeremy WeisingerSenior Network Consultant / EngineerCommented:
OK, what is the output you're getting and what would you like it to be?
bibi92Author Commented:
I don't want to Replace('N:\MSSQL','DISK = N''\\nas\backup') + "'"}}, these value can be change.
bibi92Author Commented:
and the result is empty. Thanks regards
Jeremy WeisingerSenior Network Consultant / EngineerCommented:
Sorry but without being able to construct the path from logic or having it consistent, there's no way to do this. Powershell cannot guess the path you may want. It needs to be able to get that information from somewhere.

and the result is empty.
So the $FROMCLAUSE variable is empty?
bibi92Author Commented:
yes the $FROMCLAUSE variable is empty ;
$DumpDir="\\nas\backup"
$BackupFileList = @(Invoke-SQLCmd -query "set nocount on; SELECT physical_device_name FROM msdb.dbo.backupmediafamily CROSS JOIN (SELECT max_value = MAX(media_set_id) FROM msdb.dbo.backupset b where b.type = 'D' and database_name = 'gdete'
Foreach($file in $BackupFileList){ $DumpfileList += @( "DISK = N'"+$DumpDir + "\" + [System.IO.Path]::GetFileName($file.physical_device_name) +  "'") }
echo $Dumpfilelist
DISK = N'\\nas\backup\Gdete.20160330_124543.1_3.dmp'
DISK = N'\\nas\backup\Gdete.20160330_124543.2_3.dmp'
DISK = N'\\nas\backup\Gdete.20160330_124543.3_3.dmp'

so it is not possible to concatene $DumpfileList with comma for having
DISK = N'\\nas\backup\Gdete.20160330_124543.1_3.dmp',DISK = N'\\nas\backup\Gdete.20160330_124543.2_3.dmp',DISK = N'\\nas\backup\Gdete.20160330_124543.3_3.dmp'

Thanks

regards
Jeremy WeisingerSenior Network Consultant / EngineerCommented:
Ohhhhhhhhh, geez, I see what you're doing. OK, you're referencing a variable from a loop. Once the loop is finished it won't change. Try this:
$DumpDir="\\nas\backup"
$BackupFileList = @(Invoke-SQLCmd -query "set nocount on; SELECT physical_device_name FROM msdb.dbo.backupmediafamily CROSS JOIN (SELECT max_value = MAX(media_set_id) FROM msdb.dbo.backupset b where b.type = 'D' and database_name = 'gdete') m WHERE media_set_id = max_value" -Server test\srv) 
 $FROMCLAUSE = $(Foreach($file in $BackupFileList){'DISK = N''' + $dumpdir + '\' + $([System.IO.Path]::GetFileName($file.physical_device_name))}) -join "," 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bibi92Author Commented:
Thanks a lot regards
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.