Welcome to Day 24 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.
One of the best practices that you should observe is to run regular integrity checks on our databases. This is best performed by running as a job, so let’s make a job that runs every Saturday at 3am to run DBCC CHECKDB on all of our databases.
Creating Jobs, Job Steps and Job Schedules
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
#Assign variables $Instance = "localhost\SQL2012" #Assign the SMO class to a variable $SMO = "Microsoft.SqlServer.Management.Smo" # get the server $Server = New-Object ("$SMO.Server") "$Instance" $Job = $Server.JobServer.Jobs["Database Consistency Checks"] IF (!($Job)) { # Create the job $Job = New-Object ("$SMO.Agent.Job") ($Server.JobServer, 'Database Consistency Checks') $Job.OwnerLoginName = 'sa' $Job.Create() } $JobStep = $Job.JobSteps["Run DBCC on all databases"] IF (!($JobStep)) { # Create the job step to run DBCC CHECKDB for all databases $JobStep = New-Object ("$SMO.Agent.JobStep") ($Job, "Run DBCC on all databases") $JobStep.Command = "EXECUTE sp_msForEachDB 'DBCC CHECKDB([?])'" $JobStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithSuccess $JobStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithFailure $JobStep.Create() } $JobSchedule = $Job.JobSchedules["Saturday's at 3am"] IF (!($JobSchedule)) { # Create the job schedule to run on Saturdays at 3am $JobSchedule = New-Object ("$SMO.Agent.JobSchedule") ($Job, "Saturday's at 3am") $JobSchedule.FrequencyTypes = [Microsoft.SqlServer.Management.Smo.Agent.FrequencyTypes]::Weekly $JobSchedule.FrequencyInterval = [Microsoft.SqlServer.Management.Smo.Agent.WeekDays]::Saturday $JobSchedule.FrequencyRecurrenceFactor = 1 # Run every 1 week $TimeSpanStart = New-Object -TypeName TimeSpan -ArgumentList 3,0,0 $JobSchedule.ActiveStartTimeOfDay = $TimeSpanStart $JobSchedule.Create() } |
Yesterday we created operators for the alerts. If you want an operator for the job, you go about creating one in the same manner as we did yesterday, and in the job class set the appropriate operator properties prior to creating the job.
