SQL Server already automates many administrative tasks, such as updating index statistics, but you can automate other regular tasks, such as backups and data import and export, by defining jobs to run at certain times or based on certain conditions. The SQL Server 7.0 Administration (70-028) online exam guide includes defining jobs in its list of automated administrative topics, along with defining alerts and operators, which I discussed in the May 2000 issue. In this article, I show you how to automate tasks based on a time schedule, but you can also activate a job or sequence of jobs based on an alert condition.
New in SQL Server 7.0
SQL Server 7.0 lets you set up not just a single job but a series of jobs with dependencies. For example, you can configure a job to start on successful completion of another job and configure a third job that takes corrective action if the first job fails. (For an overview of SQL Server 7.0 jobs, see Victoria Launders, "Using SQL Server 7.0 Jobs," http://www.sqlmag.com/articles, Article ID 5244.)
You can apply this functionality to automate regular database maintenance jobs, such as backing up system and user databases. In pre-SQL Server 7.0 releases, you have to start the Master database backup at, say, 1:00 a.m. Then, assuming that backup completes, you start the msdb database backup at 1:15 a.m. At 1:30 a.m., you might be ready to start backing up the user databases and transaction logs at timed intervals based on when the previous backup completes. Although you could write a script to schedule these backups, the point of a graphical interface such as Enterprise Manager is to avoid having to write complex scripts. With SQL Server 7.0, you can easily set up an automated sequence of backup jobs scheduled so that when the first job completes, the second one starts, and so on.
You can automate almost any task, T-SQL command, or OS command. And as of SQL Server 7.0, you can use active scripts in Visual Basic (VB), JavaScript, or other programming languages to automate any task. You can set up backups, data import and export tasks, table updates, and database maintenance tasks all at one time and schedule them to run when needed.
Although you can also automate replication tasks, I recommend you leave these more complicated options until you're comfortable with replication and have a pressing business need. You can manage most replication management tasks through Enterprise Manager's replication tools without having to manipulate the tasks directly.
Automating a Backup Sequence
Let's walk through the process of automating a series of jobs to back up the Master, msdb, Model, Pubs, and Northwind databases. To define the backup jobs and their schedules, open SQL Server's Enterprise Manager, expand Management, SQL Server Agent, then select Jobs.
You back up a database by using a SQL command that specifies how to run the backup. You can write this command from scratch, but I cheated a little by setting up the first backup (of the Master database) through the Enterprise Manager's Backup/Restore interface. Setting up this first backup generated the job's SQL syntax, which I then copied to the Windows NT clipboard.
To duplicate this copy process, use the Backup dialog box's Schedule options to set the start time for the Master database backup, then close the Backup dialog box. Open the Jobs window, double-click the name of the job you just created, select the Steps tab, then click Edit. The resulting Command window contains the SQL syntax for the backup job, which you can copy and paste into the other jobs you're defining.
Note that before defining the backup jobs, you must create the backup devices to which you direct the backups for each database. To create these devices, right-click Backup in the Enterprise Manager, then select the New Backup Device option. SQL Server can back up to a local tape drive, but I chose to back up to a file on disk for this example. To create each backup device, simply type the name of the device and the file location. To keep everything neatly in one file, you can create just one backup device for backing up all system databases, but if you do so, one backup failure would corrupt the entire file. I recommend using more than one device.
After you've created the backup devices, you're ready to continue creating a backup job for the Master database. Right-click Backup in the Enterprise Manager hierarchy, and select Backup a Database to open the SQL Server Backup dialog box. Leave the database set to Master, click Add and add the name of the backup device you just created, then click OK. For this backup example, select the Overwrite existing media option.
To set the job's begin and end times, click Schedule. Notice that the default is to back up once a week on Sunday. To change this setting, open the Edit schedule dialog box by clicking the ellipsis (...) to the right of the Schedule window. This dialog box shows the job set to run at midnight, but consider changing the time because midnight is a popular time for unattended tasks. After you've set the job's start and end times, exit this series of dialog boxes by clicking OK twice.
To configure the job's properties, switch back to the Jobs item and double-click the job you just created. In the resulting Properties dialog box, select the Steps tab, which shows only Step 1 defined because you've set up only one backup job. With just one backup job defined, both the On success and On failure options are set to quit, meaning the job will terminate after this backup completes.
To establish a sequence of jobs, you must add the other jobs, then set up the job flow so that when the first job completes successfully, the second one starts automatically. You can't easily copy an entire job step, then modify it. Nor can you import existing jobs you might have into the Jobs tool and make them part of the sequence. To set up the other jobs, you have to start from scratch—except that you can copy the SQL syntax from a previous backup job. From the Jobs window, click Edit to open the Edit Job Step dialog box for Step 1. Change the step name to Backup Master Database, as Screen 1 shows, highlight the text in the Command window, then you can copy the text to the clipboard. (Note that this command originally appeared all on one line, but I modified the command to use several lines so that you can more easily see and edit it.) Now, exit the Edit Job Step dialog box by clicking OK, then click New to add a second step. Name this second step Backup MSDB, and keep the job type as TSQL.
You can change the database name to msdb, but you don't have to because you specify in the T-SQL command the name of the database you want to back up. Just paste the T-SQL command into the Command window, then change the database name to msdb and the backup device name to the one you defined for msdb. If you're using one backup device for all system databases, leave the device name the same but make sure you change INIT to NOINIT. INIT tells SQL Server to initialize the backup device, thereby rewinding the tape or clearing out the file so that the second backup overwrites the first; NOINIT tells SQL Server you want to add the next backup after the preceding backup on the same device.
Controlling Job Flow
Now that you've added another job, when you open the Advanced tab in the Edit Job Step dialog box, you'll notice that the On success action is now Goto step. But you haven't set up the next step yet; adding the second step doesn't automatically set Step 1, on successful completion, to activate Step 2. If you close the Edit Job Step dialog box at this point, then close the job Properties dialog box, you'll receive a message notifying you about job-flow problems. You can either fix the problem or let SQL Server fix it for you, as the pop-up message offers to do. 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.
In response to the pop-up message, click No to return to the Job Properties dialog box. To modify Step 1 to point to Step 2, open the Edit Job Step dialog box, double-click Step 1, then select the Advanced tab. Drop down the list of options for On Success and note that Step 2 is now listed to start when Step 1 successfully completes. Click OK to close the dialog box.
You can now repeat the copy-and-paste process to add backup jobs for the Model, Pubs, and Northwind databases. Screen 2 shows the resulting list of jobs (or steps) in the sequence. If you want to automate backup jobs for only the Master and msdb databases, set Step 2 to Quit with success.
To set up operator notification of a successful or failed job, open the Properties dialog box, click the Notifications tab, and select the Net send operator box. Configure the option so that the operator receives notification when the job succeeds, as Screen 3 shows. Notice that you can choose to Write to Windows NT application event log to track job success and failure. You can also automatically delete a job after it completes successfully, which could be useful for eliminating one-time tasks from the job list. However, if you delete the job, you won't be able to check its history. Close the Job Properties dialog box.
For multistep jobs, SQL Server shows you a list of steps, letting you start at a step other than Step 1. To start at another job step, just right-click the step name in the Enterprise Manager Jobs window, then select Start Job. When the job step completes, right-click the step name again, then select View Job History. To see the steps in the order they ran, select the box in the upper right corner. After you verify that all the steps completed successfully, close the View Job History window.
On Alert
SQL Server lets you easily set up varying schedules for a job step. Let's say you want to back up the transaction logs every 2 hours from 8:00 a.m. to 6:00 p.m. and every 4 hours from 8:00 p.m. to 8:00 a.m. Simply set up one schedule to start at 8:00 a.m. and back up every 2 hours, with the last backup at 6:00 p.m., then set up another schedule starting at 8:00 p.m. and ending at 4:00 a.m. You could even add a third schedule for weekends. To avoid two instances of the job running at the same time, you should end overnight backups at 4:00 a.m. instead of at 8:00 a.m. If the second job instance tries to start when the first is already running, the second instance will fail.
Besides defining jobs to run on a schedule, you can define jobs to run in response to an alert condition. You can use the Alerts interface to define an alert as I discussed in the May 2000 issue, setting the alert to execute the appropriate job. Or you can use the Jobs interface to define a job and, on the Schedules tab, set up a New Alert instead of a schedule.
This backup job example demonstrates some possibilities for automating a sequence of jobs, and you no doubt have many other situations where you can use these SQL Server 7.0 features. Test your knowledge of job definition tasks by taking the online quiz "Practice Questions: Defining Jobs," available at http://www .sqlmag.com/articles, Article ID 8582. And for more examples of how to automate tasks with SQL jobs, see the online lab files that accompany this article. After you've automated all your administrative tasks, you should have ample time to read SQL Server Magazine cover to cover and study for the MCDBA exams.