Checking Backups with the Enterprise Manager

Prerequisites

The SQL Server Agent is responsible for scheduling and executing jobs and provides information about all scheduled jobs and their current status.

If a backup is not scheduled to run at a specific time in the Enterprise Manager, it is not registered as a job that is executed by the SQL Server Agent. For this reason it is not visible in the job overview or job history. In this case, you will only be able to find information in the SQL Server Error Log.

See also:

Viewing the Error Log with the Enterprise Manager

Procedure

  1. In the Enterprise Manager expand the Server and then Management and SQL Server Agent.
  2. Select Jobs.

    The right-hand pane lists all the jobs that have been scheduled and displays detailed information such as the start date, current status and status of the last run. Jobs that have failed are marked with a red icon at the beginning of the line. To view further information, double-click on the job to access a number of tabs that show you how the job was originally defined.

  3. To view the history of a particular backup job, select the job and choose Action ® View Job History button.

    You are now shown the times when the job ran and whether or not it was successful.

  4. To display messages that were generated during the backup, select the option Show step details. Then select one of the steps that is displayed.

    The messages generated by the SQL Server Agent for the selected step are displayed.

  5. When you have looked at all available messages, also look at the SQL Server Error Log. This may contain additional information you need.

See also:

Viewing the Error Log with the Enterprise Manager

You should never clear the job history manually as this permanently deletes the information. By default, a maximum of 100 execution histories is kept for one SQL Server job and a maximum of 1000 histories for all jobs together. If you want to increase these values, you can do so by modifying the SQL Server Agent Properties dialog box. To access the box, select the SQL Server Agent and then choose Action ® Properties. On the Job System tab make sure that the setting for Maximum job history log size is always set to at least 10 times that of Maximum job history rows per job. As the history entries are stored in the msdb database, the size of msdb must be adjusted accordingly.