Maint Plan Fails Because it Thinks a Database Exists When it Does Not

Question

Maint Plan Fails Because it Thinks a Database Exists When it Does Not

JC-3113

SSCrazy Eights

Points: 8371
More actions
September 2, 2009 at 11:07 am

#210397

Hi Folks
anyone seen this or know the cause ?
Maint Plan is giving error trying to do a differential backup on a databases that has been removed
I do not understand why it is trying to do a differential back up on database QA_WSS_Search_NSAB-SS82-SPF-N
when it does not exist and has been removed from the maint plan as well as the server
Report shows:
Back Up Database (Differential) (NSAB-SS80-SQL-N)
Backup Database on Local server connection
Databases: QA_SharedServices1_DB,
QA_SharedServices1_Search_DB,
QA_SharePoint_Config,
QA_WSS_Content_Home,
QA_WSS_Content_Mysite,
QA_WSS_Content_SSP,
QA_WSS_Search_NSAB-SS79-SPC-N,
QA_WSS_Search_NSAB-SS82-SPF-N, <========= does not exist
ReportServer,
ReportServerTempDB,
SharePoint_AdminCont
Type: Differential
Append existing
Task start: 2009-09-02T08:05:44.
Task end: 2009-09-02T08:05:44.
Failed:(0) Database 'QA_WSS_Search_NSAB-SS82-SPF-N' is not valid to be included in the maintenance plan.
TSQL:
select
CAST (a.name as varchar(66)) as database_name,
a.dbid,
CAST (b.state_desc as varchar(12)) as state_desc
from
sys.sysdatabases a,
sys.databases b
where
a.dbid = b.database_id
order by
a.name;
go
Output:
database_name dbid state_desc
------------------------------------------------------------------ ------ -----------
AdventureWorks 9 ONLINE
AdventureWorksDW 8 ONLINE
AdventureWorksLT 10 ONLINE
master 1 ONLINE
model 3 ONLINE
msdb 4 ONLINE
NorthWind 14 ONLINE
QA_SharedServices1_DB 20 ONLINE
QA_SharedServices1_Search_DB 21 ONLINE
QA_SharePoint_Config 7 ONLINE
QA_WSS_Content_Home 22 ONLINE
QA_WSS_Content_Mysite 19 ONLINE
QA_WSS_Content_Restore 23 ONLINE
QA_WSS_Content_SSP 18 ONLINE
QA_WSS_Search_NSAB-SS79-SPC-N 16 ONLINE
ReportServer 5 ONLINE
ReportServerTempDB 6 ONLINE
SharePoint_AdminContent_43055c2e-b49c-45d5-8981-4269bcb7f544 15 ONLINE
SS65_UpdateManager 13 ONLINE
SS65_VirtualCenter 12 ONLINE
TaskTracker 11 ONLINE
tempdb 2 ONLINE
WSS_Search_NSAB-SS82-SPF-N 17 ONLINE
Thanks
Jim

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

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

SQLDCH SSChampion Points: 11630 More actions · Answer 1

Dump the SQL from the job, it should allow to 'View T-SQL'. It'll give you more info, we can then start down the sys.databases path.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

Hi DH

it looks right now like an admin is in the middle of changing the database name as he just called me

so I am going to wait until he is through mucking around

but i want to ask you how i dump the sql for a maint plan ?

can you point me in the right direction

that would be very useful

Thanks

Jim

SQLDCH SSChampion Points: 11630 More actions · Answer 3

Sorry, I should have been more clear =)

Go to the maintenance plan, right click and choose 'Modify'. I was looking at one of my backup plans, so I chose the 'Backup Database (Full)' task box. You can double click or right click to choose properties. At the bottom of the properties page was a button to 'View T-SQL'. It's similar to any of the scripting options SQL Server gives you when you choose to do something thru the GUI and be able to save the SQL statements that being run (such as creating a database).

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 4

Hi DH

i am sorry but i just do not see Properties of the plan

i did a Modify adn then I am stuck

I have looked everywhere

i am looking at this in SSMS

is that correct ?

Thanks

Jim

SQLDCH SSChampion Points: 11630 More actions · Answer 5

For each plan there are individual tasks, right click and choose 'Edit' or double click one of those tasks (they are the boxes in the plans that have arrows coming out of them) to see the SQL for that task. In my backup database plan, I have four tasks that run:

Check Database Integrity

Backup User Databases (Full)

Backup User Databases (Transaction Logs)

Rebuild Indexes

If I choose to edit one of those (by double clicking or right clicking and choosing 'Edit'), it takes me to the properties page. At the bottom of that page there's a button 'View T-SQL'.

So let me recap:

Management -> Maintenance Plans. Right click the maintenance plan, choose 'Modify'. This opens the plan, in the window that contains all the tasks, double click your desired task. It will open the properties page, at the bottom of that page is a button 'View T-SQL'.

Still clear as mud?

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 6

Hi DH

okay

i got it

i was looking for "Properties" not "Edit" of the task

my issue here

Thanks

Jim