concatene variable and join with comma

bibi92
bibi92 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeremy WeisingerSenior Network Consultant / Engineer

Commented:
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'

Author

Commented:
Yes thanks

Author

Commented:
I try this :
$DumpfileList  -join ","

But it does not work.

Thanks

Regards
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Jeremy WeisingerSenior Network Consultant / Engineer

Commented:
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

Author

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 / Engineer

Commented:
OK, what is the output you're getting and what would you like it to be?

Author

Commented:
I don't want to Replace('N:\MSSQL','DISK = N''\\nas\backup') + "'"}}, these value can be change.

Author

Commented:
and the result is empty. Thanks regards
Jeremy WeisingerSenior Network Consultant / Engineer

Commented:
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?

Author

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
Senior Network Consultant / Engineer
Commented:
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

Author

Commented:
Thanks a lot regards

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial