How to find out which database was backed up by which maintenance plan

  • Article

When you backup your database in SQL Server 2000 using a maintenance plan, the information is logged into dbo.sysmaintplan_databases. You can query this table to see, which maintenance plan did the backup of which database, using this query –

select plan_id, database_name from sysdbmaintplan_databases;

The same query would not run in SQL 2005, though the table exists under msdb database.

It happens because SQL 2005 is moving away from direct access to systems tables, the information you used to pull in SQL 2000 is no longer stored in a table you can query in SQL 2005. This is stored in a DTS package which is not  "query-able". You will not be able to query anything in SQL 2005 to get databases being backed up in maintenance plans. This is a default behavior.

To make SQL log the details of backup into its tables, you would have to check “Log Extended Information” while creating maintenance plan. Also the table that would have the information logged is sysmaintplan_logdetail.

To enable logging details for a new maintenance plan, follow the below steps in Sql Server Management Studiio –

1) Management – Maintenance plan ->Right click -> New maintenance plan -> Reporting and Logging ->Log Extended information

2) Check “Log Extended Information.”

Below is a screen shot of the same -

Untitled

After you have created a new maintenance plan with “Log Extended Information” checked, you can query sysmaintplan_logdetail and sysmaintplan_plans table to know which database was backed by which maintenance plan.

Query that you could use –

use msdb

go

select line1,line3,c.name from msdb.dbo.sysmaintplan_log a,
msdb.dbo.sysmaintplan_logdetail b, msdb.dbo.sysmaintplan_plans c where
a.task_detail_id=b.task_detail_id and b.line1 like '%Back%'

go

The above query would list the database name under “line3” and name of the maintenance plan under “name” column.

Kumar Bijayanta,
SE, Microsoft SQL Server.

Reviewed by

Amit Banerjee,
Technical Lead, Microsoft Sql Server