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

  • 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

  • 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.
  • 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

  • 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.
  • 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

  • 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.
  • Hi DH

    okay

    i got it

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

    my issue here

    Thanks

    Jim

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

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