Running Powershell as an SQL Server Agent task

In my prior post about Using Powershell to automate SQL Server database restore, I mentioned a follow-up post on setting this up.

It actually worked fine, until I  upgraded SQL Server 2008 R2 to SQL 2012. Then it broke. More on that later…

In essence there are two methods for running PS scripts as an SQL Agent task.

Method 1

From SQL Agent, Create a new Job, give it a name

image

Click Steps, then New, for the New Job Step. Name the step, choose Powershell, then Run as (choose an account that has proper permissions, then Command Open… chose the Powershell script, then OK. The contents will now be posted in the command window.

image

Click OK. Then click Schedules page to schedule the operation.

Method 2

After the update to SQL 2012, the restore script posted earlier stopped working. The errors indicated that it could not read the path to the UNC. That meant one of two things; permissions or the wrong default PS was being invoked, as SQLPS is also installed on this server.

Turns out, SQLPS was now being launched by default. To correct, save the PS script as a local file. Then create the SQL Agent task with a full path to Powershell.exe. Path may vary depending on install path. Add the path to the script as the argument. Problem solved.

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe D:\Scripts\PowerShell\RestoreCENv1.ps1

image

Advertisements
This entry was posted in PowerShell, SQL, SQL Server. Bookmark the permalink.

2 Responses to Running Powershell as an SQL Server Agent task

  1. Just me says:

    Thank you, that helped. Got error “myfile.ps1] cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help abou t_signing” for more details.”

    This script fixed that:
    powershell -ExecutionPolicy ByPass -File myfile.ps1

  2. Pingback: Top 5 blog posts for 2016 | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s