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
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/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