There are many ways to schedule a PowerShell script to run within windows. The most common way is to use task scheduler however if your PowerShell script is invoking SQL commands, I find it is best to use the SQL server agent to schedule the job. This keeps all of your database based scripts in one place. This is very important when it comes time to upgrade or migrate the server. I will go over how to schedule the script in SQL Server 2016 as well as add email notifications and output to a text file.
Prepare Your Script
So you've created your PowerShell script including any necessary credentials or API keys in a safe and secure way. Now you will need to copy the .ps1 file to the SQL server.
- If it doesn't already exist, create a "scripts" folder on the C drive.
- Copy the .ps1 into the C:\scripts folder
Configure Email on SQL Server
Before creating your SQL job, you should verify email is set up properly on your SQL Server. To use email on SQL Server, you need an email profile and within that profile you need an email account. First create a default profile and then create an account on that profile.
- Open up SQL Server Management Studio and connect to your database instance
- Expand Management and right click on Database Mail
- Click Configure Database Mail
- If you don't already have a profile and/or account, Select the first Set up Database Mail by performing the following tasks option and click next
- Go through this wizard to create the needed items. You will need to configure all the relevant SMTP settings.
- After adding the profile and SMTP account, Right click on Database Mail and select Send Test Email
- Verify email is working correctly.
- Expand SQL Server Agent and then Operators
- If you don't have any operators listed, Right click and select New Operator
- Fill in the name and email name of the operator and click OK. You can disregard the pager section as we will not be configuring that as part of the job.
Configure the SQL Job
- Right click on Jobs and select New Job
- Give your job a name and leave the owner as the default. For the description, It's a good idea to add something similar to the image above
- In the left hand navigation pane, select Steps. Here you could create multiple steps with scripts that run only if a previous script completed successfully. For our purposes though, we will just create one step. Select New
- On the Type dropdown, select Operating System (CmdExec). In the command box, input the following command...
powershell.exe -file c:\scripts\example.ps1
- Great! Your job should look like the image above. Now select the Advanced tab and change On success action to Quit the Job reporting success. Enter c:\scripts\example.txt in the Output field.
- If you want to see an output for each and every time the job runs, Select the checkbox for Append output to existing file and click OK
- Go to the Schedules tab and select New and create a new schedule
- Go to the Notifications tab and check the email box and select the Operator you created in the previous step. Leave the second box as When the job fails. This way you will be notified via email if the job fails.
- Click OK and your job will be created! It should show up in the SQL Server Agent > Jobs pane. Right click it and select Start job at Step to run the job and verify its working correctly.
Conclusion
First we copied our PowerShell script over to the SQL server. If we had any credentials or API key within the script, we utilized this guide to add them to the script. Next we created a database mail profile and account for the SQL Server and verified its working correctly. Lastly, we created the SQL job that will run the PowerShell script and enabled email notifications and text output to a file. Running PowerShell scripts through SQL Server Agent is a great way to automate many tasks. One of the most useful tasks is using the SQL Server PowerShell module to import or export data to and from SQL and any REST api. Stay tuned for a guide on how to do that.