how to install SQL Server job using scripts?

Question

Hello everyone,

I have a SQL Server job in my SQL Server 2008 server. Now I want to write some scripts using PowerShell which could deploy the job into other computers automatically through command line. Any samples or documents to recommend for this topic to learn?

thanks in advance, George

Answer 1

This is probably a good starting point to understand how they are scripted. You essentially create a job with sp_add_job and can then add steps, schedules, etc with the related stored procedures. You might want to start of by scripting out your existing jobs from SQL Management Studio and comparing them to the documentation.

Answer 2

Simplest thing is to create a job manually, then right-click it in SSMS and do Script As Create.

Answer 3

If you want to deploy the same job to other SQL Servers, you may also want to consider the MSX TSX setup in SQL Server.

One server is a Master (MSX) that deploys jobs to all other servers and the Target servers (TSX) download and execute the job. The good part is that all the good things about SQL Server Agent (scheduling, the deployment, error logging, notification, triggering one from another, integration) etc is built in and you don't have to write it all custom for every job.