Exclude Offline databases from Maintenance Plan

Today morning I came across a question where the backup maintenance plan was failing because one of the databases was offline. These databases were running on SQL Server 2005.

This issue can easily be overcome by selecting these databases option in the Backup Database Task. The drawback with this approach is that whenever a new database is created, that database needs to be included in the Maintenance Plan by manually editing the Backup Task. What is the solution? Unfortunately in SQL Server 2005 there is no option to exclude databases which are not offline using Maintenance Plan. If the offline databases could not be removed due to any reasons, the Maintenance job will continue to fail stating that the database is offline. The other option is to do the maintenance tasks using custom scripts.

Welcome SQL Server 2008! In SQL Server 2008, a new tiny little option has been added to exclude databases which are not online in Maintenance Plans.

Very useful feature indeed!

This is one of the reasons why I like to contribute to the Online SQL Server communities. It helps to brush the things that one would have forgotten over time. You will always get more knowledge in return than you what you had contributed.

2 thoughts on “Exclude Offline databases from Maintenance Plan

Comments are closed.