out-file in powershell doesnt work in a sql server agent job

Question

Hi,

Im trying to establish some logging in an sql server agent job running some powershell (powershell and dbatools.io code but I keep failing, repeatably :( 

I'm running this code in explorer Ise and it work fine, no problems

 Try {

    Restore-DbaDatabase -SqlInstance MIPYDB5 -Path '\172.16.64.200\clustsql01-backup\CLUSTSQL01$AG01\AspNetServices\' -DestinationDataDirectory I:\MSSQL15.MSSQLSERVER\MSSQL\DR-DATA -DestinationLogDirectory J:\MSSQL15.MSSQLSERVER\MSSQL\DR-Log -DirectoryRecurse -RestoredDatabaseNamePrefix DR_ -DestinationFilePrefix DR_ -norecovery -withreplace -ErrorAction Stop -WarningAction stop -verbose | Out-File -FilePath "I:\MSSQL15.MSSQLSERVER\MSSQL\Log\DR_AspNetServices_$((Get-Date).ToString('yyyyMMdd_hhmmss')).txt"

    [System.Environment]::Exit(0)

} catch {

    [System.Environment]::Exit(1)

}

 When I run in in SQL Server agent as a subsystem = powershell job under a proxy account it fails. If I remove the out-file part of the job and run the code in sql server agent it works fine, so the problem is the out-file part...

Anyone, any ideas???

Regards

Michael

Answer 1

typo, I meant I run it in powershell ISE not explorer Ise

Answer 2

Without an error message to analyze, the obvious answer would be that this proxy account must not have access to the I:\MSSQL15.MSSQLSERVER\MSSQL\Log\ folder. Or the folder does not exist.

You have a Try/Catch but you don't do anything with the error message, so you don't know what went wrong. Write the $_.Exception.Message to a file on the SQL server.

Or send yourself an email as this page shows.

https://www.vexasoft.com/blogs/powershell/7255220-powershell-tutorial-try-catch-finally-and-error-handling-in-powershell