ddragman
ddragman

Reputation: 21

Powershell script behaves differently when ran in SQL Server Agent job

OS: Windows Server 2019 Standard
SQL: Microsoft SQL Server 2017 Standard CU28
PS version: 5.1.17763.2268

I've made a simple script to check if share is accessible before doing restore of databases. If the share is not accessible, it loops until the share becomes accessible. Script is the first step in SQL Agent job and is set as PowerShell type.

while(!(Test-Path \\192.168.1.92\Share\)) 
    {
        Write-EventLog -LogName "Application" -Source "DB_Refresh" -EventID 1001 -EntryType Information -Message "Backup share is not accessible!" -Category 1
        sleep 30
    }
    Write-EventLog -LogName "Application" -Source "DB_Refresh" -EventID 1001 -EntryType Information -Message "Backup share is accessible!" -Category 1

When I ran the script in PS ISE it works as it should. When I ran it in SQL Agent job step, negation works just the opposite as it should. When share is accessible it will loop and when it is not accessible it will not loop.

Any thoughts why does it behave like that? I have tried with -Not instead exclamation mark, but it behaves same.

Upvotes: 1

Views: 541

Answers (1)

AaronJPA
AaronJPA

Reputation: 1

Try replacing:

while(!(Test-Path \\192.168.1.92\Share\))

With:

New-PSDrive -Name "P" -PSProvider FileSystem -Root "\\192.168.1.92\Share"
while(!(Test-Path P:\))

I'm not sure if it will work to test if a share is accessible, as you want, but this solution works for accessing files on network shares. For example:

While this doesn't work:

$Content = Get-Content -Path "\\192.168.1.92\Share\file.txt"

This works fine:

New-PSDrive -Name "P" -PSProvider FileSystem -Root "\\192.168.1.92\Share"
$Content = Get-Content -Path "P:\file.txt"

Upvotes: 0

Related Questions