I have a SQL Server job which runs some Powershell code, but although when running the Powershell script in a PS editor on the machine it runs successfully, when running the SQL Agent Job with the same code it fails.

And the reason it fails is because it doesn't manage to add a few snap-ins, like the one below or include some assemblies.

Add-PSSnapin SqlServerCmdletSnapin100

This leads me to believe that SQL Server has a internal version of Powershell installed with which it runs the Powershell jobs. And that specific internal version of Powershell is older and does not have the required assemblies / snap-ins and thus fails.

Now, is there any way I can use the Powershell version installed on my machine and not the one that's built into SQL Server?

I've looked over "Proxies" in SQL Server but I don't think they're what I'm looking for.

Any help is appreciated!

  • 4
    You are correct, SQL uses an internal version of powershell. You will need to use a command line job step and run the powershell.exe and point to the .ps1 file you want to execute. Make sure to set your execution policy correctly. Aug 30, 2016 at 14:14
  • Why not use the scheduler in Windows instead of using SQL Server Agent to do operating system stuff? Aug 30, 2016 at 14:51
  • @AaronBertrand I'd prefer to and also suggested to use the Windows scheduler, but it's a... "requirement", which is sometimes harder to fight than magically building a solution. Aug 30, 2016 at 15:17
  • 2
    @RaduGheorghiu, You want to use a SQL Agent job, but specify a cmdexec job step. Then the command is going to be "Powershell -noprofile -executionpolicy bypass -file "C:\scripts\script.ps1". Where you have saved the powershell script you want to run in "C:\scripts\script.ps1". msdn.microsoft.com/en-us/library/ms190264.aspx Aug 30, 2016 at 15:29
  • 2
    @JonathanFite, consider posting your cmdexec solution as an answer along with a job create script example.
    – Dan Guzman
    Aug 31, 2016 at 10:36

1 Answer 1


As requested, here is an example of the process. The step should look like this, though

Job Step Command Set the step to be a cmdexec job and then run powershell through that. It lets you use the latest version of powershell as well as configure the powershell environment for that user (if you want). The step command will look like the one below, complete with arguments for the example. Remember to put quotes around strings that contain spaces.

Powershell.exe -noprofile -executionpolicy bypass -file "C:\Scripts\DailyRestoreScript.ps1" -Arguments -SourceSQLInstance 'localhost\jdf2016' -SourceDatabaseName 'AdventureWorks' -TargetSQLInstance 'localhost\jdf2016' -TargetDatabaseName 'AdventureWorks2'

Complete Job

USE [msdb]

/****** Object:  Job [DailyRestore_ADWorks]    Script Date: 2/16/2017 9:00:51 AM ******/
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2/16/2017 9:00:51 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DailyRestore_ADWorks', 
        @description=N'Daily restore AD Works from backup', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name='XXXXX\JFite', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Restore ADWorks]    Script Date: 2/16/2017 9:00:51 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore ADWorks', 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'Powershell.exe -noprofile -executionpolicy bypass -file "C:\Scripts\DailyRestoreScript.ps1" -Arguments -SourceSQLInstance ''localhost\jdf2016'' -SourceDatabaseName ''AdventureWorks'' -TargetSQLInstance ''localhost\jdf2016'' -TargetDatabaseName ''AdventureWorks2''', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave

  • Wow, this is way more than I expected! Excelent! I was using the interface from SSMS, but this is much better. Thank you once again! Feb 16, 2017 at 15:05
  • and thanks for the prompting to finally make this a real answer. Feb 16, 2017 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.