December 8, 2011 at 9:58 am
i've been asked to provide a report of when every backup job run by the sql agent runs and what databases it backs up. Not all backups come from the agent- some are run by a third party tool and those must be excluded from this report. All of the agent jobs call maintenance plans to perform backus. I can pull the schedules of the jobs quite easily, but I cant figure where sql server is storing the relationship between the maitenance plan the the databases it backs up... I can join my job name back to sysssispackages, but that's as far as I'm getting.... anyone know where I find which dbs are affected by a maintenance plan?
Thanks in advance.
EDIT: probably worth noting that from sysdbmaintplan_databases is empty- these are all 2008 servers.
December 8, 2011 at 10:06 am
I don't think they are stored in a table at all. Maintenance plans are SSIS pacakges and looking at the text of one I saved out I found the list, but to get to it you are going to have to use the SSIS object model to load the package and then parse it. Not a terribly painful task but not easy either..
Sorry..
CEWII
December 8, 2011 at 10:49 am
Elliott Whitlow (12/8/2011)
I don't think they are stored in a table at all. Maintenance plans are SSIS pacakges and looking at the text of one I saved out I found the list, but to get to it you are going to have to use the SSIS object model to load the package and then parse it. Not a terribly painful task but not easy either..Sorry..
CEWII
Can you give me an example of what you mean by "use the SSIS object model to load the package and then parse it"? Is there any way to do this other than manually?
December 8, 2011 at 10:53 am
You can do it in .Net using the object model which gives you the ability to see all the proteries of all the individual task hosts within the package. I don't know of a way to do this in SQL easily.
However, you might be able to instantiate the SSIS package in an XML datatype, I'm not sure how you would query the data though.. I'll have to think about that..
CEWII
December 8, 2011 at 11:00 am
just stumbled across sysmaintplan_logdetail and sysmaintplan_log from which I think I'll be able to pull the data. thanks just the same.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply