Lab Files: Defining Jobs

This set of lab exercises supplements the Certifiably SQL "Defining Jobs" column in the June 2000 issue of SQL Server Magazine. These lab exercises show you how to automate regular tasks, such as backups and data importing and exporting, by defining jobs to run at certain times or based on certain conditions.

Related: Defining Jobs

Task Administration

Open the SQL Server Enterprise Manager. Expand Management, SQL Server Agent to see Alerts, Jobs, and Operators. You'll be working mostly with the Jobs item in this lab.

Automate a Backup Sequence

In this exercise, you're going to automate a sequence of backup jobs for the Master, msdb, Model, Pubs, and Northwind system databases. The first step is to create backup devices for each database. Under Management, right-click Backup, then select the option for New Backup Device. Supply the device name and the backup file location for each system database.

Create a backup job for the Master database by following these steps:

Modify the Job to Add Multiple Steps

Now, switch to the Jobs item under Management in the Enterprise Manager hierarchy, and open the Properties dialog box by double-clicking the job you just created. You may have to refresh the Agent to see the new job listed in the Jobs window.

Click the Steps tab, and notice that only Step 1 is defined; at this point, you have only the one backup job for the Master database set up. Click the Edit button to show the Edit Job Step dialog box for Step 1. While you're here, change the step name to "Backup Master database."

You can now highlight the text in the Command window, and copy it to the clipboard. Click OK to exit the Edit Job Step dialog box.

You're now ready to add a second step. Click New, then name this step "Backup MSDB database." Leave the job type as TSQL. Now paste the TSQL command into the command box. Change the name of the database in this command to msdb, then change the name of the backup device to the one you defined for msdb.

Controlling Job Flow

When you switch to the Advanced tab, notice that the "On success" action for Step 2 is "Goto step," but there's no next step to go to. If you click OK to close the Edit Job Step dialog box and click OK again to close the Job Properties dialog box, you'll see a message telling you there's a problem with the job flow and asking whether you want SQL Server to fix the problem. Although SQL Server can correct the problem in Step 2 by changing the "On success" action from "Goto step" to "Quit with success," you still have to set up the "Goto step" for Step 1.

Click No to return to the Job Properties dialog box. Double-click Step 1 to open the Edit Job Step dialog box, then select the Advanced tab.

Drop down the list of choices for the "On success" action, and note that Step 2 is now in the list. Select Step 2 from this list as the "Goto step" that will start as soon as Step 1 completes successfully. Click OK to close this dialog box.

You can now repeat the process, adding a backup job for the Model, Pubs, and Northwind databases.

Set Up an Operator Notification

In the Job Properties dialog box, click the Notifications tab. Select the check box to notify an operator (perhaps the operator you configured if you went through last month's lab exercises to set up alerts and operators). Change the option so that, for this test, the operator receives notification whether the job succeeds or fails.

Close the dialog boxes, and return to the Enterprise Manager.

Testing the Job Flow

Right-click on the name of the job you want to run, then select Start Now. Click Start to begin the job at Step 1.

Alternatively, you can wait for the job to start at the time you scheduled earlier and watch the Last Run Status to see whether it runs.

Check the Job History

When the job completes, right-click the job name again, and select View Job History. Select the box in the upper right corner of the Job History window to expand the step details, and you'll see the job steps in the order they ran.

Verify that all the steps completed successfully, then close the Job History window.

Configure Job History Settings

Right-click the SQL Server Agent icon in Enterprise Manager, then select Properties. Select the Job System tab, and verify that you've selected the "Limit size of job history log" check box. You can also change the maximum size of the job history file or change the maximum number of rows per job. Close the dialog box.

Job Scripting

SQL Server creates scripts to help you keep track of the actions you've taken. You can use these scripts to create an identical job on another server. Just right-click the job you just created, then select All Tasks, Script Job. Specify a file name for this job script, and click OK to generate the script. You can open a Query Analyzer window and read in the script.

If you want, delete the job, then run the script to recreate it. Note that you may see errors about a "non-existent step referenced by @on_success_step_id." Don't worry about such error messages. The script builds the job from the top down, so Step 1 will produce an error because you've set it up to go to Step 2, which the script hasn't built yet.

Cleanup

Finally, delete the job and the backup devices you created for this exercise.