powersell script execute from sql server agent job failed

Question

Hi I created power shell script and unable to execute from the SQL server agen job. But through commnad promt powershell prompt etc. only diffrentl is I manually did uisng my local user. but sql server agent run with diffrent user. I already given permission on C:\app\DBA.

Version: 2016 SP2 enterpirse
+++++++++++++++++++++++++++++++++++++

Answer 1

Hi @Ashwan

The system can't find the file specified.

1.The error messages tell us, the path or the file name is not correct. Make sure the path in your command is one absolute path and the filename is correct.
2.Change Type from Operating system(CmdExec) to PowerShell in your job.

Firstly, make sure the Domain\Account has the permission to access the file.--From your description, you have already done this;
Then make sure the file path and name is correct, for example test on my own side, if I named the file as 1.txt, use the command as this copy C:\powershelltest\1.txt C:\powershelltest\11.txt, it will not be copied,the error message shows can not find the file specified, because the real name of the file is 1.txt.txt, then I change the file as 1, then the command is executed correctly.

Steps as next:

--Step 1: create one txt file and then save it as one batch file
Note: Write the absolute path here, because there is no way to enter the current path directly after SQL Server

--Step2: create the job,Note: choose the Type: PowerShell

--Step3, verify whether there is one 11 txt file.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @Ashwan ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi Mia Many thanks for the reply . Yes looks job is complete. but following "Restore-SqlDatabase "commnad didnt executed . I can confirmed "Get-Process" executed. bit strange wht didnt executed "restore" command? and no error on that. But when I run the script manually through the powershell , then it is executed successfully .

Get-Process | Out-File -FilePath C:\app\DBA\1.txt
Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile $FileToRestore -RelocateFile @($RelocateData,$RelocateData1,$RelocateData2,$RelocateData3,$RelocateData4,$RelocateLog) -ReplaceDatabase -ConnectionTimeout 0
Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile $FileToRestore -RelocateFile @($RelocateData,$RelocateData1,$RelocateData2,$RelocateData3,$RelocateData4,$RelocateLog) -ReplaceDatabase