PowerShell allows you to interact fairly easily with SQL Agent jobs. If you wanted to have one job that could fire off the other 20 you can create a SQL Agent job and set it to fire off a PowerShell script similar to below. This would just fire off every job that started with "Test".
If you have other jobs that you don't want to start just filter out the results of the
Once you get the script doing what you need just create a SQL Agent job to fire it off. If you want it to happen anytime the SQL Agent service is restarted, configure the service to run PowerShell.exe and have it call the script you create. You can do this under the Services applet and go to the "Recovery" tab of the SQL Server Agent properties window.
I would make sure you document how you set this up so anyone else on your team or that you work with is aware of how it is configured.
answered Oct 12, 2011 at 11:21 AM