THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Create Agent Jobs to run PowerShell Scripts

Actually, let's use PowerShell and SMO to create an Agent job which will run a PowerShell script. (Or is that a circular reference?)

I've created a number of PowerShell scripts which automate database management processes, and I wanted to be able to run them from an Agent job. The trick, of course, is to be able to get to the PowerShell environment from within Agent. It's not as hard as I thought. Let's say I have a script to back up my SQL Server user databases, and that script is in C:\Admin and it's called backupdb.ps1. The command from a Windows command window is this:

powershell "& c:\Admin\backupdb.ps1"

Try it - it's a lot easier than I'd thought. Once you have that little tidbit it's just a matter of setting up the job to execute that exact command line from within an Agent job. First, we need to load the SMO dll, and connect to the SQL Server where we want the job to run.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'

Next we instantiate an Agent Job object, set its properties, and create it.

$j = new-object ('Microsoft.SqlServer.Management.Smo.Agent.Job') ($s.JobServer, 'FullBackup')
$j.Description = 'Backup User Databases'
$j.Category = '[Uncategorized (Local)]'
$j.OwnerLoginName = 'sa'
$j.Create()

Once the job exists we can create the step to execute the PowerShell script. By default the step default SubSystem is TransactSQL, so we have to specify that we want the command shell, so we set the JobStep.SubSystem property to 'CmdExec' and the command property to the powershell command we looked at earlier.

$js = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($j, 'Step 01')
$js.SubSystem = 'CmdExec'
$js.Command = 'powershell "& C:\Admin\backupdb.ps1"'
$js.OnSuccessAction = 'QuitWithSuccess'
$js.OnFailAction = 'QuitWithFailure'
$js.Create()

Once the JobStep is created we need to alter the Job to set the target server and tell it what step should execute first.

$j.ApplyToTargetServer($s.Name)
$j.StartStepID = $jsid
$j.Alter()

The job needs to be scheduled so we create a JobSchedule object and set its properties. The time of day properties require a System.Timespan object, so we have to instantiate a couple of those, one for the start time and one for the end time, but that's pretty straightforward.

$jsch = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($j, 'Sched 01')
$jsch.FrequencyTypes = 'Daily'
$jsch.FrequencySubDayTypes = 'Once'
$startts = new-object System.Timespan(2, 0, 0)
$jsch.ActiveStartTimeOfDay = $startts
$endts = new-object System.Timespan(23, 59, 59)
$jsch.ActiveEndTimeOfDay = $endts
$jsch.FrequencyInterval = 1
$jsch.ActiveStartDate = get-date
$jsch.Create()

Once this is done the job will run every day at 2am and execute the backupdb.ps1 script we created for this purpose.

Allen

Published Wednesday, January 09, 2008 2:50 PM by AllenMWhite
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ola Hallengren said:

Wouldn't it be nice if there was a build in PowerShell job step type in SQL Server 2008?

(Just as with the VBScript.)

January 25, 2008 11:36 AM
 

david liu said:

Do you try to connect sybase and oracle ?

September 16, 2008 1:21 PM
 

srini said:

Hi,

Can I have backup powershell script for SQL database backup.

Thanks & regards,

Srini

April 20, 2009 8:35 AM
 

Mark said:

Seems like I'm coming to this blog in my search for the answer to an issue I have.  I have a powershell script that runs that copies the backup files to another SAN and I thought I put it as part of my sql server maintainence plan.  Running the script by itself as an administrator works.

Putting it in as a job to include in the maintenance plan failed.  I'm suspecting that it is because the script needs to be run as an administrator.  So how do I get the script to be run as an admistrator under the sql server agent login?

Thanks,

Mark

January 22, 2010 1:05 PM
 

Allen White said:

Lately I've been working on scripts to check and set security and configuration settings for SQL Server

May 19, 2011 4:26 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement