Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Should SQL 2000 and 2005 maintenance plans be resilient to a database being detached or offlined?

I detached / offlined a few databases, and now the maintenance plans are failing in their entirity because SQL can't find/connect to the database. It seems to me a simple matter for SQL to either re-calculate the script upon such a change OR to have a wizard-generated maintenance plan that will (at worst) just emit a warning about the unavailable databases and continue with the independent parts of the script, such as the other databases.

As it is, it seems to me to be required that I go into each step-block of the "User DBs" maintenance plan, switch the setting to "All databases", ok out of the wizard, save, go back in and set it back to "All databases except the system databases", ok out of the wizard, and save. That's a lot of clicks! (That's 2005, with a similarly awkward list for 2000)

Is it normal? or is something else wrong?

Thanks!

share|improve this question
    
Yup. Maintenance plans = nice for general usage, evil for big things. –  squillman Aug 31 '09 at 23:38

1 Answer 1

up vote 5 down vote accepted

Jason,

Yes, that is normal, and that is why many people (myself included) have decided to "roll-our-own" maintenance plans.

Another quirk of MP's is that they will attempt to do a trx log dump for a database in SIMPLE recovery mode. You would think it is a simple check to make before attempting the database backup, right? Well, it is, but not inside of the MP, your job just fails.

There are lots of little quirks like that. If you rely on MP's to handle your database backups then you must be aware that any change made to the database can have an adverse affect on your MP, and you will need to review and act accordingly. Otherwise you will most likely be reminded when the job fails next time around.

share|improve this answer
    
Yup! I ran in to that SIMPLE issue a few times lately. Has it been fixed since (2008)? –  uosɐſ Aug 31 '09 at 22:59
    
not that i am aware of. i do, however, believe that the new MP's offer you some flexibility. They are supposed to be SSIS packages that you can edit. so, in theory, you could build your own logic into the plans. I continue to roll my own, however. –  SQLRockstar Sep 1 '09 at 0:11
    
...and if you're too lazy to roll your own, take a look at Ola Halegren's scripts which don't suffer from the issues your describing. ola.hallengren.com –  Nick Kavadias Sep 1 '09 at 2:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.