Introduction

PowerShell is a task-based command-line shell and scripting language; it is designed specifically for system administrators and power-users, to rapidly automate the administration of multiple operating systems (Linux, OSX, and Windows) and the processes related to the applications that run on those operating systems. For system administrators (DBAs\Wintel Admins), creating powershell scripts is a day to day activity that helps them automate their tasks.

This article will give you an insight to understand how you can schedule a powershell script using Microsoft SQL Agent job. A couple of scenarios where you will find this useful are:

• When you are a person who is more comfortable in using SQL Agent jobs compared to other scheduling tools.
• When you need to make use of alerts and notifications facility provided by the SQL Agent jobs.
• When you use proxy accounts instead of AD level accounts to run the job.

Prerequisites

As we are using SQL Agent, you need a SQL Server Edition supporting SQL Agent jobs. All Editions of SQL Server other than the Express edition have the luxury of SQL Agent. I have tested this script in SQL Server 2008 and above. You also need PowerShell, any version from V1.0 to latest. This comes with all Windows versions.

The high level steps are to:

1. Create a powershell script ‘checklastreboothistory.ps1’ which will check the last reboot time of the server and saved to a location in C drive.
2. Schedule the powershell script using sql agent job to run on daily basis.

Let's look at the details of each of these steps.

Create the PowerShell Script

Any powershell script can be scheduled using SQL Agent jobs.

I have written a powershell script called ‘checklastreboothistory.ps1’ for the demo. This script will pull information about the last reboot of the server and log this in a text file. The file is called reboot log and stored in the C:\ps\RebootHistory location. This is done using the WMI classes to pull the reboot information. Please refer here to read more on WMI : https://technet.microsoft.com/en-us/library/cc181125.aspx.

The script contains:

$data = Get-WmiObject -class win32_OperatingSystem -computername localhost
$uptime = $data.ConvertToDateTime($data.LastBootUpTime)
$uptime | Out-File C:\ps\RebootHistory\rebootlog.txt -Append

Scheduling PowerShell in SQL Agent

The next step is to schedule this powershell script in a SQL Agent job. To do this, let's start by expanding the SQL Server Agent node and the right cliking on Job. Select "New Job". This will take you to the New Job window where you can specify the job level details.  

Next we need to update the job details by filling the fields. Here are the items to fill in, which you can see in the image below:

• Give a Job Name which will explain the intention of the job (even if it is not mandatory)
• Give job owner which is preferably sa. If required you can change to any AD account as well.
• Category (Not a mandatory Option). You can choose any option from the dropdown.
• Description- A little wording on what the job does for others to understand. (Not Mandatory Option )

Once you click the Steps item in the left pane, you will see the screen below. Here is where we see an option to create a new step. 

After clicking on the New button , Job Properties page will come up and the below details need to be filled in this page. Update the step details as follows.

• Step name   : As per your Wish.
• Type            : Operating System (CmdExec).
• Run as         : SQL Server Agent Service Account Or you can use a proxy having access to the servers you want to check the details.  
• Command    : Powershell –file “C:\ps\checklastreboothistory.ps1”

Note : In the Type field, we are using the CmdExec type as we are calling the Powershell executable.

Click OK to save this job step.

Next click on the Schedules item in the left pane. This will give you a list of schedules. Click New to create a job schedule as follows. You can choose to change the schedule options based on your requirements.

After filling in your schedule, click OK to save it. Then click OK to create the job. You should see this in the list of jobs for your SQL Server Agent.

Expand the SQL Agent Job Node in SQL Server and Right click on the job named ‘”Schedule Powershell Script’’ and click ‘Start job at Step..’, as shown below.

The job will start and you will see a screen like the one below.

Verification

After execution of the job, a text file, named Rebootlog.txt, has been created in the folder as noted in the PowerShell script. Again, this is a verification step based on my PowerShell script example. This is just included to show that the PowerShell script that we scheduled is completed and the expected result is achieved.

If you check the folder, you will see this text file.

Open it and see the date of the last reboot. This is what my system showed:

Conclusion

This article explained how to schedule a powershell script using the SQL Agent job which will help the administrators (particularly SQL Server admins to schedule their daily tasks) .This was tested in SQL Server 2008 and above having powershell Version 1.0 and above . Please feel free share your comments on this.