I am currently in the process of implementing standardized backups and maintenance at my new job and as a part of this, I am eliminating the existing Maintenance Plans and Jobs. One of the reasons for doing this is that Maintenance Plans require manual configuration, are less flexible than a good script, and are easily configured in a manner that violates best practices. A commonly misconfigured task in Maintenance Plans is the Maintenance Cleanup Job Task.
A common Maintenance Plan usually has backups, reindexing, statistics updates, and cleanup configured for it. For example, a plan has Full, Differential, and Log backup tasks configured in it with the Full Backup task occuring weekly, Differential task occuring daily and Log backups occuring every hour. Sounds like a really good plan so far, it covers backup properly. It also has reindexing weekly before the full backup, and nightly statistics updates. Still good. However, when it comes to the Maintenance Cleanup Task included in the plan I’ve seen a number of plans configured to delete bak Files older than 3 days old from the Backup folder.
Where does that leave you in the event of a disaster? Trying to sail upstream without a paddle. Its really an attention to detail kind of thing and even when not using a Maintenance Plan you can still make this mistake. Wherever you configure your clean up, it has to take into account the frequency of each type of backup so that a complete set of backups exists including the Full, last Differential (or all the logs since the Full), and subsequent TLogs are retained.