asked on

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

Jeremy Weisinger

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'

bibi92

ASKER

Yes thanks

bibi92

ASKER

I try this :
$DumpfileList -join ","

But it does not work.

Thanks

Regards

Jeremy Weisinger

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

bibi92

ASKER

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 Weisinger

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

bibi92

ASKER

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

bibi92

ASKER

and the result is empty. Thanks regards

Jeremy Weisinger

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?

bibi92

ASKER

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

ASKER CERTIFIED SOLUTION

Jeremy Weisinger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bibi92

ASKER

Thanks a lot regards