Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Using SQL Server Agent to Monitor Backups
Last updated Mar 28, 2003.
I've introduced the SQL Server Agent in a previous tutorial, so if you're new to the topic make sure you check out the article called Automating Tasks with SQL Server Agent in the Administration section of this guide. We'll start this tutorial with that information in mind.
The task we are going to automate is very simple, but along the way I'll use it to demonstrate some of the more important features of the SQL Server Agent. You may monitor your backups using other standard methods, or perhaps you have a dedicated tool that you use for backups, and it has its own feedback mechanism. Even so, this tutorial lays out an outline that you can use for almost any kind of task, not just monitoring.
In the introduction tutorial I mentioned earlier I show tools from both SQL Server 2000 and 2005 for the SQL Server Agent, but to keep from jumping around a great deal throughout this tutorial I'll use only SQL Server 2005, which uses the SQL Server Management Studio graphical tool. Everything I show you in this tutorial is possible in both versions. I'll also include commands at the end of each section, since you should be familiar with those as well.
Before you use a tool, you need to have a goal in mind. Not only do you need a goal, but you need to have an understanding of how a given tool will help you accomplish it. With a goal in mind, I normally break out computer automation tasks into five major steps:
- Determine the task steps and flow
- Understand the task security
- Determine the task repetition and schedule
- Determine the task reporting and alerting
- Implement and check the solution
Our goal in this case is simple: we want to check the backup dates of all of the databases on a given server. You can get this information, one database at a time, using the SQL Server Management Studio. Connect to a server and right-click the name of a database. Select Properties from the menu that appears, and you'll see the date the last backup was taken there in the top part of the screen, as shown in this screen:
You can see the same information in SQL Server 2000 using the Enterprise Manager tool.
Determine the task steps and flow
The first step is to understand the flow of the process. To do that, you normally have to follow some manual process that achieves the result you want. In this case, we used a graphical tool that shows the last backup date and time of a single database on a server.
But viewing this data one database at a time isn't really the easiest way to make sure all your databases are backed up. You could peruse the log files for the server, since backup operations are recorded there as well, but once again you have to look through more information than you need to find what you're after.
It turns out that the graphical tools in both SQL Server 2005 and 2000 are getting their information directly from the server engine. This information, and more, is found in the backupset table in the msdb database. Here's a query that shows the entire set of database backups performed on this server. It also shows the databases that haven't been backed up, which is what we're really after:
SELECT a.name , b.backup_finish_date FROM sys.databases a LEFT OUTER JOIN msdb.dbo.backupset b ON a.name = b.database_name ORDER BY backup_finish_date DESC, a.name GO
With this information in hand, we can now set about automating that query to show the backups on our server. We want to run a query and create a log of the results, or perhaps send an e-mail with the results of the query to ourselves.
There are only two steps in this process, but often you will want to automate far more steps than that. In any case, there is a flow to the tasks involved. That means that we have a series of steps that we want to follow, but the outcome of one step might determine what happens next. You're probably familiar with a simple flowchart, which has shapes that detail what happens when a step of the process succeeds or fails. While you may not need to break out a pencil and paper and draw the steps, you should at least have them in mind as you create your automation. With just these two steps, there isn't much to diagram, but by using some forethought, you can plan a fairly sophisticated task as you work to the goal. You need to think about this from the outset.
For the purpose of this example, there are two primary steps: run the query, and create either a "pull" notification (log file) or "push" notification (e-mail or pager) of the results.
Understand the task security
I'm normally an advocate of thinking about security from the very outset of a process, but in this case you can't evaluate the security of the entire process every time. Within each SQL Server Agent job there are steps, each of which might require different levels of access. For instance, one step might create a file, and another might connect to a database on another server. Each of these requires different security, and if you string together several jobs to automate a more complex process, each job might need to have a different level of access. It's important to think about that when you create the jobs.
Determine the task repetition and schedule
The next step is to figure out the number of times you want to do the task, and when it should happen. These are not the same thing, since you may only want to perform a task once, but you may want it to run next month. In this step you should think about why you want to automate the task. Not only can you run the task multiple times, but you can run it at an inconvenient time (for you, anyway).
Determine the task reporting and alerting
Automating tasks has a dark side. I've seen DBAs "set it and forget it", which is very dangerous. You should always set an alert or notification to contact you on success, not failure. The reason is that if you tell a system to notify you when a process fails and the notification system fails, you believe that everything is working when in fact it might not be. You have unknowingly introduced two points of risk in the system.
When you set a notification for success, the only time you need to investigate is when you don't receive a notice. Whether that means that the notification system is down or the process has failed, it needs to be investigated.
There are three ways that a job can notify you. You can set up an operator in SQL Server that contains an e-mail address, a pager e-mail address and a NET SEND address. I've covered setting up mail in SQL Server elsewhere on Informit, so I won't go into that here, but be careful of relying on the NET SEND mechanism. The System Administrator may have this service turned off, and if you're away from your desk you won't get the message anyway. Use e-mail or logs instead.
If you do use a logging process (my preference), you should remember to check the logs each day. I create logs for most of my system maintenance, so I'm used to doing that task.
Implement and check the solution
At last we're ready to create a job and automate our solution. We understand the query we need to run, and we've tested that it returns what we expect, which is an important thing to do. There's no reason to automate something that doesn't work manually!
Start by connecting to your server using SQL Server Management Studio. Expand the SQL Server Agent object.
Now right-click the Jobs object and select New Job... from the menu that appears.
In this panel you can name the job, and optionally give it a category. The categories aren't terribly useful, but I'm a DBA, so I have to put things in categories. Actually, categories do let you sort and group the jobs later, so they have some use.
I've also provided a description. This is useful for others who have to support the work you do, so make sure you explain any strangeness you've had to code.
Finally, there is a checkbox here to enable the job. You can uncheck this box to suspend a job that you don't want to erase, but you don't want to run. For now leave this option checked.
Click the Steps item on the left-hand side of the panel to move to the next phase of the job setup. Once inside, click the New... button at the bottom of the panel.
First, name the step. This comes from the first part of the analysis you did earlier. The type of jobs ranges from Transact-SQL statements, as we have here, all the way to ActiveX code. Also notice that you're able to set the name of the step owner here, which deals with the security aspect I mentioned earlier.
I've pasted the query from the script I showed earlier, and then clicked the Parse button to ensure that it works, or at least is syntactically correct.
We're not done with this step. The words in the left-hand part of the screen have changed to General and Advanced. Clicking the word Advanced brings up the following panel.
This is where a lot of the power of this tool lives. You have the ability to determine the flow using the On success and On failure actions you see here. You can set how often the step should retry, so that it forms rudimentary self-healing.
You can also send the output of the step to a file, which actually accomplishes both of our steps for this process in one go. You can also log that information to a table or store it in the history table for this job, which you can see using the views and functions I spoke about in my earlier tutorial.
Click OK on this panel, which sends you back to the main panel for the job where you can add more steps using the same process that I just demonstrated.
The rest of the options on the left-hand side of the job creation panel are very straightforward. Follow the same process as above to create and apply any schedules, alerts or notifications that you want to set up based on the information you gleaned using the outline above. We'll return to this job in a future tutorial and expand on these options further.
An interesting feature in SQL Server 2005 is the ability to apply the job to multiple "targets," or other servers. I'll also explain how to do this in another tutorial.
Once you click OK on the jobs panel, your job is complete. As promised, here is the same thing done in code:
USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Check Backups’, @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N’Checks the backups on the server to ensure that they are all running.’, @category_name=N’Database Maintenance’, @owner_login_name=N’YourLoginNameHere’, @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Run Backup Jobs Query’, @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N’TSQL’, @command=N’SELECT a.name , b.backup_finish_date FROM sys.databases a LEFT OUTER JOIN msdb.dbo.backupset b ON a.name = b.database_name ORDER BY backup_finish_date DESC, a.name GO’, @database_name=N’master’, @output_file_name=N’c:\temp\test,log’, @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N’(local)’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
After you create any job, you should run it to make sure it works. Just right-click the job and select Start Job at Step... from the menu that appears. If your job has more than one step (this one doesn't), then you can select where you want to start the job.
If you run this job as I have it set up in this example and the server is not the workstation you created the job on, keep in mind that you'll need a C:\TEMP directory and that the output will be on the server, not the workstation.
As you have seen, SQL Server Agent Jobs are very easy to create, and allow you a lot of flexibility. We'll return to them in future tutorials, building on what we've done here and delving further into schedules and notifications.
Informit Articles and Sample Chapters
Still using SQL Server version 7? We haven't left you out. Most of the info in this article still holds, with a few differences. You can read here for more.
The main site to start with is Books Online, Online. Microsoft has a series of articles starting here on the SQL Server Agent.