question

yb751 avatar image
yb751 asked

Maitenance Plan Task Failed

We have a maitenance plan that does full backups on specific databases. (SQL 2005) The plan reports that some tasks has failed because 'One or more selected databases no longer exits on server'. I can see that it's still trying to backup a database that was recently deleted (DROP). When I open the plan and select the databases to be backed up it looks fine. The old database doesn't appear in the list. It appears that the rest of the plan works fine and I can see that my other db's were in fact backed up but I don't like getting false positives on my reports. Has anybody ever come across this before?
maintenance
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Maintenance plans are a pain. If it's just backups that you're running, you'd be better off in the long run writing a metadata / stored procedure driven process.
1 Like 1 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
I suspect (and this is why I don't use maintenance plans too much) that when you open the MP for editing it is only showing you the DBs that are available. Until you re-save it, it will have the previous config, including the dropped database. It uses the MP GUI to script the actions, unfortunately it doesn't reverse this when you open it for editing. So you think you are editing the MP as it currently is, but no, you are simply in the on-way process of creating the MP. Save it and try it again. If you want a reliable solution that dynamically handles new and dropped databases, either write your own, or do as many others do and use one that's already available and free - http://ola.hallengren.com
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That would make sense. I saved the plan but unfortunately I'll have to wait until tomorrow to see if it worked. I don't want to run full backups during production hours.
0 Likes 0 ·
I just verified today that it was in fact the problem. Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.