I will explain how to add another step to existing job and configure it correctly.
Let's assume that you already have a job with 1 step (for example as in Sethu's backup job example)
1. Open SSMS (SQL Server Management Studio, make sure that SQL Agent is running) navigate to Jobs folder under SQL Server Agent and select desired job. In my Exmple it is Demo DB Backup Job, right click on properties to edit the job.
In opened dialog (see below image) select job steps (it will show your current active step) and click on New button to add another step.
2. In new step dailog do the following (see below)
3. Configure advance options of the step (see image below)
Note: The system doesn't control the size of the table storing the job steps output. It is a job owner responsibility to clean this table via sp_delete_jobsteplog stored procedure.
Note: The size of the History on the other hand is configurable, here how to do it in SSMS: Right click on the SQL Server Agent node then navigate to properties->History. By default Maximum job history log size (in rows) is 1000. Maximum job history rows per job is 100.
4. The next step will be to adjust the previous first step on success to execute the next step.
Note: The previous step will not be adjusted automatically to move to next step!
5. You are done! Now you can execute your new multi steps job. During the execution you will see the following difference: The system will recognize that your job is a multi steps job and will offer you the option to execute from specific step (meaning that you can start job execution from any step)
6. After execution, in the history page, you can see the results per each step:
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
when i automate this to happen on a specific time.It does not start at all do i have to give some parameters ??