April 25, 2008 at 5:25 am
First of I'm new to SQL Server 2005 so keep it simple.
I have created 3 jobs to restore 3 databases form a backup device. I have then created a Maintenance plan to execute each SQL Server Agent job one after the other. The final task is to execute SQL to send an email. (I'm currently using a SQL Server statement msdb.dbo.sp_send_dbmail to send the mail rather than an operator.) The link between each SQL Server Agent Job task is set to success with the last link to the SQL task is set to fail.
Although the actual restore takes approx 5 minutes on each database the maintenance plan reports success suspiciously early. Also the Maintenance plan reports success even when one of the jobs fail. What I want to do is be notified if any of these fail. In fact I don't want the maintenance plan to continue if any fail.
Any ideas on how to do this with just one email rather than one for each database.
April 26, 2008 at 8:45 pm
Some clarification:
Are you using SQL Agent Job or SSMS Maintenenance Plan? It sounds like you are using both - so, correct me if I am wrong here.
I am guessing that you have three SQL Agent Jobs - one for each database being backed up, and then you are trying to use a maintenance plan to schedule the separate jobs.
If so, then what you are seeing is expected behavior. Let me explain: from a maintenance plan, using an Execute SQL Agent Job task - all the maintenance plan is going to do is start the job and return with either success or failure on being able to start the job. The next task in the maintenance plan will then start.
Sounds to me like you want an automated restore process for a training system. This can all be done in a single SQL Agent Job for each database and then schedule the job itself (no need to try and schedule the job using a maintenance plan).
Or, if you really want to use the maintenance plan - then do not use a SQL Agent job. Instead - use the Execute SQL Task and execute the restore in each task.
If using a SQL Agent job - you do not need to create another step for notification. You can setup notifications on the job itself (and yes, this does need an operator defined).
If using the maintenance plan - you can use the notify operator task or use an Execute SQL Task to run your own code. Use the constraint option of 'Success' between each restore task, and also connect each task to the notification task and change it to 'Failure'. Then right-click on the failure connection and change it from 'AND' to 'OR' which will set up the task to run if any one task fails. If you leave it on AND (solid red line) - then it will only notify if all tasks fail.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2008 at 2:41 am
Yes this is exactly what I am doing and your resolution is what I thought but I wanted to know for sure that my assumptions were correct. Thanks for the tip in the OR clause on the email notification.
The reason I created the SQL Agent jobs was because it was automated and I prefer to use the execute SQL to send the email as I find it more flexible.
In summary I will now go and create a new Maintenance Plan with three execute SQL tasks and copy the SQL from each of the Agent jobs.
Many thanks for your help!
Darryl
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply