none
PowerShell with special character (/) RRS feed

  • Question

  •    Group 

           I am generating a backup script of SQL Server jobs, via powershell but in the file part, gave some problems in using the replace command of powershell where checking that I can't replace the \ character, what would be the correct way to use replace for this character via powershell ?

         Where the file name looks like this: Backup folder (c:\bd_sqlserver\backup\bkp_job\)  Job Name (ProcessTest_ter\qua\qui.sql)

    # Nome da sua máquina
    $ServerNameList = "notenew\sql_2016"
    
    # Diretório para salvar os scripts
    $OutputFolder = "C:\bd_sqlserver\backup\bkp_job\"
    
    $DoesFolderExist = Test-Path $OutputFolder
    $null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
    
    $objSQLConnection = New-Object System.Data.SqlClient.SqlConnection
    
    foreach ($ServerName in $ServerNameList)
    {
    
        Try
        {
            $objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
            Write-Host "Tentando se conectar na instância do servidor $ServerName..." -NoNewline
            $objSQLConnection.Open() | Out-Null
            Write-Host "Conectado."
            $objSQLConnection.Close()
        }
        Catch
        {
            Write-Host -BackgroundColor Red -ForegroundColor White "Falha"
            $errText = $Error[0].ToString()
            if ($errText.Contains("network-related"))
                {Write-Host "Erro de conexão à instância. Por favor, verifique o nome do servidor digitado, porta ou firewall."}
    
            Write-Host $errText
            
            continue
    
        }
    
        $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
        
        # Arquivo único com todos os jobs
        # $srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file "$OutputFolder\jobs.sql"
    
        # Um arquivo por job
        $srv.JobServer.Jobs | foreach-object -process {out-file -filepath $("$OutputFolder\" + $($_.Name.replace('\\','_x1_').replace('[\]','_x2_').replace('[','_x3_').replace(']','_x4_').replace('.','_x5_') ) + ".sql") -inputobject $_.Script() }
    
    }
    
    

    Thursday, August 22, 2019 4:20 PM

All replies

  • At this line:
    $OutputFolder = "C:\bd_sqlserver\backup\bkp_job\"
    remove the trailing \. It's not needed, and powershell is interpreting it as an escape character, causing much of your script to be treated as a string instead of as a proper script.
    Thursday, August 22, 2019 4:53 PM
  • At this line:
    $OutputFolder = "C:\bd_sqlserver\backup\bkp_job\"
    remove the trailing \. It's not needed, and powershell is interpreting it as an escape character, causing much of your script to be treated as a string instead of as a proper script.

    No it isn't. PowerShell does not use "\" as an escape character.

    To create a path no matter what slashes exists use :Join-Path"

    $outfile = Join-Path $OutputFolder 'jobs.sql'

    This will always work correctly.

    Double slashes in file names are ignored by PS.  Ps treat them as a single backslash.

    Try this to see what I mean:

    dir c:\\\\\Windows\\\\\System32




    \_(ツ)_/

    Thursday, August 22, 2019 5:22 PM
    Moderator
  •  

    Adam C Brown / JRV / Group

        Firstly I apologize for my not so great experience in the powershell script issue, I understand your information now I just doubt the correct writing, especially in item 3, for involving several steps and if you have a more practical way to write the line thank you ?

    1)
    $OutputFolder = "C:\bd_sqlserver\backup\bkp_job\"
    $OutputFolder = "C:\bd_sqlserver\backup\bkp_job"
    
    2)
      # $srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file "$OutputFolder\jobs.sql"
     # $srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file = Join-Path $OutputFolder 'jobs.sql'
    
    3)
     $srv.JobServer.Jobs | foreach-object -process {out-file -filepath $("$OutputFolder\" + $(( $_.Name.replace('\\','').replace('[','{').replace(']','}') ) + ".sql") -inputobject $_.Script() }
     $srv.JobServer.Jobs | foreach-object -process {out-file = Join-Path $OutputFolder $($_.Name.replace('\\','_x1_').replace('\','_x2_').replace('[','_x3_').replace(']','_x4_').replace('.','_x5_')  $('.sql')) -inputobject $_.Script() }
    
    



    • Edited by neibala Thursday, August 22, 2019 7:41 PM Ajuste da dúvida.
    Thursday, August 22, 2019 6:51 PM
  • Hi,

    Thanks for your reply.

    As you said your job name like ProcessTest_ter\qua\qui.sql, if you only want to replace"\", I think "-replace "\\","_x_" " works. 

    For example:

    $OutputFolder = "C:\bd_sqlserver\backup\bkp_job\"
    $name="ProcessTest_ter\qua\qui"
    join-path $outputfolder $($name -replace "\\","_x_")

    Best regards,

    Lee


    Just do it.

    Friday, August 23, 2019 9:30 AM
    Moderator
  • LeeSeenLi   /  Adam C Brown / JRV / Group

       In the matter of switching from (-filepath) to (join-path), I believe that I have a problem with the syntax of the instruction, in this case I would like you to validate my routine based on your example and your knowledge, so I am submitting below all the script and the job name (Process_Test_ter/Wed/Thu) that has this situation, if you can simulate, you can understand which part of the command line I am wrong, based on your knowledge ?

    # Nome da sua máquina
    $ServerNameList = "NOTE55\SQL_2016"
    
    # Diretório para salvar os scripts
    $OutputFolder = "C:\bd_sqlserver\backup\bkp_job\bkp01\"
    
    $DoesFolderExist = Test-Path $OutputFolder
    $null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
    
    $objSQLConnection = New-Object System.Data.SqlClient.SqlConnection
    
    foreach ($ServerName in $ServerNameList)
    {
    
        Try
        {
            $objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
            Write-Host "Tentando se conectar na instância do servidor $ServerName..." -NoNewline
            $objSQLConnection.Open() | Out-Null
            Write-Host "Conectado."
            $objSQLConnection.Close()
        }
        Catch
        {
            Write-Host -BackgroundColor Red -ForegroundColor White "Falha"
            $errText = $Error[0].ToString()
            if ($errText.Contains("network-related"))
                {Write-Host "Erro de conexão à instância. Por favor, verifique o nome do servidor digitado, porta ou firewall."}
    
            Write-Host $errText
            
            continue
    
        }
    
        $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
        
        # Arquivo único com todos os jobs
        # $srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file "$OutputFolder\jobs.sql"
    
        # Um arquivo por job
        $srv.JobServer.Jobs | foreach-object -process {out-file join-path $OutputFolder  $($_.Name.replace('\\','_x0_').replace('*','_x1_').replace('\','_x2_').replace('[','_x3_').replace(']','_x4_').replace('.','_x5_').replace('/','_x6_')  + '.sql') -inputobject $_.Script() }
    
    }
    
    

    • Edited by neibala Friday, August 23, 2019 5:29 PM error in typing
    Friday, August 23, 2019 5:08 PM