Avatar of bibi92
bibi92Flag for France

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
Powershell

Avatar of undefined
Last Comment
bibi92
Avatar of Jeremy Weisinger
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'
Avatar of bibi92
bibi92
Flag of France image

ASKER

Yes thanks
Avatar of bibi92
bibi92
Flag of France image

ASKER

I try this :
$DumpfileList  -join ","

But it does not work.

Thanks

Regards
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

Avatar of bibi92
bibi92
Flag of France image

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
OK, what is the output you're getting and what would you like it to be?
Avatar of bibi92
bibi92
Flag of France image

ASKER

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

ASKER

and the result is empty. Thanks regards
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?
Avatar of bibi92
bibi92
Flag of France image

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
Avatar of Jeremy Weisinger
Jeremy Weisinger

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of bibi92
bibi92
Flag of France image

ASKER

Thanks a lot regards
Powershell
Powershell

Windows PowerShell is a task automation and configuration management framework from Microsoft, consisting of a command-line shell and associated scripting language built on the .NET Framework. PowerShell provides full access to the Component Object Model (COM) and Windows Management Instrumentation (WMI), enabling administrators to perform administrative tasks on both local and remote Windows systems as well as WS-Management and Common Information Model (CIM) enabling management of remote Linux systems and network devices.

27K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo