SQL Backups, Use the Maintenance Wizard or No?

Yesterday I was reading an entry in one of the LinkIn forums that I belong to. It went something like this:

“In a simple scheduled backup maintenance plan the Server runs the job but doesn’t create a backup? what could be the reason? In the history of the backup it shows the job ran successfully?”

Obviously, this person had created the SQL Server backup plan using the SQL Maintenance Wizard, which, if you’ve never used it before, walks you through a plethora of maintenance options that you can select from, and then generates an SSIS-like visual plan.  You then schedule the plan to run whenever needed.

The subsequent forum conversation ran the gamut from how to troubleshoot a SQL Maintenance Plan (there really isn’t any good way, without a LOT of additional work, creating error-checking routines in the plan itself), to my philosophy, which was an echo of several individuals who posted before me:

“Take direct control; script out the backups and run them as SQL Agent scheduled jobs. This way, if they fail, you have a record of what happened and why.”

I’ve heard from overworked DBAs that they use the SQL Maintenance Plans because they don’t have the time to write out the code and schedule the jobs to run under the SQL Agent’s control. My experience is that 1) the Maintenance Wizard gives the user too much temptation in selecting options (do you really need to reorganize all the indexes in the database before backing up?), and 2) you don’t have control over what happens and why. With no good troubleshooting tools, when something goes wrong, the time the DBA will spend trying to figure out root cause and fixing it more than compensates for any time saved by using the Maintenance Wizard.