Determine wich databases have a Maintenance Plan(SQL2005)

  • hi,

    I've developed a simple application that help's me supervise if all databases are being "backed up".

    My problem is that i want to extend the monitoring to SQL 2005 Server's and i realized that the system tables i query are about to be deprecated(sysdbmaintplan_jobs, sysdbmaintplan_databases, ...

    )

    So far i've being able to build the tables except the "sysdbmaintplan_databases". I'm having trouble to know wich databases are associated with a maintenance plan.

    Does anyone knows how to associate a database with the correspondent maintenance plan?

    thank you for all the help

    JMValente

  • it seems that we can retrieve that information inspecting the log files

    http://blogs.msdn.com/sqlserverfaq/archive/2009/04/13/how-to-find-out-which-database-was-backed-up-by-which-maintenance-plan.aspx

    thanks for your help,

    JMValente

  • Rather than querying to determine what databases have maintenance plans, you might want to consider querying the MSDB..backupset table to see which databases have been backed up.

    In this example, you can see how long it's been since the last full or diff backup of any database on the server, or if no backup exists. This example also reports "Possible Problem" in the status column if it has been more than 7 days since the last full or 2 days since the last differential backup.

    http://dougzuck.com/hta

    http://dougzuck.com/sql-how-to-retrieve-a-list-of-the-most-recent-database-backups

    SELECT @@servername as server_name, s.name as database_name,

    is_offline =

    CASE

    WHEN s.state_desc = 'ONLINE' THEN ''

    WHEN s.state_desc = 'OFFLINE' THEN 'OFFLINE'

    WHEN s.state_desc = 'RESTORING' THEN 'RESTORING'

    END,

    is_readonly =

    CASE

    WHEN s.is_read_only = 1 THEN 'READONLY'

    WHEN s.is_read_only = 0 THEN ''

    END,

    backup_type =

    CASE

    WHEN b.type = 'D' THEN 'Full'

    WHEN b.type = 'I' THEN 'Diff'

    END,

    MAX(b.backup_start_date) AS last_backup_start_time,

    DATEDIFF(d, MAX(b.backup_start_date), getdate()) AS days_since_last_backup, status =

    CASE

    WHEN b.type = 'D' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) 7 THEN 'Possible Problem' /*if the most recent full backup was more than 7 days ago, we're Possible Problem*/

    WHEN b.type = 'I' AND DATEDIFF(d, MAX(b.backup_start_date), getdate()) 2 THEN 'Possible Problem' /*if the most recent diff backup was more than 3 days ago, we're Possible Problem*/

    WHEN MAX(b.backup_start_date) is NULL THEN 'No Backup Exists'

    END

    FROM sys.databases s LEFT JOIN msdb..backupset b

    ON s.name = b.database_name

    WHERE s.name 'master' and s.name 'model' and s.name 'msdb' and s.name 'tempdb'

    GROUP BY s.name, s.state_desc, s.is_read_only, b.type

    ORDER BY s.name

    The above script actually reports a bit more db info than the image below illustrates, but you get the idea...

  • hi dougznospam-mailbox and thank you for the reply,

    you are right, in my application i also query the backup's system tables (backupset, backupmediafamily) to retrieve the last backup executed(a query very similar to yours),

    but i also query for the 'backup job' assigned to each database, having this info i can cross the last backup and the responsible job for it.

    Retrieving job errors and the next run date are some of the info i can query.

    thank's

    JMValente

  • That makes total sense. After I posted my reply I re-read your original post and thought to myself "maybe he's already doing this..." 😀

Viewing 5 posts - 1 through 4 (of 4 total)

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