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

Today is SQL day for me :)

I have a maintenance plan that is failing to run with the following error:

Failed:(-1073548784) Executing the query "USE [SharedServices1_DB]" failed with the following error: "Database 'SharedServices1_DB' cannot be opened because it is offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

where SharedServices1_DB is a database that is set to offline. I would like to exclude this database from the maintenance plan, but when the database is offline, it does not show up at all as a "specific database" in the maintenance plan task, and if I bring it online, it is already unchecked in the maintenance plan task.

How can I exclude an offline database from a maintenance plan?

share|improve this question

4 Answers 4

Sql server 2008 is the only solution I've found. It's got ignore offline option.

This problem is driving me nuts.

share|improve this answer
    
This really helped. You have to go through each step in the plan, click on the "Database(s)" drop-down and then check the checkbox at the bottom "ignore databases whose state is not online." –  mdma Nov 29 '12 at 19:02

Yes we can exclude offline DB from specific maintenance plan. Please find below steps.

  1. Right click on maintenance plan -> select 'modify' -> you can see plan and subplans here ->right click on plan select 'properties' -> properties window will open in your right hand side > expand and search for the option 'IgnoreDatabaseInNotOnlinestate' -> check value set for this if its false then change it to 'True' with the help dropdown.

It works well for me without recreating maintenance plans on server.

share|improve this answer

We had a similar problem recently. We ended up going from an "include all" method to a 'pick all' method. It means that new databases are not automatically added to the maintenance plan, which means the DBAs need to let us know they're doing it. But the maintenance jobs no longer fail. A sub optimal solution, and perhaps there is a better way.

share|improve this answer
up vote 0 down vote accepted

Recreating the maintenance plan while the offline database remained offline ended up solving the problem. I will leave this question open in case somebody has the answer for resolving the problem without recreating the maintenance plan.

share|improve this answer

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.