SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
kwilt
kwilt
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23385 Visits: 9730
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
kwilt
kwilt
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23385 Visits: 9730
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
kwilt
kwilt
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23385 Visits: 9730
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
rollercoaster43
rollercoaster43
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 435
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 Sad
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search