question

MichaelPettersson-8793 avatar image
0 Votes"
MichaelPettersson-8793 asked IanXue-MSFT commented

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

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

sql-server-generalwindows-server-powershell
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The out-file part seems fine. Can you provide the error message?

0 Votes 0 ·
MichaelPettersson-8793 avatar image
0 Votes"
MichaelPettersson-8793 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MotoX80 avatar image
0 Votes"
MotoX80 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.