Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing databases from Maintenance Plan backups did not remove them from T-SQL backup script Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 7:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 17, 2011 10:05 AM
Points: 85, Visits: 394
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
Post #671448
Posted Monday, March 9, 2009 8:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #671533
Posted Monday, March 9, 2009 8:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 17, 2011 10:05 AM
Points: 85, Visits: 394
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
Post #671570
Posted Monday, March 9, 2009 8:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #671588
Posted Monday, March 9, 2009 8:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 17, 2011 10:05 AM
Points: 85, Visits: 394
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
Post #671592
Posted Monday, March 9, 2009 9:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #671594
Posted Thursday, March 7, 2013 9:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:55 AM
Points: 64, Visits: 427
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
Post #1428372
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse