20 Creating and working with SQL Server Agent objects

 

In the previous chapter, you learned how to filter and retrieve information about a SQL Server Agent. The responsibility of DBAs for a SQL Server Agent includes the creation and alteration of SQL Server Agent objects. This chapter will provide you with all of the tools to do this easily.

Now that you are learning how useful and powerful dbatools is, you will also want to know how to schedule the running of dbatools scripts with SQL Server Agent jobs, and we will show you the most stable and robust method that we use to do this.

20.1 SQL Server Agent job creation

In the previous chapter, you used Find-DbaCommand to find all of the dbatools commands that interact with the Agent. Let’s search for all of the commands that you can use to create or alter Agent objects. The PowerShell command Get-Command returns commands you can filter by a module such as dbatools with the -Module switch, by verb with the -Verb switch, and by noun with the -Noun switch. As you can see in the next listing, the verbs that we use to create, alter, and remove are New, Set, and Remove.

20.1.1 Creating categories

20.1.2 New schedule

20.1.3 New proxy

20.1.4 Create a new operator

20.1.5 Create a new Agent job

20.1.6 The job step

20.2 Bonus Agent job commands

20.2.1 Start-DbaAgentJob

20.2.2 Get-DbaRunningJob

20.2.3 Get-DbaAgentJobHistory

20.3 Hands-on lab