Removing databases from Maintenance Plan backups did not remove them from T-SQL backup script

  • We have two databases that have been removed from the maintenance plan as they have been taken offline and will at some point be archived off to tape. To save space on the server, we have removed them from the nightly full maintenance plan backup job; however, when generating the T-SQL script from the maintenance plan, those two databases are still included (and are still being backed up nightly.) When editing the "Back Up Database Task" in the maintenance plan, the two databases do not even appear in the drop-down of available databases to select from.

    We found these because the backup job is failing, yet all of the appropriate databases are being backed up successfully (verified through Event Viewer on the server, and the fact that .bak files are created as expected.) Any ideas as to why the T-SQL still shows these databases even after they have been removed? Does the maintenance plan need to be recreated when we make changes to the selected databases?

    Many thanks for any advice.

    K

  • First, are the databases still attached to the server, but offline, or have you detached them? Detaching them should fix what you're running into.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The databases are still attached; however, they have no "objects" under them (i.e., no tables, views, programmability...basically the "plus sign" is no longer there, only the database name.) I will contact the developers who used these databases to find out why this happened. The developers have full access to the objects on this server as it is strictly development use, so this not a critical issue.

    This may have been a better "Newbie" post, based on my lack of understanding on how the databases ended up in this state. I've never seen databases with no objects associated, and the fact that these are the only databases causing the plan failure makes them intriguing.

    In any event, thank you for the information. I will find out if the databases can be detached and archived and see if that removes them from the T-SQL backup script and corrects the failure of the maintenance plan job.

    K

  • Databases that are offline won't display any of the objects under them. That's normal.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Great, that explains the database state.

    More importantly, now I understand that taking databases offline removes the databases from the maintenance plan, but does not dynamically update the T-SQL script in the related job.

    I hope this helps someone else as well. Appreciate your help!

    K

  • You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Dear Sir,

    I am facing the same issue but the scenario in my case is that I have detached a few databases and My Full BAckup maintenance Plan has specific databases selected. Although I saved my Package after detaching the databases, The job is still showing as failed although the remaining databases are backed up properly.

    Kindly Assist 🙁

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply